SQL Server Üzerinde Kullandığı RAM Miktarını Bulma

08/10/2019 iTech

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.

Kaynak Kod
1 2 3 4 5 6 7 8 9	`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.

Kaynak Kod


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;

You May Also Like

URL Rewrite – Protocol (http/https) in the Action

How to Downgrade from SQL Server Enterprise Edition to Standard Edition

How to Read a Traceroute