October 20, 2011 at 4:39 am
How do i check the size of the datacache allocated from the buffer pool by sql server?
I appreciate this may be opening a can of worms, but if someone can advise on dmv or anything to show me the pool allocation sizes for the various pools in sql server i think i may be able to work from there.
October 20, 2011 at 4:49 am
select count(*)*8/1024 AS 'Cached Size (MB)'
,case database_id
when 32767 then 'ResourceDB'
else db_name(database_id)
end as 'Database'
from sys.dm_os_buffer_descriptors
group by db_name(database_id), database_id
order by 'Cached Size (MB)' desc
Gives you space used by each database in the buffer pool.
October 20, 2011 at 3:00 pm
thanks for that, so would the following query give me the size of the data cache ( not plans or anything)?
select count(*)*8/1024 AS 'Cached Size (MB)'
,case database_id
when 32767 then 'ResourceDB'
else db_name(database_id)
end as 'Database'
from sys.dm_os_buffer_descriptors
where page_type in
(
'INDEX_PAGE'
,'DATA_PAGE'
)
group by db_name(database_id), database_id
order by 'Cached Size (MB)' desc
Im trying to work out my datacache size, and dont want to include planCache, to work out a realistic Page life expectancy as discussed in this article:
October 26, 2011 at 4:23 am
Sorry to bump this post, but I too am looking for a means of determining our "DataCacheSizeInGB" as per Jonathan Keyayias' article.
As an "accidental DB" it would be great if there were a performance counter for this - perhaps Denali will provide this 🙂
I'm @benjaminathawes on Twitter if anyone feels kind enough to educate me.
October 26, 2011 at 9:34 am
Just to follow up on this:
I was given this formula to use although I haven't yet confirmed it is correct:
(CAST (value_in_use AS INT)/1024/4)*300 FROM sys.configurations WHERE NAME = 'max server memory (mb)'
If this is correct then determining the appropriate PLE is actually very straightforward (although I imagine this is only a rule of thumb): you basically use the MS figure of 300 for 4GB of RAM as a baseline then adjust the value accordingly depending on how much memory you have installed.
e.g. if you have 64 GB RAM, you should probably investigate if your PLE consistently drops below 4800 seconds (80 minutes). To calculate this I just used [(installed RAM in GB / 4) * 300].
I'll ask around to hopefully get this confirmed who knows their SQL.
October 26, 2011 at 9:55 am
maybe this will help
declare @nRows int
set @nRows = ##################################
declare @resultstable
(database_idint
,objectnamesysname null
,indexnamesysname null
,cache_kbbigint
,free_bytesbigint
,size_kbbigint null
,filegroupsysname null
,indidint null
,dirty_kbbigint null
,schema_namesysnamenull
,user_namesysnamenull
)
declare@databases table
(database_idint
,namesysname null
,idint identity
)
insertinto @databases (database_id, name)
selectdatabase_id, name
fromsys.databases
whereuser_access <> 1-- NOT SINGLE USER
andstate = 0 -- ONLINE
andhas_dbaccess(name) <> 0-- Have Access.
declare@nBufferSize bigint
select@nBufferSize = count(*)
fromsys.dm_os_buffer_descriptors with (readpast)
declare @sql nvarchar(max)
declare @n int
set@n = 1
declare @db int
set@db = 0
while 1=1
begin
set@db = null
select@db = database_id from @databases where id = @n
set @n = @n + 1
if @db is null
break
if @db = 0x7FFF-- Skip this one.
continue
set @sql= 'use ' + quotename(db_name(@db))
+'selectdb_id() database_id'
+',isnull(o.name,''<in-memory-resource>'')object_name'
+',isnull(i.name,'''')index_name'
+',cast(8*sum(cast(b.cache_pages as bigint)) as bigint)cache_kb'
+',sum(cast(b.free_bytes as bigint))free_bytes'
+',cast(8*sum(cast(a.total_pages as bigint)) as bigint)used_kb'
+',(select top 1 name from sys.filegroups fg with (readpast) where fg.data_space_id = a.data_space_id) filegroup'
+',min(i.index_id)indid'
+',cast(8*sum(cast(b.dirty_pages as bigint)) as bigint) dirty_kb'
+',min(s.name)schema_name'
+',min(u.name)user_name'
+'from('
+'selecta.database_id'
+',allocation_unit_id'
+',count(*) cache_pages'
+',sum(cast(free_space_in_bytes as bigint)) free_bytes'
+',sum(case when is_modified=1 then 1 else 0 end) dirty_pages'
+'fromsys.dm_os_buffer_descriptors a with (readpast) '
+'wherea.database_id = db_id()'
+'group by a.database_id,allocation_unit_id'
+')b'
+'left outer joinsys.allocation_unitsawith (readpast) onb.allocation_unit_id = a.allocation_unit_id'
+'left outer joinsys.partitionspwith (readpast) on(a.container_id = p.hobt_id and a.type in (1,3) )'
+'or(a.container_id = p.partition_id and a.type = 2 )'
+'left outer join sys.objectsowith (readpast) on p.object_id = o.object_id '
+'left outer join sys.indexesiwith (readpast) on p.object_id = i.object_id and p.index_id = i.index_id'
+'left outer join sys.schemasswith (readpast) on o.schema_id = s.schema_id'
+'left outer joinsys.database_principals uwith (readpast) on s.principal_id = u.principal_id'
+'wheredatabase_id = db_id()'
+'anda.data_space_id is not null'
+'group by a.data_space_id, isnull(o.name,''<in-memory-resource>''), isnull(i.name,'''')'
+'option (keepfixed plan)'
insert into @results
(database_id
,objectname
,indexname
,cache_kb
,free_bytes
,size_kb
,filegroup
,indid
,dirty_kb
,schema_name
,user_name
)
exec(@sql)
end
insert into @results (database_id, schema_name, user_name, cache_kb, free_bytes, dirty_kb)
selecta.database_id
, 'system'
, 'system'
,8*count(*) cache_pages
,sum(free_space_in_bytes) free_bytes
,sum(case when is_modified=1 then 1 else 0 end)*8 dirty_pages
fromsys.dm_os_buffer_descriptors a with (readpast)
wherea.database_id = 0x7FFF
group by a.database_id
option (keepfixed plan)
set rowcount @nRows
set nocount off
selectDBName
,TBOwner
,TBName
,IXName
,SizeInCacheKB
,case
when PercentageOfCache > 100 then 100
when PercentageOfCache < 0 then 0
else PercentageOfCache
end PercentageOfCache
,ObjectSizeKB
,case
when PercentageOfObject > 100 then 100
when PercentageOfObject < 0 then 0
else PercentageOfObject
end PercentageOfObject
,FileGroup
,indid
,DirtyKB
,case
when PercentageObjectDirty > 100 then 100
when PercentageObjectDirty < 0 then 0
else PercentageObjectDirty
end PercentageObjectDirty
,AllocateCacheUnusedKB
from(
selectcase when database_id = 0x7FFF then 'mssqlsystemresource' else db_name(database_id) endDBName
,isnull(user_name,'system') TBOwner
,objectnameTBName
,indexnameIXName
,cache_kbSizeInCacheKB
,case when @nBufferSize = 0 then 0.0 else 100*(cache_kb/8.)/@nBufferSize endPercentageOfCache
,size_kbObjectSizeKB
,case when size_kb = 0 then 0.0 else ((cache_kb*1.)*100.0)/size_kb endPercentageOfObject
,filegroupFileGroup
,indid
,dirty_kbDirtyKB
,case when size_kb = 0 then 0.0 else dirty_kb*100.0/size_kb end PercentageObjectDirty
,free_bytes/1024.AllocateCacheUnusedKB-- NEWCOlumn
from @results
)x
order by PercentageOfCache desc
set rowcount 0
October 26, 2011 at 10:45 am
Thanks for this Anthony.
Hopefully this answers the OPs question although I haven't had a chance to try it myself.
I'll post my PLE question separately as it is more of a high level "sanity check" around whether the formula I posted above is a good rule of thumb approach.
August 25, 2013 at 10:18 pm
bathawes 16438 (10/26/2011)
Just to follow up on this:I was given this formula to use although I haven't yet confirmed it is correct:
(CAST (value_in_use AS INT)/1024/4)*300 FROM sys.configurations WHERE NAME = 'max server memory (mb)'
If this is correct then determining the appropriate PLE is actually very straightforward (although I imagine this is only a rule of thumb): you basically use the MS figure of 300 for 4GB of RAM as a baseline then adjust the value accordingly depending on how much memory you have installed.
e.g. if you have 64 GB RAM, you should probably investigate if your PLE consistently drops below 4800 seconds (80 minutes). To calculate this I just used [(installed RAM in GB / 4) * 300].
I'll ask around to hopefully get this confirmed who knows their SQL.
I came across this thread and thought I would provide some info to those that come across it as I did through Google/Bing...
@bathawes formula below is off by the value being used. The Max server memory is a static value that tells what your buffer cache could be, telling SQL Server how much of the available OS memory you are allowed to take.
This thread on SSC has some information regarding how to pull the buffer cache size, note John.Sansom's post in the thread. You can utilize sys.dm_os_sys_info as noted to pull the bpool_committed value.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 23, 2014 at 4:26 pm
I think this could work as well; it calculates the size of data cache per database.
Execute sp_msforeachdb @command1 = '
Select
''?'' As DatabaseName,
DataCacheSize = (Sum(cached_pages_count) * 8.00)/1024.00
From (
SELECT
COUNT(*) AS cached_pages_count,
name AS BaseTableName,
IndexName,
IndexTypeDesc
FROM ?.sys.dm_os_buffer_descriptors AS bd
INNER JOIN(
SELECT
s_obj.name,
s_obj.index_id,
s_obj.allocation_unit_id,
s_obj.OBJECT_ID,
i.name IndexName,
i.type_desc IndexTypeDesc
FROM(
SELECT
OBJECT_NAME(OBJECT_ID) AS name,
index_id,
allocation_unit_id,
OBJECT_ID
FROM ?.sys.allocation_units AS au
INNER JOIN ?.sys.partitions AS p ON au.container_id = p.hobt_id AND (au.TYPE = 1 OR au.TYPE = 3)
UNION ALL
SELECT
OBJECT_NAME(OBJECT_ID) AS name,
index_id,
allocation_unit_id,
OBJECT_ID
FROM ?.sys.allocation_units AS au
INNER JOIN ?.sys.partitions AS p ON au.container_id = p.partition_id AND au.TYPE = 2
) AS s_obj
LEFT JOIN ?.sys.indexes i ON i.index_id = s_obj.index_id AND i.OBJECT_ID = s_obj.OBJECT_ID
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID(''?'')
GROUP BY name, index_id, IndexName, IndexTypeDesc
) Res
'
July 14, 2014 at 9:01 am
This does the same, but a whole lot quicker
SELECT DB_NAME(database_id) AS [DBName],
COUNT(*) * 8/1024.0 AS [CacheUsed]
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id)
ORDER BY DB_NAME(database_id) ASC
June 25, 2015 at 7:01 am
if there are allocation unit is present in sys.dm_os_buffer_descriptors but not present in sys.allocation_units then does that mean those are used by SQL server internally. On my DB I see out of 500 GB memory 200 GB is being consumed by such pages. How can I identify what is causing these pages to be very high in number.
December 31, 2015 at 10:22 am
bathawes 16438 (10/26/2011)
Sorry to bump this post, but I too am looking for a means of determining our "DataCacheSizeInGB" as per Jonathan Keyayias' article.As an "accidental DB" it would be great if there were a performance counter for this - perhaps Denali will provide this 🙂
I'm @benjaminathawes on Twitter if anyone feels kind enough to educate me.
There are a couple of example queries here that give the data cache size in MB. Why not just change the Jonathan Keyayias' suggested formula "DataCacheSizeInGB/4GB *300" to work with MB instead? Like this: "DataCacheSizeInMB/4096MB*300"? Wont this work the same since 4GB = 4096MB?
January 4, 2016 at 1:27 pm
I've allocated 40 GB memory to SQL 2014 instance.
How much memory SQL can allocate for data cache (store data pages in buffer ) and plan cache.
Where can I find the information.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply