SQL Server - Buffer pool usage

Check buffer pool usage for every database. Returns information about all the data pages that are currently in the SQL Server buffer pool. Use output of this view to determine the distribution of database pages in the buffer pool according to database. When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse.

Use this script to quick overview how is your load distributed among databases.

TIP: You can collect results on regular basis (create SQL Agent job) for further analysis.

NOTE: querying sys.dm_os_buffer_descriptors  requires the VIEW_SERVER_STATE permission.

DECLARE @TotalBuffer INT

-- get total buffer pool usage
SELECT 
	@TotalBuffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Database Pages';

-- get distribution by database
WITH src AS (
	SELECT 
		database_id, 
		db_buffer_pages = COUNT_BIG(*)
	FROM sys.dm_os_buffer_descriptors
	GROUP BY database_id
) SELECT
	[db_name] = CASE [database_id] 
		WHEN 32767 THEN 'Resource DB' 
		ELSE DB_NAME([database_id]) 
	END,
	db_buffer_pages,
	db_buffer_MB = db_buffer_pages / 128,
	db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @TotalBuffer)
FROM src
ORDER BY db_buffer_MB DESC

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
db_namesysnameName of database for usage distribution.
db_buffer_pagesbigintCount of pages in buffer pool for database.
db_buffer_MBbigintSize of pages in buffer pool for database in MB.
db_buffer_percentdecimal(6,3)Percent of buffer pool used by database.

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *