Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Determine SQL Server memory use by database and object


By:   |   Read Comments (20)   |   Related Tips: More > Dynamic Management Views and Functions

Problem

For many people, the way that SQL Server uses memory can be a bit of an enigma. A large percentage of the memory your SQL Server instance utilizes is consumed by buffer pool (essentially, data). Without a lot of digging, it can be hard to tell which of your databases consume the most buffer pool memory, and even more so, which objects within those databases. This information can be quite useful, for example, if you are considering an application change to split your database across multiple servers, or trying to identify databases that are candidates for consolidation.

Solution

A Dynamic Management View (DMV) introduced in SQL Server 2005, called sys.dm_os_buffer_descriptors, contains a row for every page that has been cached in the buffer pool. Using this DMV, you can quickly determine which database(s) are utilizing the majority of your buffer pool memory. Once you have identified the databases that are occupying much of the buffer pool, you can drill into them individually. In the following query, I first find out exactly how big the buffer pool currently is (from the DMV sys.dm_os_performance_counters), allowing me to calculate the percentage of the buffer pool being used by each database:

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
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 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC; 

 

In the above query, I've included the system databases, but you can exclude them by uncommenting the WHERE clause within the CTE. Note that the actual filter may need to change with future versions of SQL Server; for example, in SQL Server 2012, there is a new database for Integration Services called SSISDB. You may want to keep an eye on system databases just to have a complete picture, seeing as there isn't much you can do about their buffer pool usage anyway - unless you are using master or msdb for your own custom objects.

That all said, here are partial results from an instance on my local virtual machine:

using dmv in sql server 2005 to determine which database is utilizing the majority of your buffer pool memory

Clearly, the SQLSentry database - while only representing 258 MB - occupies about 70% of my buffer pool for this instance. So now I know that I can drill into that database specifically if I want to track down the objects that are taking up most of that memory. You can once again use the sys.dm_os_buffer_descriptors only this time, instead of aggregating the page counts at the database level, we can utilize a set of catalog views to determine the number of pages (and therefore amount of memory) dedicated to each object.

USE SQLSentry;
GO

;WITH src AS
(
SELECT
[Object] = o.name,
[Type] = o.type_desc,
[Index] = COALESCE(i.name, ''),
[Index_Type] = i.type_desc,
p.[object_id],
p.index_id,
au.allocation_unit_id
FROM
sys.partitions AS p
INNER JOIN
sys.allocation_units AS au
ON p.hobt_id = au.container_id
INNER JOIN
sys.objects AS o
ON p.[object_id] = o.[object_id]
INNER JOIN
sys.indexes AS i
ON o.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE
au.[type] IN (1,2,3)
AND o.is_ms_shipped = 0
)
SELECT
src.[Object],
src.[Type],
src.[Index],
src.Index_Type,
buffer_pages = COUNT_BIG(b.page_id),
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
src
INNER JOIN
sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE
b.database_id = DB_ID()
GROUP BY
src.[Object],
src.[Type],
src.[Index],
src.Index_Type
ORDER BY
buffer_pages DESC;

Here are the results from this database. Notice that I've captured both clustered and non-clustered indexes, for clustered tables and heaps, and for illustrative purposes I have also created an indexed view.

both clustered and non-clustered indexes have been captured

Please keep in mind that the buffer pool is in constant flux, and that this latter query has explicitly filtered out system objects, so the numbers won't always add up nicely. Still, this should give you a fairly good idea of which objects are using your buffer pool the most.

When investigating the performance of your servers, buffer pool data is only a part of the picture, but it's one that is often overlooked. Including this data will help you to make better and more informed decisions about direction and scale.


Next Steps


Last Update:





About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips





More SQL Server Solutions




Post a comment or let the author know this tip helped you.

All comments are reviewed, so stay on subject or we may delete your comment.

*Name    *Email    Notify for updates 

Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Sunday, November 15, 2015 - 5:11:01 AM - AbuElshabab Back To Top

Tempting article, however, shouldn't the summation of the db_buffer_percent = 100 ... it's not for my SQL instance. the summatoin is just 79%. although i didn't include any filter. I've just ran the query as it is! ... where is the rest 21%?!


Wednesday, June 03, 2015 - 12:21:54 PM - coolcancer Back To Top

Great article and very informative.

 

Is there a way to get the results for date range or for a period?


Friday, December 05, 2014 - 3:12:02 PM - Jodine Back To Top

This is great information.  The memory is out the roof on one of my 2012 SP2 servers.  Its causing issues and I'm trying to determine if there is a memory leak and if so, which procedures might be causing it.  If you have any past articles on that, I'd love to read them.

 

Thanks!


Tuesday, November 18, 2014 - 4:55:35 AM - Will Smith Back To Top

Hi Aaron,

Just came acros this today and found very informative, Thank you !

One question though - For the first query, How about subtracting the stolen pages from total pages? Dont you think that might be helpful specially in case sql is under internal memory pressure ?

 


Tuesday, September 17, 2013 - 8:50:10 AM - Anandan Kanagarajan Back To Top

Great Post indeed and I used this several time to identify the Databasewise Memory usage on a SQL Server Instance.

While recently using this DatatabaseWise Memory usage query, on the result window, the tempdb is consuming around 2+ Gigs of Memory.

I also used the object wise memory usage query to identify the memory usage of Tempdb. But from the result set I am not getting the details of the entire 2 Gigs of Memory usage that was shown on the result of Databasewise Memory usage.

May I request to shed some light about the Tempdb Memory usage on SQL Server 2008 and how to remove the memory usage with out restarting the instance ?

Thanks in advance


Tuesday, March 19, 2013 - 4:56:33 PM - Aaron Bertrand Back To Top

Kevin,

Yes, an index (or a range) is either in memory or not, if it is referenced 15 times or 20 times it is still only represented once.


Tuesday, March 19, 2013 - 10:14:39 AM - Kevin Di Sotto Back To Top

Hi Aaron

Love this query ! Why would indexes that are not being used as much as other indexes  be take up a bigger percentage of buffer memory? Does it all depend on the size of the Index?    

 

 


Thursday, January 24, 2013 - 10:03:32 AM - Aaron Bertrand Back To Top

Thanks for the note John; this was written well before SQL Server 2012 was public. :-)


Thursday, January 24, 2013 - 8:40:07 AM - John Henderson Back To Top

It appears that for SQL 2012 in order to get the total buffer value you need to alter the where clause to be "counter_name = 'Database pages';" instead of "counter_name = 'Total Pages';"

Great article though...


Monday, January 21, 2013 - 2:08:38 PM - Aaron Bertrand Back To Top

Ammad not really an easy way through the DMVs directly unless you parse the plan cache for any currently running queries and determine it that way - however that may actually cause more performance problems than you could possibly hope to solve.

If you want to know who is querying specific tables, that might be a better job for trace, audit, or 3rd party monitoring like SQL Sentry Performance Advisor.

http://www.sqlsentry.net/performance-advisor/sql-server-performance.asp

*Disclaimer: I work for SQL Sentry.


Monday, January 21, 2013 - 12:00:24 PM - Ammad Back To Top

Fantastic article!

Is there a way to include which user or process was accessing the tables?


Tuesday, November 20, 2012 - 11:15:22 AM - Krishna Back To Top

 

Very good article for beginner DBA's.. Thanks alot Sir


Thursday, May 24, 2012 - 11:57:50 PM - Momfei Back To Top

GODLIKE!

Excellent article!

Thanks you so much to share this VERY x999 useful technique.


Thursday, June 30, 2011 - 4:05:49 AM - Muratos Back To Top

Excellent.

Can you develop it further to include sum(row_counts) and sum(real_table_row_count)? I think we can understand whether all table rows in memory or not using that way.


Friday, June 03, 2011 - 12:34:18 PM - Bruce Samuelson Back To Top

Here's a slight correction to these excellent tools. The last line of the first query should be ordered by buffer pages rather than buffer MB because the latter rounds to the same value for some DBs.

ORDER

 

BY db_buffer_pages DESC, db_name ASC; --db_buffer_MB DESC;


Wednesday, June 01, 2011 - 4:42:13 PM - TheSmilingDBA Back To Top

Excellecent article!!!

Need to explain how to use resutls to tune queries or request more RAM.

Thanks,

Thomas


Sunday, May 22, 2011 - 6:20:30 PM - Daniel Back To Top

Excellent article!!! Congrats!!!


Thursday, May 19, 2011 - 11:42:16 AM - Aaron Bertrand Back To Top

Moinu, have a look at these articles from Kimberly Tripp:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat.aspx


Thursday, May 19, 2011 - 7:21:41 AM - Ahmad Back To Top

Goodish !!!

 

somewhat related to buffers i am in need to effectively use DBCC FREESYSTEMCACHE if possible to replace hectic DBCC DropCleanBuffers for during testing performance of queries??

:(

 

 


Thursday, May 19, 2011 - 4:31:22 AM - Moinu Back To Top

This is a great article. Is there any way by which we could find out the details of memory used in other places like in proc cache etc?


Learn more about SQL Server tools