Different environments and applications require different metrics to be measured. The first task is to determine what parameters to track. The second task is to find out what values indicate normal and bad performance, and what the baselines are
Once you determine your performance goals, selecting the metrics and baseline values is easier. For most performance goals, monitoring memory, CPU usage, disk activity, and network traffic is common
A general recommendation for smooth SQL Server running is to have a dedicated server for it. Having other applications on the same machine will make SQL Server compete with other applications for memory, processor, and disk resources, and thus downgrade SQL Server performance
SQL Server memory metrics
Memory metrics indicate server health
The operating system provides a number of performance counters that are associated with memory. The most important are: Pages/sec, Page Faults/sec, Pages Input/sec, Pages Output/sec, Page Reads/sec, Page Writes/sec, Available Memory, Non-pageable memory pool bytes, Pageable memory pool bytes, and Committed Bytes [1]
Adding memory can solve some of the performance issues caused by insufficient memory. If some parameters are not properly configured and performance issues properly diagnosed, additional memory will not improve SQL Server performance and you’ll be back to square one
Pages/sec
Pages/sec is one of the most important memory metrics. It’s recommended to monitor it whatever performance goals are
The pages/sec counter “Shows the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays. It is the sum of Memory\ Pages Input/sec and Memory\ Pages Output/sec. It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\ Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) and noncached mapped memory files.” [2]
Note that this is not the same as Page Faults/sec
The average Pages/sec value should be below 50. It’s not possible to reduce the value to zero, as moving pages from memory and to memory always occurs while the operating system is running. The more memory the server has, fewer pages have to be written and read due to page faults
A high value of Pages/sec indicates intensive memory activity – many pages are written from disk to RAM and read from RAM to disk. High Pages/sec values can indicate insufficient RAM memory, overhead and SQL Server performance degradation, but it doesn’t necessarily have to be so. A high Pages/sec value can also be caused by sequential reading of a file mapped in memory. To determine whether this is the case, check the Memory Available Bytes and Paging File % Usage values [4]
Occasional peaks are normal and appear when creating SQL Server database and transaction log backups, restoring databases, importing and exporting data, and performing other complex tasks
It’s recommended to have a dedicated server for SQL Server only, as other applications can use memory needed by SQL Server and affect SQL Server performance. A counter that indicates that this is the case is Buffer Hit Cache Ratio. If its value is 99% or higher for more than 24 hours and during this same period the Pages/sec value is higher than 50, other applications use memory needed by SQL Server
If there are no other applications and Pages/sec is still higher than 50, SQL Server is not taking as much memory as it needs, which means that the memory settings are not configured correctly
- In SQL Server Management Studio Object Explorer, right click the SQL Server instance
- Select Properties
- Open Memory
Set the Maximum server memory (in MB) to approximately 80% of available physical memory, in order to leave enough memory for the operating system
If SQL Server has already been allocated maximum available memory, the solution is adding additional RAM
Page Faults/sec
Page faults are one of the most common problems with memory
“A page fault occurs when a program requests an address on a page that is not in the current set of memory resident pages” [3]
When a page fault is encountered, the program execution stops and is set to the Wait state. The operating system searches for the requested address on the disk. When the page is found, the operating system copies it from the disk into a free RAM page. The operating system allows the program to continue with the execution afterwards
There are two types of page faults – hard and soft page faults. Hard page faults occur when the requested page is not in the physical memory. Soft page faults occur when the requested page is in the memory, but cannot be accessed by the program as it is not on the right address, or is being accessed by another program
Monitoring page faults is important as excessive hard page faults affect application performance. Soft page faults cause no performance issues. The Page faults/sec counter shows both hard and soft page faults, so it can be difficult to determine whether the page faults value indicates performance problems in SQL Server, and should be addressed, or presents a normal state
There is no specific Page faults/sec value that indicates performance problems. Monitoring Page faults/sec should provide enough information to create a baseline that will be used to determine normal server performance. The normal values are 10 to 15, but even 1,000 page faults per second can be normal in specific environments. The value depends on the type and amount of memory, and the speed of disk access. A sustained or increasing value for Page faults/sec can indicate insufficient memory. If this is the case, check the Page reads/sec value, as it also indicates hard page faults. If the latter is also high, it indicates insufficient memory on the machine
Pages/sec and Page faults/sec are two of several most important memory performance metrics. Their high values don’t necessarily indicate memory problems. Sometimes it’s normal and checking other memory metrics is necessary to determine whether there’s a serious issue with performance. However, these metrics should not be skipped in any SQL Server performance monitoring scenarios
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