SQL Server Üzerinde Kullandığı RAM Miktarını Bulma
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.
|
1
2
3
4
5
6
7
8
9
|
SELECT[DatabaseName] = CASE [database_id] WHEN 32767THEN '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_descriptorsGROUP BY [database_id]ORDER BY [Pages in Buffer] DESC; |
Diğer sorgumuzda ise Index ve benzeri diğer nesnelerin kullandığı RAM miktarını sorgulayabiliriz.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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 bdINNER JOIN(SELECT object_name(object_id) AS name, object_id,index_id ,allocation_unit_idFROM sys.allocation_units AS auINNER JOIN sys.partitions AS pON au.container_id = p.hobt_idAND (au.type = 1 OR au.type = 3)UNION ALLSELECT object_name(object_id) AS name, object_id,index_id, allocation_unit_idFROM sys.allocation_units AS auINNER JOIN sys.partitions AS pON au.container_id = p.partition_idAND au.type = 2) AS objON bd.allocation_unit_id = obj.allocation_unit_idINNER 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.nameORDER BY [Cached Pages In MB] DESC; |
