SQL Server PerfMon counters for access methods and buffer manager 2

In this episode of the screencast series "PerfMon Counters for the SQL Server DBA," SQL Server MVP Kevin Kline focuses specifically on SQL Server counters. Kline shares best practices for using access method counters to watch for IO intensive operations, such as full table or clustered index scans and page splits. You'll also learn why it's important to monitor the buffer manager in SQL Server, that is, keep an eye on lazy writer, page utilization and how quickly pages age out of the buffer.

In this episode of "PerfMon Counters for the SQL Server DBA," SQL Server MVP Kevin Kline starts a new discussion and turns his focus specifically on SQL Server counters. Kline shares best practices for using access method counters to watch for IO intensive operations, such as full table or clustered index scans and page splits. You'll also learn why it's important to monitor the buffer manager in SQL Server, i.e, keep an eye on lazy writer, page utilization and how quickly pages age out of the buffer.

Kline also discusses these SQL Server Performance Monitor Counters:

 

  • Ideal ratio for page splits to batch requests and how to get the lowest number by using clustered indexes and setting your SQL Server at a reasonable fill factor.

     

  • Buffer cache hit ratio for both OLAP and OLTP systems.
  •  

  • How a high ratio of page lookups per second to batch requests per second could indicate you have an inefficient query plan and query tuning is in order.
  •  

  • Optimal ratio for page reads/ page writes per second and why high numbers could mean index problems or memory constraints within SQL Server.
  •  


    Sreencast series: PerMon counters for the SQL Server DBA

     Part 1: SQL Server PerfMon counters for tracking Windows memory
     Part 2:  SQL Server PerfMon counters for Windows operating system (OS)
     Part 3: Monitor SQL Server disk I/O with PerfMon counters
     Part 4: SQL Server PerfMon counters for access methods and buffer manager

     

    ABOUT THE AUTHOR:   
     
    Kevin Kline is the Technical Strategy Manager for SQL Server Solutions at Quest Software, a leading provider of award winning tools for database management and application monitoring on the SQL Server platform. Kevin is the President of the international Professional Association for SQL Server (PASS). He has been a Microsoft SQL Server MVP since 2004. Kevin is the lead author of "SQL in a Nutshell" and a co-author of "Professional SQL Server 2005 Database Design & Optimization" and "Database Benchmarking." Kevin writes the monthly SQL Server Drilldown column for Database Trends & Applications and blogs at SQL Server Magazine and SQLBlog.com. Kevin is a top rated speaker, appearing at international conferences like Microsoft TechEd, DevTeach, PASS, Microsoft IT Forum, and SQL Connections. When he's not pulling his hair out over work, he loves to spend time with his four kids and in his flower and vegetable gardens.
     

     

This was first published in April 2008

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close