In my commentary "Will Database Tuning Become Obsolete?" I considered the question of whether query tuning would become obsolete with super-fast disks having the potential to make the cost of accessing data almost negligible. Because the main purpose of query tuning is to enable SQL Server to find a plan with the least amount of page reads, if we no longer have to worry about limited reads, tuning will lose its importance. The answer to my question seems to be no, at least for the foreseeable future. Our queries will still require tuning, and we still must design the most appropriate indexes to get the required data as efficiently as possible.
So my question today is if we can’t eliminate tuning, can we automate it? Can tools be built to do what a SQL Server performance tuning expert does?
About 12 years ago, when I was in the process of writing my first book, I talked to one of the engineers on the SQL Server development team at Microsoft about a new technology the team was developing for SQL Server 7.0 that would do your tuning work for you. The tool was called the “Index Tuning Wizard.” I had a brief moment of panic thinking that perhaps I, and other SQL Server performance consultants, might become obsolete if things were made too easy.
However, I needn’t have worried. In some cases the Index Tuning Wizard (renamed and expanded into the Database Engine Tuning Advisor—DTA—in SQL Server 2005) can help you create reasonable indexes, but the tool still isn’t a substitute for someone who knows how SQL Server really works, and someone who knows all the details about the data that’s being stored and accessed, as well as the kinds of queries that are submitted.
Now, three versions later, great strides have been made. SQL Server 2008 offers a new tool, which is actually a set of components, called the management data warehouse. The following are the components of the management data warehouse:
- The Data Collector, which gathers data from various sources, including T-SQL queries, Dynamic Management Views, and performance monitor counters.
- A database (by default called MDW) that holds the collected data.
- A set of drill-down reports for exploring the collected data.
Now should I be worried?
The reports can make suggestions, but like the Index Tuning Wizard and the Database Engine Tuning Advisor, they’re not always the best. They’re based on the queries that were run while performance data was being collected, and there might be other queries that you know will be run eventually but aren’t included in the collections to date. As for the blocking problems, any solution is just a temporary fix. The reports don’t provide any information about why the blocking occurred.
To make maximum the use of the information provided in the reports, you have to understand the way SQL Server can use indexes and why indexes are good in some situations and not good in others. You have to understand what kinds of locks SQL Server can acquire, how longs the locks are held, and when those locks can cause blocking. So education is a necessary prerequisite to using the reports effectively. And since I make my living primarily as a trainer and writer, trying to educate people on how SQL Server works, it seems like there’s nothing to worry about at all.
The management data warehouse is an incredibly useful tool for tuning your queries and applications in SQL Server 2008, but it’s just a tool. It’s up to you to learn how to use it effectively.