Share via

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

  • Article

<#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
2015-01-14
#>

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.

DMV_All-Stars_v20150114.sql

Comments

  • Anonymous
    November 02, 2008
    The comment has been removed

  • Anonymous
    November 16, 2008
    @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!

  • 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
    April 03, 2009
    SP_who2 has become a very handy tool for administrator, DBAs developers or all who are the All-In-One

  • Anonymous
    April 27, 2009
    Preamble For those of you who are already facile with SQL Server wait stats, there’s nothing new here.

  • Anonymous
    May 14, 2009
    The comment has been removed

  • Anonymous
    July 06, 2010
    nvm my previous comment - while the link in the article doesn't work, the attachment at the end does.

  • Anonymous
    July 06, 2010
    The comment has been removed