Find buffer pool usage of database in SQL Server

SQL Server stores data in datapage of 8KB. Whenever you read datapage first time, SQL Server used to physically read it from disk and load that page into buffer memory so that subsequent call of that page can cater the need fast. Memory in server may be limited so it is better to keep an eye on buffer memory usage so that less important database/object doesn’t occupy more space in buffer.

Note:  Know more about datapage, read my earlier article here.

We can find information about buffer from the DMV “sys.dm_os_buffer_descriptors”. When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server.  sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database which is hidden system database.

Here is the query to find buffer usage:

 SELECT
 database_id AS DatabaseID,
 DB_NAME(database_id) AS DatabaseName,
 COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
 FROM
 sys.dm_os_buffer_descriptors
 GROUP BY
 DB_NAME(database_id),database_id
 ORDER BY
 BufferSizeInMB DESC
 GO

You can get output something like this:

Please note first row in above screen shot which has NULL in DatabaseName column whose databaseID is 32767. That row belongs to ResourceDatabase which is system database, hidden from the user and always having databaseID 32767.

You can clear up your buffer memory with following command.

 DBCC DROPCLEANBUFFERS
 GO

Please note that executing above given command in production server will degrade performance of your queries temporary as it will remove all datapage from buffer pool so I would highly recommend not using above command in production server.

Let us execute SELECT query for buffer pool again and see what is the status of buffer pool with the same SELECT query.

 SELECT
 database_id AS DatabaseID,
 DB_NAME(database_id) AS DatabaseName,
 COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
 FROM
 sys.dm_os_buffer_descriptors
 GROUP BY
 DB_NAME(database_id),database_id
 ORDER BY
 BufferSizeInMB DESC
 GO

You can see most of your buffer memory free due to DROPCLEANBUFFERS command. Here is the screen capture in my SQL Server Instance.

You may not get exactly same output as above screen capture but you can definitely see the difference the buffer memory distribution in your both execute (Before and after DROPCLEANBUFFERS).

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.