Drum Roll, Please...The Debut of The SQL DMV All-Stars Dream Team!
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). (And sh-h-h-h—don't tell anyone but I've copied-&-pasted one directly from the SQL Server Customer Advisory Team's (SQL CAT) Best Practices Toolbox). Yet, 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 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 fragmentation. 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, MCDBA, MCSE, MCITP: DBA + DB Dev | Senior Performance Consultant: SQL Server | A.C.E.: Assessment Consulting & Engineering Services
http://blogs.msdn.com/jimmymay
Performance is paramount: Asking users to wait is like asking them to leave.
Jimmy May is a Principal Engineer for SQL Server in the Microsoft Assessment. Consulting, & Engineering (A.C.E.) team & is assigned to Team Lee. He is the Visionary-in-Chief of SQL Server Pros & was formerly the Senior Database Architect for one of the world’s largest, SQL Server high-throughput OLTP VLDBs. He is a founder & on the executive committees of both the Indiana Windows User Group (www.iwug.net) & Indianapolis Professional Association for SQL Server (www.indypass.org). He is a recipient of the MS IT Gold Star award, is collaborating with the SQL Server Customer Advisory Team (SQL CAT) on a series of SQL best practices papers, & is a membe of the Microsoft Oracle Center of Excellence. Jimmy lives in Indianapolis, Indiana with his lovely new bride, Phyllis, & Fannie May the Wonder Dog. Contact him at jimmymay@microsoft.com. Visit his SQL Server performance & personal productivity blog at http://blogs.msdn.com/jimmymay.
“In the late 90’s I made a conscious decision to become a geek, & started working with SQL Server in 1999. Since then it’s been quite a ride—going from the Help Desk to DBA to Architect to entrepreneur to Microsoft Principal Engineer & consultant.”
