A couple years ago, I was interviewing for a new job and one of the interviewers I met with asked me how I would go about tuning a query to improve performance. One of the options I mentioned was to run the query through the Database Engine Tuning Advisor, although, I added, its results should be taken with a grain of salt and not blindly implemented. This seemed to take the interviewer by surprise and he asked me to elaborate. I did so, and I will do so again here in more detail.
The Database Engine Tuning Advisor is a great tool that Microsoft started including with SQL Server 2005. It can
take a query and look at the tables used in the query and recommend steps to improve performance. It can recommend adding clustered and non-clustered indexes, table partitions, statistics, and indexed views, to name a few. It will also report the performance improvement it thinks its changes will achieve. It’s a great tool to have in your DBA toolbox.
However, as with most cases of automated analysis, a human should carefully review the recommendations and not blindly implement them. Why? There are a couple of reasons.
First, the DETA works in a vacuum. All it knows about your server is the query you gave it and the current database structure – indexes, statistics, etc. It has no idea of the server workload. Perhaps the query you are optimizing is one used once a month for reporting on an OLTP system that is handling thousands of updates and inserts a minute. Any added indexes that DETA recommends will slow down access to the table because now SQL has to update additional indexes for each DML operation. This could adversely affect performance. (Note: you can give DETA a SQL Profiler trace file as an input and it will consider that entire workload as it evaluates options, but in this case I am talking about optimizing a single query. You cannot do this and provide a workload file at the same time, unless the query is part of that workload.)
Second, I’ve seen DETA make some recommendations that are redundant. Well, technically, they are not redundant as the recommendations are not identical. But they are close enough so that only one of the two would be needed. For example, yesterday, I was using DETA and it recommended two indexes on a table. One index was just on Column A. The second recommended index was on Column A with Column B as an included column in the index. Technically, those aren’t redundant, but in practice, the second index will provide all the functionality of the first.
Lastly, DETA can only provide recommendations that fit the parameters it has been given. Here is a good example. I’ve got a table in a vendor database that contains approximately 450 million rows. There is no clustered index. (I know, I know. Ridiculous.) The table takes 45 GB of space. There are three non-clustered indexes on that table. As a test, I had DETA tune a simple query that did a SELECT * FROM Table WHERE ColumnF = <some value>. ColumnF was not included in any of the existing indexes. Because this table is so big, I do not want DETA to recommend creating a clustered index. Creating that would require reordering all the data on the disk and would likely take a very long time. So I checked the option to recommend nonclustered indexes only.
What was the result? DETA recommended a nonclustered index on ColumnF with all the other fields in the table listed in the INCLUDE clause of the index. (Basically, a clustered index in all aspects except name.) This would result in a 99% performance improvement! Sounds great, right? But DETA also reports this index will require another 45 GB of disk space.In effect, DETA just told me to double the size of my table. Plus there is the before mentioned impact of DML commands having to update an additional index.
Clearly, it would be unwise to blindly accept this recommendation. A better option would be to leave off the included columns. SQL will need to do a lookup to get the other fields, but when compared to the additional storage requirements and index upkeep during DML operations, this is a small price to pay.
These examples show why it is important for an experienced DBA to carefully review the recommendations of the Database Engine Tuning Advisor and not just blindly implement them. As with any advice, the DBA is free to do with it as he or she pleases, including ignore it or just take selected bits.
That’s my advice. Do with it as you please. :-)