SQL Server üzerinde yer alan veritabanı ve diğer nesnelerin ne kadar sistem kaynağı tükettiğini bulmak için aşağıdaki iki temel sorguyu kullanabiliriz.
İlk sorgumuzda veritablanı bazında kullanılan RAM miktarını sorgulayabiliriz.
SELECT [DatabaseName] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, COUNT_BIG(*) [Pages in Buffer], COUNT_BIG(*)/128 [Buffer Size in MB] FROM sys.dm_os_buffer_descriptors GROUP BY [database_id] ORDER BY [Pages in Buffer] DESC;
Diğer sorgumuzda ise Index ve benzeri diğer nesnelerin kullandığı RAM miktarını sorgulayabiliriz.
SELECT obj.name [Object Name], o.type_desc [Object Type], i.name [Index Name], i.type_desc [Index Type], COUNT(*) AS [Cached Pages Count], COUNT(*)/128 AS [Cached Pages In MB] FROM sys.dm_os_buffer_descriptors AS bd INNER JOIN ( SELECT object_name(object_id) AS name, object_id ,index_id ,allocation_unit_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, object_id ,index_id, allocation_unit_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 obj ON bd.allocation_unit_id = obj.allocation_unit_id INNER JOIN sys.indexes i ON obj.[object_id] = i.[object_id] INNER JOIN sys.objects o ON obj.[object_id] = o.[object_id] WHERE database_id = DB_ID() GROUP BY obj.name, i.type_desc, o.type_desc,i.name ORDER BY [Cached Pages In MB] DESC;