PerfMon Objects, Counters, Thresholds, & Utilities for SQL Server

  • Articolo

I’m frequently asked questions about which PerfMon counters to use & what thresholds to rely on. I’ve been amalgamating this information for years in the form of an Excel workbook: PerfMon_Counters_Digest_w_Vital_Signs_Correlation_v6.xls.

 

The current incarnation of this workbook includes worksheets related to the following topics:

· Performance objects for SQL Server 2000, 2005, & 2008

· Information related to logman.exe which I use to create, stop & start PerfMon counter logs

· List of counter thresholds

· I/O notes & references

 

An Excel workbook cannot easily provide a comprehensive primer. However, if you already have a working knowledge of PerfMon, you should be able to hit the ground running with the counters & thresholds documented here. As stated this is a work-in-progress which will from time-to-time be modified (& hopefully improved!).

 

Microsoft PFE Shane Creamer is the Yoda of PerfMon. He has created a workshop called Vital Signs. It is already available for public consumption, and he’s rumored to be updating it soon to a formal SKU. Whether you’re new to PerfMon or want to take your skills from Padawan to Jedi Master, the Vital Signs workshop is for you. Stay tuned for details.

 

I invite any insights & feedback.

PerfMon_Counters_Digest_w_Vital_Signs_Correlation_v6.xls

Comments

  • Anonymous
    October 15, 2008
    The comment has been removed

  • Anonymous
    November 05, 2008
    These are normal questions: What counters should I select to monitor my SQL Server instance? What thresholds

  • Anonymous
    November 28, 2008
    This is the first of a four-part series: Introduction to Query Parallelism (this post) Flipping the Bit

  • Anonymous
    December 02, 2008
    Introduction This is the second of a four-part series: Introduction to Query Parallelism Parallelism

  • Anonymous
    February 03, 2009
    Hi, What is the procedure to register for the Vital Signs workshop? Can non-microsoft employees attend the same? Thanks, Hari

  • Anonymous
    February 05, 2009
    Hari, I spoke to Shane Creamer, the author of Vital Signs.  This is his reply, "The Vital Signs workshop is available for customers with a Premier service contract since considerable money was spent to create the materials.  It is my hope that someday this will be a externally SKU’d class that can be taught at any Microsoft certified training center." I hope this helps.  Good luck!

  • Anonymous
    March 15, 2009
    SAN Over-Subscription There are a variety of challenges related to I/O performance. One common problem

  • Anonymous
    October 28, 2009
    Can someone explain difference bwetten SQL Batches/sec, Transactions/sec?

  • Anonymous
    December 15, 2009
    The comment has been removed

  • Anonymous
    December 15, 2009
    The comment has been removed

  • Anonymous
    December 22, 2009
    @Sudhir: A transaction consists of one or more batch(es).  The Batch Requests/sec counter belongs to a SQL instances SQL Statistics performance object.  It is an easy-to-reference metric which can be useful across tests on the same system.  Batches/sec is a server-level metric. Transactions/sec belongs to an instences Databases performance object.  It is more granular, available at the database level (a _Total instance of the counter is also available). Hope this helps.  Let me know if you need additional clarity.

  • Anonymous
    February 25, 2010
    The comment has been removed

  • Anonymous
    March 12, 2010
    Thanks, Matthew.  The X means that it is a "vital" counter, a counter which Vital Signs has deemed authoritative.