Dive Into DMVs

SQL Server 2005 Dynamic Management Views (DMVs) might not be as important to humankind as the invention of the wheel or the taming of fire, but in the life of a SQL Server professional, they'll probably rank pretty high on the scale of significant advancements. I've been excited about DMVs since I first learned about them a few years ago. At face value, DMVs might seem to be nothing more than a new-and-improved version of system tables--and who can get too excited about system tables? But during last week's Professional Association for SQL Server (PASS) Summit, I attended some really cool Microsoft presentations that helped me have a much better appreciation for the effect DMVs will have on my ability to support, manage, tune, and otherwise interact with SQL Server.

DMVs essentially make your server more transparent. SQL Server 2000 and earlier editions don't make it easy to solve important tuning, troubleshooting, and supportability problems. As a consultant, I think that's wonderful, so I have mixed feelings about DMVs.

I always knew that DMVs would eventually be a huge leap forward in solving practical problems, but I honestly thought the initial DMV model would be sorely lacking in real-world practicality. No offense to product development teams at Microsoft and elsewhere, but sometimes it can be easy to build something really cool but that doesn't necessarily solve a problem for a front-line DBA in the field.

However, I was pleasantly surprised that Microsoft has made DMVs quite practical. I'm a performance-tuning geek at heart, so that's the DMV use I explored most at PASS. I was pleased with what I saw. I suspect that Microsoft did such a good job of hitting the target because it relied on input from two teams--Microsoft Product Support Services (PSS) and the SQL Server Customer Advisory Team--that truly understand the most pressing real-world problems that high-end customers face. Most people are familiar with PSS and its role in solving customer problems, but many customers have never heard of the SQL Server Customer Advisory Team. This team works to solve the biggest-of-the-big and hardest-of-the-hard SQL Server problems around the world. I know some of the smartest SQL Server people in the world, and every member of the SQL Customer Advisory Team is on that short list.

PSS, the SQL Server Customer Advisory Team, and the SQL Server development team have done a great job of making DMVs practical instead of just cool. Think about how hard it is to figure out what indexes SQL Server isn't using; it's really hard in SQL Server 2000. But it's really easy with SQL Server 2005 DMVs. Are you interested in knowing the aggregate cost of a query, the affect of parallelization versus serialized query plans, or the cost of query compilations? Answering those questions is really hard today and really easy in SQL Server 2005 because of DMVs and supporting technology in SQL Server 2005.

I could go on and on--and I probably will because I plan to make understanding DMVs inside and out one of my core specialties. For now, I'll simply paraphrase a quote from SQL Server Customer Advisor Team member Tom Davidson. In one of his PASS Summit presentations, Davidson summarized the possibilities of DMVs by saying, "Transparent servers are a reality in SQL Server 2005." I couldn't agree more. With DMVs, it's surprisingly easy to see what's really happening inside SQL Server 2005, and this visibility greatly improves supportability, manageability, and tenability. Although I understand that many of the DMVs won't be documented until RTM, all of them are included in the September CTP. So do yourself a favor and dive into the new DMV model.