Is Monitoring Buffer Pages Too Noisy To Be Useful?

Question

I quite like dbatools, so I gave Get-DbaDbMemoryUsage (docs here) a try on my production SQL Server 2019 server. Specifically, I ran this

Get-DbaDbMemoryUsage -SqlInstance [MyProdServer] -IncludeSystemDbs |
Select Database, PageType, PercentUsed |
Where PercentUsed -ne 0 |
Format-Table

The idea of the command is that it shows what percentage of your server's RAM is being used by what type of pages in each of your databases.

After waiting a few minutes, I ran it again. Only one database returned the same result. The rest either returned completely different numbers or vanished from the result set.

Given this result, is there anything to be gained from monitoring RAM usage on a per-database level? Or is it too noisy to be useful?

Note: If this is a hint that my server has way, way, too little RAM, then let me know.

Answer 1

Get-DbaDbMemoryUsage uses the sys.dm_os_buffer_descriptors DMV. I think this is an interesting DMV but I've never used it to diagnose any performance issues. I have seen it be a significant contributing factor to the performance issues on a couple of SQL instances. I've also heard of other people having issues with this DMV.

So worth noting this DMV can be quite heavy to run if your server has a large amount of memory. In the situations I investigated the servers didn't have a huge amount of memory but the monitoring tool was running the query every ~20 seconds.

I think the sys.dm_os_memory_clerks DMV is more useful to monitor to diagnose certain memory related performance issues. e.g. USERSTORE_TOKENPERM issues. It tells you where your memory is been used but broken down by clerk. It can't tell you which DBs are using memory in your buffer pool. For that, you can maybe focus on what is been read from disk. e.g. sys.dm_io_virtual_file_stats

I'd probably take a step back from looking at memory and focus more on waits as a starting point. It's probably the best single thing to look at at a high level, though you will also want to look at other things in combination with this to get a more complete picture. e.g. IF PAGEIOLATCH* waits are one of the top wait types then you might benefit from additional memory and/or need to focus your tuning efforts in reducing physical IO.

PS. It sounds like you might benefit from a monitoring tool. I created DBA Dash which is free and open source. It might be worth a look.

Hope this helps.