Milena Petrovic

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

February 21, 2014 by
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

SQL Server performance - Available memory measure

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

Showing the Total Server Memory value

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

Viewing the Target Server Memory value

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

Showing value and ratio for Total and Target Server memory

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

Milena Petrovic

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

View all posts by Milena "Millie" Petrovic
Milena Petrovic
26,337 Views