27 November,2012 by Jack Vamvas
Calculate the SQL Server Database memory usage with the DMV - sys.dm_os_buffer_descriptors, which reports details of all data pages currently in the buffer pool. This post is only dealing with distribution of database pages at the database level, but there are details at an object or type level.
The sys.dm_os_buffer_descriptors doesn’t return information about stolen pages. Read SQL Server – Troubleshooting Internal Memory pressure – stolen pages and buffer count for stolen pages details
When troubleshooting memory pressure , TempDB memory usage - is always worth a look – quite often long running transactions can use large amounts of memory. Read SQL Server – List current tempdb statements to report on sql statements running in tempdb.
This sql query returns a list of all databases on SQL Server Instance - broken down into buffer pool usage displayed in MB and GB. Once you've identified the memory usage - drill down into the specific database to identify queries using large amounts of memory.
SELECT DB_NAME(database_id) AS DB, COUNT(row_count)*8.00/1024.00 AS MB, COUNT(row_count)*8.00/1024.00/1024.00 AS GB FROM sys.dm_os_buffer_descriptors GROUP BY database_id