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:
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?
Shouldn't SQL Server buffer (query shown above) be consuming the entire 160GB memory (since the tables being queried are about 250GB)?
Is there a query to check how much of the SQL max memory setting value utilization split by database and other things?