Buffer usages on Server, Database, Object Memory Clerks
Definition Description
1. Look at ALL MEMORY clerk allocation.
SELECT * FROM sys.dm_os_memory_clerks
Aşağıdaki sorgu sonucunda genelde CACHESTORE_SQLCP : are compiled plans for stored procedures, functions and triggers. CACHESTORE_OBJCP : are cached SQL statements or batches that arent 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. değerlerine bakılır. Şayet CACHESTORE_SQLCP > CACHESTORE_OBJCP arasındaki oran farkı çok büyük ise, stored procedure dab fazla adhoc sorgu çalıştırılmaktadır diyebiliriz.
SELECT type MemmoryClerk, SUM(pages_kb+virtual_memory_committed_kb+shared_memory_committed_kb) total_kb, SUM(pages_kb+virtual_memory_committed_kb+shared_memory_committed_kb)/1024 total_MB FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY total_kb DESC
Şayet CACHESTORE_SQLCP > CACHESTORE_OBJCP arasındaki oran farkı çok büyük ise, stored procedure dab fazla adhoc sorgu çalıştırılmaktadır diyebiliriz.
Bu sorguya bağlı olarak, Plan adı, tipi boyutunu ve ne kadar kullanıldıklarına aşağıdaki sorgu ile ulaşabiliriz.
SELECT TOP 6 LEFT([name], 20) as [name], LEFT([type], 20) as [type], pages_kb AS cache_kb, [entries_count] FROM sys.dm_os_memory_cache_counters ORDER BY pages_kb DESC
Peki bu kullanılan sorguları görmek istersek, o zaman aşağıdaki sorguyu kullanabiliriz.
SELECT TOP 100 objtype, usecounts, p.size_in_bytes/1024 'IN KB', (p.size_in_bytes/1024)/1024 'IN MB', ((p.size_in_bytes/1024)/1024)/1024 'IN GB', 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
2. Look at NON-BUFFERPOOL memory allocations:
SELECT type MemmoryClerk, SUM(pages_kb+virtual_memory_committed_kb+shared_memory_committed_kb) OutSideBP_kb, SUM(pages_kb+virtual_memory_committed_kb+shared_memory_committed_kb)/1024 OutSideBP_MB FROM sys.dm_os_memory_clerks WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL' GROUP BY type ORDER BY OutSideBP_kb DESC ORDER BY total_kb DESC
TOTAL SİZE of the Buffer Pool -----------------------------
SELECT COUNT(*) AS Buffered_Page_Count ,(CAST(COUNT(*) AS BIGINT) * 8192) / (1024 * 1024) as Buffer_Pool_MB ,((CAST(COUNT(*) AS BIGINT) * 8192) / (1024 * 1024))/1024 as Buffer_Pool_GB FROM sys.dm_os_buffer_descriptors
DATABASE USES BUFFER POOL SIZE -------------------------------------------
SELECT LEFT(CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END, 20) AS Database_Name ,COUNT(*) AS Buffered_Page_Count ,(CAST(COUNT(*) AS BIGINT) * 8192) / (1024 * 1024) as Buffer_Pool_MB ,((CAST(COUNT(*) AS BIGINT) * 8192) / (1024 * 1024))/1024 as Buffer_Pool_GB FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY Buffered_Page_Count DESC
OBJECT USES BUFFER POOL SIZE ------------------------------ SELECT TOP 25 obj.[name] ,i.[name] ,i.[type_desc] ,count(*)AS Buffered_Page_Count ,(CAST(COUNT(*) AS BIGINT) * 8192) / (1024 * 1024) as Buffer_Pool_MB ,((CAST(COUNT(*) AS BIGINT) * 8192) / (1024 * 1024))/1024 as Buffer_Pool_GB ,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
