SQL Server Performance, Best Practices, & Productivity
<#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 May2015-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.
Great Post Jimmy. This is a must for every SQL consultants toolbox.
@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!
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
SP_who2 has become a very handy tool for administrator, DBAs developers or all who are the All-In-One
Preamble For those of you who are already facile with SQL Server wait stats, there’s nothing new here.
Jimmy May has compiled a great white paper on Disk Partition Alignment Best Practices for SQL Server
nvm my previous comment - while the link in the article doesn't work, the attachment at the end does.
@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.