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
|
1 2 3 4 5 |
SELECT object_name ,counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)' |
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
|
1 2 3 4 5 |
SELECT object_name ,counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)' |
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
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
- Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports - September 12, 2014
- Using custom reports to improve performance reporting in SQL Server 2014 – the basics - September 8, 2014
- Performance Dashboard Reports in SQL Server 2014 - July 29, 2014