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