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:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| db_name | sysname | Name of database for usage distribution. |
| db_buffer_pages | bigint | Count of pages in buffer pool for database. |
| db_buffer_MB | bigint | Size of pages in buffer pool for database in MB. |
| db_buffer_percent | decimal(6,3) | Percent of buffer pool used by database. |