Drum Roll, Please…The Debut of The SQL DMV All-Stars Dream Team!

Author's note: Though I originally posted this six years ago, it's still one of my most popular posts.  I still use the queries & I've updated them a bit.  Enjoy!
Jimmy May

The introduction of Dynamic Management Views (DMVs) in SQL Server 2005 provides us with all manner of wonderful information, much of which was heretofore difficult or even impossible to get.

Yet I often hear confusion about their use.  People ask, "But Jimmy May, there are so many of them—which are the most important ones?  Where do I begin?"

You can begin right here.

 I have drafted a team of five first-round picks which comprise my DMV All-Stars Dream Team. They are simple to use & provide a rich source of useful information.

 The chosen queries provide information on the following topics:

·         Expensive Queries (CPU, reads, frequency, etc.)

·         Wait Stats

·         Virtual File Stats (including calculations for virtual file latency)

·         Plan Cache

·         Blocking (real-time)

 I have provided backward-compatible versions for three of them. The first has no equivalent in SQL Server 2000.  The fifth has an equivalent, but would require a big post all its own.

 These are among the most frequent DMVs I use—that's why I put them in one place.  I hope you find them helpful as well.  There’s not much unique in what I’ve provided (though I do have a surpise for a forthcoming post).  In fact, you're likely to recognize all of them.  Some of them I've liberated right out of BOL (2005 2008).  (I've even included one directly from the SQL Server Customer Advisory Team's (SQL CAT) Best Practices Toolbox).  The point is, ladies & gents, goyles & geeks, you have here in one place many of the DMVs you’ll need for much of your routine performance work.

 There were many worthy queries which I have not included.  Perhaps someday I’ll draft a team of bench warmers which might include, e.g., DMVs for indexes.  If you don’t like my picks, nominate DMVs for your own  team.

 Adding the DMV All-Stars to your roster will help give you a winning game plan!

 Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev | Senior Program Manager, SQL Server Customer Advisory Team (SQL CAT)
Performance is paramount: Asking users to wait is like asking them to leave.


Comments (9)

  1. cjgunn says:

    Great Post Jimmy. This is a must for every SQL consultants toolbox.

  2. Jimmy May says:

    @cjgunn:  Thanks for the validation.  I plan to dedicate discrete posts to each of the five All-Stars.  Stay tuned for the extraction of "virtual file latency" from virtual file stats!

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

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

  5. SP_who2 has become a very handy tool for administrator, DBAs developers or all who are the All-In-One

  6. Jimmy May has compiled a great white paper on Disk Partition Alignment Best Practices for SQL Server

  7. mbourgon says:

    nvm my previous comment – while the link in the article doesn't work, the attachment at the end does.

  8. Jimmy May says:

    @mbourgon: Yes, you're right.  MSDN & TechNet blogs have recently undergone an "upgrade", but in-line links such as these were broken & must be manually remediated.  I've done so here & have several posts in the queue.  Apologies for the inconvenience, and kudos for figuring it out–a lot of folks weren't as persistent as you.

Skip to main content