SQL Server memory performance metrics Part 3 SQL Server Buffer Manager metrics and memory counters
SQL Server memory performance metrics – Part 3 - SQL Server Buffer Manager metrics and memory counters
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 Latest posts by Milena Petrovic (see all) 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
SQLShack
SQL Server training EspañolSQL Server memory performance metrics – Part 3 – SQL Server Buffer Manager metrics and memory counters
March 3, 2014 by Milena Petrovic 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 countersPage 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 123456 SELECT object_name, counter_name, cntr_valueFROM sys.dm_os_performance_countersWHERE [object_name] LIKE '%Buffer Manager%'AND [counter_name] = 'Page reads sec'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 123456 SELECT object_name, counter_name, cntr_valueFROM sys.dm_os_performance_countersWHERE [object_name] LIKE '%Buffer Manager%'AND [counter_name] = 'Page writes sec' 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 Author Recent Posts Milena PetrovicMilena 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 Latest posts by Milena Petrovic (see all) 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