SQL Server Performance, Best Practices, & Productivity
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.
Thanks for sharing such valuable info. I am already planning to attend the Vital Signs workshop
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
What is the procedure to register for the Vital Signs workshop? Can non-microsoft employees attend the same?
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?
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.