Skip to main content
edited title
Link
d-_-b
  • 1.2k
  • 1
  • 12
  • 25

SQL Server - Find Memory ALLOCATED vs. Memory ACTUALLY USED

Source Link
d-_-b
  • 1.2k
  • 1
  • 12
  • 25

SQL Server - Find Memory ALLOCATED vs. Memory USED

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 ?