In the previous parts of the SQL Server performance metrics series, we presented most important SQL Server memory metrics that indicate overall SQL Server memory pressure. The described metrics show how SQL Server uses memory and physical I/O to store, read and write data pages. In this part, we continue with SQL Server Buffer Manager metrics and memory counters

Page reads/sec

“Page reads/sec indicates the number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.”[1]

In other words, this shows how many times the pages were read from disk, in a second. Please note that this is not the number of pages read from disk (which is the Pages input/sec metric described below). This is a server-level metric, the number indicates page reads for all databases on the instance

The recommended Page reads/sec value should be under 90. Higher values indicate insufficient memory and indexing issues

The value can be obtained from the sys.dm_os_performance_counters system view

Page writes/sec

The Page writes/sec metric is similar to Page reads/sec and shows the number of times pages were written to disk on the server level

“Indicates the number of physical database page writes that are issued per second.”[1]

The value can be obtained from the sys.dm_os_performance_counters system view

The same as Page reads/sec, the recommended value for Page writes/sec is below 90

High paging and disk I/O activity indicate insufficient memory. If the excessive disk activity is not caused by paging (indicated by normal Page Faults/sec values, compared to the defined metric baseline), Page reads/sec and Page writes/sec will be high. In this situation, it’s recommended to check the Lazy writes/sec and Page Life Expectancy values as well, as the non-zero Lazy writes/sec and low Page Life Expectancy require attention [2]

Pages Input/sec and Pages output/sec

Pages Input/sec and Pages Output/sec are memory counters

Pages Input/sec is defined as “the number of pages brought in from disk every second. The difference between this value and Page Faults/sec represents soft page faults.”[3]

Pages Output/sec is defined as “the number of pages written to disk every second to make room in the working set of the process for newly faulted pages. If the process modifies the pages, they must be written out. They cannot be discarded.”[3]

In other words, these metrics show how many of the requested pages not available in memory had to be read from and written to disk in order to resolve hard page faults

The Pages/sec metric is the sum of Pages Input/sec and Pages Output/sec. If the Pages/sec value is constantly higher than 50, to confirm that hard page faults are happening, additional investigation is needed. It’s recommended to monitor disk behavior and paging via memory and disk counters, such as Pages Input/sec, Pages Output/sec, Disk Reads/sec, and Avg. Disk Read Bytes/sec

Another metric that is closely related to Pages Input/sec and Pages Output/sec is Page Faults/sec. It shows both hard and soft page faults. As soft page faults don’t affect SQL Server performance, it’s good to check the Page Reads/sec value first and compare it to Pages Input/sec. If the latter is greater, it indicates a high page fault rate that can be solved by increasing memory designated to SQL Server

Both Pages Input/sec and Pages Output/sec values are recommended to be lower than 10

SQL Server Buffer Manager Page reads/sec and Page writes/sec metrics, and Pages Input/sec and Page Output/sec memory counters help identify and solve performance issues, such as insufficient or inadequately configured memory. They show the number of times the pages were read/written from disk and the total number of pages read/written. The threshold is clearly defined so performance issues can be diagnosed without creating a baseline first