Perf counters - Memory utilization

 To monitor the amount of memory that SQL Server uses, examine the following performance counters: 

• SQL Server: Memory Manager: Total Server Memory (KB) 
   

This counter indicates the amount of the operating system's memory the SQL Server memory manager currently has committed to SQL Server. This number is expected to grow as required by actual activity, and will grow following SQL Server startup. Query this counter using the sys.dm_os_sys_info dynamic management view, observing the committed_kb column. 

 

 

• SQL Server: Memory Manager: Target Server Memory (KB) 
   

This counter indicates an ideal amount of memory SQL Server could consume, based on recent workload. Compare to Total Server Memory after a period of typical operation to determine whether SQL Server has a desired amount of memory allocated. After typical operation, Total Server Memory and Target Server Memory should be similar. If Total Server Memory is significantly lower than Target Server Memory, the SQL Server instance may be experiencing memory pressure. During a period after SQL Server is started, Total Server Memory is expected to be lower than Target Server Memory, as Total Server Memory grows. Query this counter using the sys.dm_os_sys_info dynamic management view, observing the committed_target_kb column. For more information and best practices configuring memory, see the Server memory configuration options. 

 

• Process: Working Set 

 
This counter indicates the amount of physical memory that is in use by a process currently, according to the operating system. Observe the sqlservr.exe instance of this counter. Query this counter using the sys.dm_os_process_memory dynamic management view, observing the physical_memory_in_use_kb column. 

 

• Process: Private Bytes 

 
This counter indicates the amount of memory that a process has requested for its own use to the operating system. Observe the sqlservr.exe instance of this counter. Because this counter includes all memory allocations requested by sqlservr.exe, including those not limited by the max server memory option, this counter can report values larger than the max server memory option. 

 

• SQL Server: Buffer Manager: Database Pages 

 
This counter indicates the number of pages in the buffer pool with database content. Does not include other nonbuffer pool memory within the SQL Server process. Query this counter using the sys.dm_os_performance_counters dynamic management view. 

 

• SQL Server: Buffer Manager: Buffer Cache Hit Ratio 

 
This counter is specific to SQL Server. A ratio of 90 or higher is desirable. A value greater than 90 indicates that more than 90 percent of all requests for data were satisfied from the data cache in memory without having to read from disk. Find more information on the SQL Server Buffer Manager, see the SQL Server Buffer Manager Object. Query this counter using the sys.dm_os_performance_counters dynamic management view. 

 

 

• SQL Server: Buffer Manager: Page life expectancy 

 
This counter measures amount of time in seconds that the oldest page stays in the buffer pool. For systems that use a NUMA architecture, this is the average across the all NUMA nodes. A higher, growing value is best. A sudden dip indicates a significant churn of data in and out of the buffer pool, indicating the workload could not fully benefit from data already in memory. Each NUMA node has its own node of the buffer pool. On servers with more than one NUMA node, view each buffer pool node's page life expectancy using SQL Server: Buffer Node: Page life expectancy. Query this counter using the sys.dm_os_performance_counters dynamic management view.