SQL Server – Ile czego w buforze danych?

Czy pracując z SQL Server 2005/2008/2008 R2 można dowiedzieć się, która baza danych zajmuje najwięcej pamięci w buforze danych (buffer cache)? Spróbujmy użyć do tego celu widoku dynamicznego sys.dm_os_buffer_descriptors, który przechowuje jeden rekord dla każdej 8-kilobajtowej strony przechowywanej w buforze danych.

SELECT
  CASE
    WHEN database_id = 32767 THEN 'mssqlsystemresource'
    ELSE DB_NAME(database_id)
  END AS [Database],
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC;

Wynik:

Database                     In buffer cache (MB)
---------------------------- --------------------
AdventureWorks2008R2         19.59
mssqlsystemresource          13.13
master                       3.30
msdb                         2.97
ReportServer$SQL2008R2       2.81
ReportServer$SQL2008R2TempDB 2.12
AdventureWorks               1.37
tempdb                       1.20
AdventureWorksDW2008R2       1.03
AdventureWorksDW             0.95
AdventureWorksLT             0.94
AdventureWorksLT2008R2       0.94
model                        0.50
...

Tu ciekawostka – pojawia nam się baza niewidoczna w widoku sys.databases – mssqlsystemresource (nazwałem ją tak od nazwy pliku danych), czyli baza systemowa, w której Microsoft ukrył przed użytkownikami (użyszkodnikami?) tabele systemowe.

Idąc dalej, czy można dla wybranej bazy sprawdzić, które obiekty zajmują w tym buforze najwięcej miejsca? Czemu nie? Wystarczy dołożyć kilka złączeń (żeby dowiedzieć się, jakie obiekty rezydują w buforze dołączamy widoki systemowe sys.allocation_units i – w chytry sposób – sys.partitions).

USE AdventureWorks2008R2;
GO
SELECT
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id)) AS Object,
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors AS d
INNER JOIN sys.allocation_units AS u
ON d.allocation_unit_id = u.allocation_unit_id
INNER JOIN sys.partitions AS p
ON (u.type IN (1,3) AND u.container_id = p.hobt_id)
OR (u.type = 2 AND u.container_id = p.partition_id)
WHERE d.database_id = DB_ID()
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY 2 DESC;

Wynik:

Object                             In buffer cache (MB)
---------------------------------- --------------------
[sys].[sysobjvalues]               12.70
[Sales].[SalesOrderDetail]         9.68
[Sales].[SalesOrderHeader]         5.35
[sys].[sysrowsets]                 4.25
[sys].[syscolpars]                 3.55
[sys].[sysschobjs]                 2.29
[sys].[sysrscols]                  1.95
[sys].[sysallocunits]              1.32
[sys].[sysmultiobjrefs]            1.15
[sys].[sysftstops]                 0.95
[sys].[sysiscols]                  0.93
[sys].[syssingleobjrefs]           0.92
...

A teraz zadanie domowe – zmodyfikuj powyższe zapytanie, by zwracało ilość megabajtów zajętą przez każdy indeks (sprawdź, które indeksy zajmują w buforze najwięcej miejsca). To nie może być trudne :-)