Jimmy May's Blog

SQL Server Performance, Best Practices, & Productivity

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

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

Rate This
  • Comments 13

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.

Attachment: PerfMon_Counters_Digest_w_Vital_Signs_Correlation_v6.xls
Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
  • Jimmy!

    Thanks for sharing such valuable info. I am already planning to attend the Vital Signs workshop

    alikl

  • These are normal questions: What counters should I select to monitor my SQL Server instance? What thresholds

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

  • Introduction This is the second of a four-part series: Introduction to Query Parallelism Parallelism

  • Hi,

    What is the procedure to register for the Vital Signs workshop? Can non-microsoft employees attend the same?

    Thanks,

    Hari

  • 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!

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

  • Can someone explain difference bwetten SQL Batches/sec, Transactions/sec?

  • Jimmy,

    I came across a video of Shane Creamer speaking with RunAs Radio.  They mention the Vital Signs workshop but also show a "25 Key OS Performance Metrics" PDF document that Shane states is available to the public.  I have gone through Google and Bing and cannot locate it.  Have you heard of this document or know where I might find it?

  • I contacted Shane & he already replied.  What a great guy, eh?  Here's what he gave me:

    Richard and Greg dig deep into PerfMon with Shane Creamer. Shane works with Microsoft Premier Field Engineering and runs the Vital Signs workshop, a three day course teaching everything the administrator needs to know about performance monitor.

    Download the video here: http://libra.franklins.net/runasradio_0081_shane_creamer.wmv

    Also, a link to the 25 key perfmon counters is in Stephen Choy's article from TechNet mag: http://technet.microsoft.com/en-us/magazine/cc718984.aspx?pr=blog

    Be clear about something.  Steven's article is for acute troubleshooting, not monitoring.  So, for example, he leaves out metrics for disk throughput & IOPs--critically important for day-to-day monitoring (& I would argue for troubleshooting as well).

    BTW, I have a couple of refinements to make to this post, so stay tuned, eh?

  • @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.

  • Jimmy, excellent work - easy question...does the X mean the relevant party excludes or includes the said counter?

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

Page 1 of 1 (13 items)