1

I want to find average Memory USED per day.

SQL server caches as much data as possible in RAM (Buffer Cache) - this is ALLOCATED RAM
But how much of this data does it actually read subsequently - this is USED RAM

Scenario:

  1. Server with 128 GB RAM, 120 GB allocated to SQL
  2. Day 1 : Run SELECT * FROM table_120GB (Copies 120GB from DISK into RAM, and Reads it from RAM)
  3. Day 2-9 : Do Nothing
  4. Day 10 : Run SELECT * FROM table_120GB WHERE ID = 1 (Reads 1 GB from RAM)

Average Memory ALLOCATED = 120 * 10 / 10= 120 GB / day

Average Memory USED = (120 + 1)/10 = 12.1 GB / day

Which counter from sys.dm_os_performance_counters gets me this Memory USED value ?

Improve this question
14
  • 1
    Why do you want to know this?
    – J.D.
    Commented Jun 24, 2022 at 11:44
  • 1
    The only way to do this accurately is a pretty bad idea: drop clean buffers every night and see how much memory gets used each day. But I have to be honest; knowing this will get you nowhere useful.
    – Erik Reasonable Rates Darling
    Commented Jun 24, 2022 at 11:56
  • @ErikDarling - it will get me somewhere very useful - reliably specing VM size when migrating to cloud. AWS/GCP/Azure have this weird scheme that you HAVE to scale CPU along with RAM . If our on-prem server is 16-core, 512 GB RAM, we can't have have the same specs on cloud, we need a 64 core machine on cloud to get up to 512 GB RAM, which is prohibitively expensive. Therefore, if SQL simply hogs 512 GB (as seen in all relevant memory metrics), but uses only 100 GB of it on a weekly basis, we can spec a 16 core, 100 GB VM on cloud to reflect real need and avoid wasting a ton of money.
    – d-_-b
    Commented Jun 24, 2022 at 13:25
  • 1
    My point, in addition to Erik's, is knowing how much data you read from Memory each day isn't really useful for your end goal. Using Erik's example on how to do that, I provided one reason why it's fruitless to do so: if you only read 1 GB of data from Memory but your execution plans requires the entire table to be read into Memory first, you still need more than 1 GB of Memory to support that query. So the fact of knowing you only read 1 GB of data from Memory every day does not tell you how to provision your server. Instead, you should measure Memory pressure per my previous comment.
    – J.D.
    Commented Jun 24, 2022 at 14:20
  • 1
    @d-_-b no, I definitely meant that this isn't a useful data point for your end goal. Even if you only read 120GB into the buffer pool every 2-3 days, what do you think performance is going to look like on every 2nd-3rd day? I can't think of a nicer way to tell you that this is totally foolish.
    – Erik Reasonable Rates Darling
    Commented Jun 24, 2022 at 14:49

1 Answer 1

Reset to default
2

There's no accurate way to examine a running SQL Server and determine how it would perform with less memory. The best way is to test by configuring MAX SERVER MEMORY to drop the available memory incrementally and watch the effect on Page Life Expectancy and IO.

Basically you're trying to find the "sweet spot" where reducing RAM just starts to increase IO and reduce Page Life Expectancy.

enter image description here

But that takes work to do in a test environment and you might not want to do in production.

Microsoft has an assessment tool for Azure migrations that will estimate the correct target configuration.

Identify the right Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Azure VM SKU for your on-premises database

And while the recommendations are Azure-specific, you can translate that to other environments. Even so, expect to test and adjust after the migration.

If you want to test with a smaller-than-recommended configuration use something like the Database Experimentation Assistant to capture and replay a production workload against a restored copy of your database and measure the performance impact of running with fewer cores and smaller memory. But you should do this in the target cloud, because you also need to test the storage configuration too.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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