SQL SERVER – Knowing Which Database is Consuming My Memory

I have been fortunate enough to be at conferences around the world and it is always refreshing to see how people come up with some really common yet never answered questions from time to time. The classic questions I get asked ever since I started working with databases and SQL Server is – why does SQL Server take all the memory and not return it? Even more bizarre is the question – Can I know how much memory is my databases using?

I always tell them, memory is a big topic and we need to use a number of commands like DBCC MEMORYSTATUS to know the internal working. The much more interesting way is to find out what are the pages in our buffer pool for our various databases. This can be got using DMVs as shown below:

--List the Number of pages in the buffer pool by database and page type
SELECT DB_NAME(database_id),
page_type,
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! = 32767
GROUP BY database_id, page_type
ORDER BY number_pages DESC
GO
--List the number of pages in the buffer pool by database
SELECT DB_NAME(database_id),
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! =32767
GROUP BY database_id
ORDER BY database_id
GO

SQL SERVER - Knowing Which Database is Consuming My Memory os_buffer_descriptors-01

As you can see in the above output, we can see the amount of data pages and index pages that are loaded into our SQL Server memory.

A small variation of the above query can be to scan the buffer pool based on the type of pages that are loaded into memory. Below is a typical query fired against the same DMV.

--List the number of pages in the buffer pool by page type
SELECT page_type, COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
GROUP BY page_type
ORDER BY number_pages DESC
GO
--List the number of dirty pages in the buffer pool
SELECT COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
GO

SQL SERVER - Knowing Which Database is Consuming My Memory os_buffer_descriptors-02

In the above query, I have also shown the dirty pages that are in memory and are yet to be flushed out.

This DMV is super useful when you have a number of databases that are running on our server and want to find out who is consuming the server memory. Do let me know your thoughts and what output are you seeing in your environment. Is there anything strange that you fine? Let me know via your comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Unable to Start SQL – Error: SQL Server Could Not Spawn Lazy Writer Thread
Next Post
SQL SERVER – A Stored Procedure for Executing SSIS Packages in the SSIS Catalog – Notes from the Field #092

Related Posts

7 Comments. Leave new

  • Thank you Pinal,

    The question is, how to force flush/remove the dirty pages that are in memory and are yet to be flushed out?
    As you mentioned this question is asked by everyone, the SQL Server take all the memory of server and not return it, why? is because of the dirty pages?
    If we add the minimum and maximum memory settings for SQL server in the equation, what is your recommendation?

    Thanks,
    ~Charbel

    Reply
  • Thank you Pinal,

    The question is, how to force flush/remove the dirty pages that are in memory and are yet to be flushed out?
    As you mentioned this question is asked by everyone, the SQL Server take all the memory of server and not return it, why? is because of the dirty pages?
    If we add the minimum and maximum memory settings for SQL server in the equation, what is your recommendation?

    Thanks,
    ~Charbel

    Reply
  • Very useful script.
    But, what I find more interesting is to know how to interpret this data. What’s the right/usual proportion beween DATA_PAGE and INDEX_PAGE?
    I’ve run the first script in the dev server and one of the data types is NULL, does that make sense to you?
    Thanks in advance.
    Arnau.

    Reply
  • Hi Pinal,
    Is it memory consuming now or is it overall memory used by the Database?

    Reply
  • how we can flush out Dirty pages

    Reply
  • Hi Pinal,

    The count Pages tells running/executing pages or total data pages
    present in the database

    Reply
  • Hi Pinal
    Hi Pinal,
    what Count of pages tells? i sit Memory consumed in bufferpool or disk ?

    Reply

Leave a Reply

Menu