Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Calculate Database memory usage with sys.dm_os_buffer_descriptors

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 

 Read More

SQL Server – How to Ask for support and troubleshoot problems

SQL Server - How to find Open Transactions

SQL Server Performance ,memory pressure and memory usage

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer