In the world of SQL Server performance tuning, it is crucial to monitor and analyze various metrics to ensure optimal database performance. One set of metrics that plays a significant role in this process is the SQL Server Buffer Manager metrics. In this article, we will explore these metrics and understand how they can help identify and solve performance issues.

Page reads/sec

The first metric we will discuss is the “Page reads/sec” metric. This metric indicates the number of physical database page reads that are issued per second. In simpler terms, it shows how many times the pages were read from disk in a second. A higher value for this metric suggests insufficient memory and indexing issues. To obtain this value, you can query the “sys.dm_os_performance_counters” system view using the following SQL statement:

SELECT object_name, counter_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE [object_name] LIKE '%Buffer Manager%' 
AND [counter_name] = 'Page reads/sec'

The recommended value for “Page reads/sec” is below 90. If the value exceeds this threshold, it is an indication that you need to address memory and indexing issues.

Page writes/sec

Similar to “Page reads/sec”, the “Page writes/sec” metric shows the number of times pages were written to disk on the server level. It indicates the number of physical database page writes that are issued per second. To obtain this value, you can use the same SQL statement as above, but replace the counter name with ‘Page writes/sec’.

Again, the recommended value for “Page writes/sec” is below 90. If the value exceeds this threshold, it suggests high paging and disk I/O activity, which can be a result of insufficient memory. In such cases, it is recommended to also check the “Lazy writes/sec” and “Page Life Expectancy” values.

Pages Input/sec and Pages Output/sec

These memory counters, “Pages Input/sec” and “Pages Output/sec”, provide insights into how many of the requested pages not available in memory had to be read from and written to disk. “Pages Input/sec” represents the number of pages brought in from disk every second, while “Pages Output/sec” represents 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 “Pages/sec” value (sum of “Pages Input/sec” and “Pages Output/sec”) is constantly higher than 50, it indicates the occurrence of hard page faults. In such cases, additional investigation is needed to understand the disk behavior and paging. Monitoring disk counters such as “Pages Input/sec”, “Pages Output/sec”, “Disk Reads/sec”, and “Avg. Disk Read Bytes/sec” can provide valuable insights.

Another closely related metric is “Page Faults/sec”, which shows both hard and soft page faults. Soft page faults do not significantly impact SQL Server performance. To identify if hard page faults are occurring, it is recommended to compare the “Page Reads/sec” value with “Pages Input/sec”. If the latter is greater, it indicates a high page fault rate that can be resolved by increasing the memory designated to SQL Server.

Both “Pages Input/sec” and “Pages Output/sec” values are recommended to be lower than 10. Monitoring these metrics can help identify and solve performance issues related to insufficient or inadequately configured memory.

By understanding and monitoring SQL Server Buffer Manager metrics, you can proactively address performance issues and ensure optimal database performance. Remember to regularly analyze these metrics and take appropriate actions to maintain a healthy SQL Server environment.