Tuesday, April 24, 2012

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

No comments:

Post a Comment