2

I have SQL 2019 enterprise with system memory of 180GB and max SQL server memory configured as 160GB.

There are 3 large databases of around 450GB each. The others are smaller databases.

When I run the following query:

SELECT
    databases.name AS database_name,
    COUNT(*) * 8 / 1024 AS mb_used
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.databases
ON databases.database_id = dm_os_buffer_descriptors.database_id
GROUP BY databases.name
ORDER BY COUNT(*) DESC;

The output is:

Db1 83443
Db2 35665
Db3 20112
Db4 3559
Db5 2236
Tempdb 988
Msdb 670
Ssisdb 21
Master 2
Model 0

The total of above is 146696=143GB.

I have scheduled 3 agent jobs in parallel, 1 job targets 1 db (for db1, db2 and db3). The job does a select * on some the largest database tables one by one. The combined size of all the tables on which I perform SELECT * is about 250GB.

I know that SQL sever is supposed to consume as much memory as configured in the max memory setting.

When I run the above query at various times while the agent jobs are running, I observe that the memory of Db1, Db2, Db3 increase and decrease and increase/decrease again.

The fact that there is a decrease makes me think that there is memory pressure because of which pages are being removed from the memory to make space for pages from the other database.

However what is confusing me is that the total of the above query output is always around 135GB to 145GB. Therefore there is about 15GB to 25GB spare RAM always. This is against the idea that SQL uses up as much memory as possible and I want to ask:

  1. Why does the above sql query show a decrease/increase/decrease in the memory usage for db1, db2, db3 as the agent jobs progress, even when the total buffer utilization is only about 135-145GB? Shouldn't it have shown a decrease only when the total utilization goes to about 160GB?

  2. Shouldn't SQL Server buffer (query shown above) be consuming the entire 160GB memory (since the tables being queried are about 250GB)?

  3. Is there a query to check how much of the SQL max memory setting value utilization split by database and other things?

1 Answer 1

6

Prior to SQL 2012, Max Memory setting only affected the buffer pool. In 2012 and later the behavior is different. Memory Configuration and Sizing in SQL Server 2012.

The 15-25 GB of unaccounted for memory you are experiencing is being used by something besides the buffer pool. The dmv sys.dm_os_memory_clerks provides an accurate break down of memory usage.

4
  • Does sys.dm_os_memory_clerks give a utilization split by databases?
    – variable
    Aug 31, 2022 at 14:00
  • No. memory_clerks will tell you how much is used for the buffer pool overall. buffer_descriptors is still required to get a per db breakdown. Aug 31, 2022 at 14:02
  • Ok so memory_clearks minus buffer_descriptors = memory used by sql server for the non db query activities?
    – variable
    Aug 31, 2022 at 14:04
  • yes. memory_clerks -- group by type, ~90 rows, SQLBUFFERPOOL is only one of those. Aug 31, 2022 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.