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:
- Server with 128 GB RAM, 120 GB allocated to SQL
- Day 1 : Run
SELECT * FROM table_120GB
(Copies 120GB from DISK into RAM, and Reads it from RAM) - Day 2-9 : Do Nothing
- 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 ?