I have a SQL 2019 server that has multiple 2019 instances installed. What are the best SQL DMVs to use to track how much memory each instance is using? (I know how much I've allocated.)
Thanks
Suggest reviewing Glenn Berry's video on memory:
You can find his queries here:
If you just want an overall measure of memory usage, performance counters are probably fine, and you'll probably want to include some that are not part of SQL Server's memory footprint:
Review that list for anything about "memory" or "buffer", especially the Paging file. Paging memory will kill your performance pretty quickly. Unfortunately, since you're hosting multiple instances you will have memory contention no matter what you do, unless you have more than 256 GB of RAM and databases that are smaller than that.
Regardless of how you monitor memory usage, make sure to set max memory for each instance, such that if all 3 hit their maximum, there's still enough RAM left for the OS to function. More details here:
There's no hard rule on how much memory to leave for the OS, it depends on the total system memory. Jonathan has some advice here:
Suggest following that, even though it's more than a few years old, and I'm not sure if it applies for 64-bit versions of SQL Server. In any case it recommends changing settings until certain performance thresholds are hit. Again, multiple instances will complicate it, whatever you can do to reduce or eliminate them will probably serve you well.
1 Like
Thank you @robert_volk