How to find how Cache/Bpool is being used and what objects are using the cache/Bpool ..

1: To determine what plans are in the cache and how often they're used we can use
sys.dm_os_memory_cache_counters dm view .



SELECT TOP 6
LEFT([name], 20) as [name],
LEFT([type], 20) as [type],
[single_pages_kb] + [multi_pages_kb] AS cache_kb,
[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC



Here :

CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers.This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

You will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dmv.This will give us idea about which cache is getting filled up .

Generally, you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP(because we cannot frame everything in to SPs) , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.

You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.



2 : After this to know the querry are being cached and used , we can use
sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views.

select TOP 50
objtype,
usecounts,
p.size_in_bytes/1024 'IN KB',
LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

and then we can check the query plans(and size) for the one we have some doubt .

3: To find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.The query below can give us total currrent size of buffer pool .


select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors



After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .



SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
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.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC