Perfmon: SQL Server memory performance metrics – Part 2 – available bytes, total server, and target server memory

In the first part of this series, we started with most important SQL Server performance memory metrics. We described Pages/sec and Page faults/sec, what they indicated, and what the normal values were

In this part, we will present three more memory metrics that affect SQL Server performance – Available memory bytes, Total server memory, and Target server memory

 

Available bytes

“The Available Bytes counter indicates how many bytes of memory are currently available for use by processes.” [1]

SQL Server doesn’t use a fixed amount of memory by default. The memory used is determined dynamically, based on SQL Server current needs and available system memory. If SQL Server needs more memory than currently allocated and there’s memory available in the system, the operating system will allow additional memory allocation for SQL Server. If on the other hand, SQL Server allocated more memory that it needs at the moment, the excessive memory will be released to the operating system and the operating system can allocate it to other applications

If the memory Available bytes value is constantly lower than 100 MB, it indicates that there is insufficient memory on the server, or that there is an application that is not releasing memory. In case of insufficient memory, the Pages/sec and Page faults/sec values will also be high, as there is not enough memory to store all needed pages, and intensive writing into and reading from memory occurs

When there’s enough memory on the server, the available memory is high, page faults are rare, and Pages/sec and Page faults/sec are low

For good SQL Server performance, available memory should not be less than 200 MB. The threshold value depends on the system configuration and usage. It’s recommended to have a dedicated machine for SQL Server where no other applications run, so the physical memory is used only by the operating system and SQL Server

 

If there are peaks in available memory, check the disk activity. If disk activity increases when available memory is low, check whether it’s due to complex SQL Server tasks – backups, restores, etc. If not, this indicates memory pressure on the system

If available memory is constantly low and server load cannot be reduced, it’s necessary to add more RAM

Total Server Memory (KB)

“Specifies the amount of memory the server has committed using the memory manager.” [2]

In other words, this is the amount of memory currently assigned to SQL Server

The Total Server Memory value is a metric tracked by SQL Server performance counter. It can be obtained by querying the sys.dm_os_performance_counters system view

 

 

 

If the Total Server Memory (KB) value is consistently high, it means that SQL Server is constantly using a lot of memory and that the server is under memory pressure

Target Server Memory (KB)

“Indicates the ideal amount of memory the server can consume.”[2]

The Target Server Memory (KB) value shows how much memory SQL Server needs to for best performance

Similar to Total Server Memory, the Target Server Memory (KB) value is tracked by SQL Server counters and can be obtained from the sys.dm_os_performance_counters system view

 

 

 

When the Total Server Memory and Target Server Memory values are close, there’s no memory pressure on the server

In other words, the Total Server Memory/ Target Server Memory ratio should be close to 1. If the Total Server Memory value is significantly lower than the Target Server Memory value during normal SQL Server operation, it can mean that there’s memory pressure on the server so SQL Server cannot get as much memory as needed, or that the Maximum server memory option is set too low

 

As shown, the Available memory bytes metrics is one of the SQL Server performance parameters that can be used to indicate performance problems without setting a baseline first. If the available memory is constantly low, the performance is suffering. The Total Server Memory (KB) and Target Server Memory (KB) values should be analyzed together, as their ratio helps determining the right course of action to fix performance issues

References
[1] MSDN – Monitor Memory Usage
[2] SQL Server, Memory Manager Object

Useful resources
sys.dm_os_performance_counters (Transact-SQL)
Effects of min and max server memory

- See more at: https://www.sqlshack.com/sql-server-memory-performance-metrics-part-2-available-bytes-total-server-target-server-memory/#sthash.R879ixKI.dpuf


 

 

 

Fonte: https://www.sqlshack.com/
Author: Milena Petrovic

 

 

 


Crie um site com

  • Totalmente GRÁTIS
  • Design profissional
  • Criação super fácil

Este site foi criado com Webnode. Crie o seu de graça agora!