SQL Server buffer pool usage

All data is stored as 8 KB pages on the disk. When a readrequest is made for a page then that page is cached in SQL Server buffer poolso it can be reused for consequent requests for same page.

So all the database objects will consume some of buffer pooland it is good idea to keep track of what is using the buffer pool and how muchof it.

Every page cached in buffer pool is assigned a descriptorwhich uniquely identifies the cached data page.

sys.dm_os_buffer_descriptors will return all cached pages for alldatabases including user and system databases. Please note that pagesassociated with Resource database are also included. Following query will showhow much biffer cache is consumed by individual user databases

SELECT

DB_NAME(database_id) AS [Database Name],

(COUNT(*) *8/1024.0)/1024 AS [Buffer Cache Size (GB)],

(COUNT(*) *8/1024.0) AS [Buffer Cache Size (MB)]

FROM

sys.dm_os_buffer_descriptors

WHERE

database_id >4 -- filter system databases

ANDdatabase_id <> 32767 -- filter Resource database

GROUP BY

DB_NAME(database_id)

ORDER BY

[Buffer Cache Size(MB)] DESC;

Here is the output

Database Name	Buffer Cache Size (GB)	Buffer Cache Size (MB)
MyTestDB1	26.34036255	26972.53125
MyTestDB2	13.73649597	14066.17188
MyTestDB3	11.53723145	11814.125
MyTestDB4	10.90490723	11166.625
MyTestDB5	10.03324127	10274.03906
MyTestDB6	7.539787292	7720.742187
MyTestDB7	6.231697082	6381.257812
MyTestDB8	4.891433716	5008.828125
MyTestDB9	1.46887207	1504.125
MyTestDB10	0.601066589	615.492187

Also it is good idea to know what objects within a databaseare using how much of the SQL buffer.

SELECT top 10

OBJECT_NAME(p.[object_id]) AS [ObjectName] ,

p.index_id ,

COUNT(*) / 128 AS [Buffer Cache size(MB)] ,

COUNT(*) AS [Buffer Pagecount]

FROM sys.allocation_units AS a

INNER JOIN sys.dm_os_buffer_descriptors

ASb ON a.allocation_unit_id= b.allocation_unit_id

INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id

WHERE b.database_id= DB_ID()

AND p.[object_id] > 100

GROUP BY p.[object_id] ,

p.index_id

ORDER BY[Buffer Page count] DESC ;

ObjectName	index_id	Buffer Cache size(MB)	Buffer Page count
tbl_test1	3	4947	633216
tbl_test2	1	3087	395184
tbl_test3	1	2066	264574
tbl_test4	1	610	78152
tbl_test5	1	437	55956
tbl_test6	1	242	31055
tbl_test7	1	208	26706
tbl_test8	1	57	7309
tbl_test9	49	5	669
tbl_test10	1	3	490