Using Database Engine Tuning Advisor can lend a great deal to a DBA. All you need to do is plug in a query, hit start and viola, instant 99% performance increase estimates. OK, it might not be all that but it can give you some pretty good suggestions for increasing performance. Is this too good to be true? In some cases, yes, it is.
Let’s look at something I just ran across. I do actively use DTA. It’s a great tool and it makes tuning extremely large queries quick with reviewing suggests along with execution plans to ensure recommendations are sound. Even better and my primary reason for using DTA is it writes the CREATE statements for you. Remember, a lazy DBA is actually a more efficient DBA.
This morning however, DTA was being less than helpful.
A query went by my monitoring tools that sent my disk queue length threw the roof. Well, it went to 8 but as most DBAs know, 3 will make us squirm in our chairs. Once I reviewed the query, it wasn’t really all that bad. In fact, I think it was well written by the vendor. Checking the execution plan I could see there was just some covering index needs to ensure parallelism and some key lookup steps were removed. So I opened DTA already knowing pretty much what I wanted to do and ran the query through it. The results were about what I expected but looking closer I saw this
CREATE NONCLUSTERED INDEX [_dta_index_9_379148396__K1] ON [dbo].[WHDR] ( [WO_ID] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
Why is that a problem you might ask? Well, it might not be a problem if you are working on a HEAP table and that typical work order ID is really in need of a nonclustered index. At that point we would want the awesomeness of a clustered index anyhow.
Here is the biggest problem. There is a clustered index on the table and that clustered index only consists of the WO_ID. Yes, that would be an extremely big problem as far as performance goes. You would have two indexes doing the same thing and on every change to the table, you would be updating them at the same time. In a large table that cost can show quick and become painful to the users.
So my tip for the day; don’t forget DTA as a beneficial tool to be the lazy DBA we all speak of. It can truly help you even if that is simply taking some code writing shortcuts. But, believe it as much as you believe the first Google hit you get back when researching a problem. As Janice Lee (Twitter | Blog) says in Save Me, Google, “Google told him to. He was in trouble, he panicked, and he trusted his salvation to Google.” Well, don’t fall into the points that Janice put together so well in that blog. Even the tools that are meant to assist us in doing our jobs better can be wrong. They are suggestions that require as much review as anything else going into your database servers.
7 Comments
You know what…I have never use DTA and I believe I have used the Index Tuning Wizard in SQL Server 2000 only once or twice. Maybe it is because I am a developer and I know when looking at a query what I should have in terms of indexes.
The Dynamic Management Views are also a great help to discover unused indexes…because you know sometimes people get ‘creative’ and ‘excited’ when adding indexes, they see the performance improvement when adding an index on a column and then think if one index is good then many must be better. So before you know if you have more indexes than columns 🙂
There are queries that could benefit from additional index, same as clustered one.
Simplest one would be Select WO_ID from WHDR or Select WO_ID from WHDR where WO_ID between @p1 and @p2
In most cases you don’t need something like that, except if you have really a lot of those queries and your row size is much bigger than WO_ID size.
Hey Ted,
I agree with niikola. The DTA appears to be correctly considering the density of the index when making its recommendation (the clustered index is the least dense index possible).
See this link for an example, and an analysis of the performance implications.
Paul
As an independent consultant I have made a LOT of money over the past 5 years cleaning up the messes that clients have created for themselves by using the DTA to perform their indexing strategy. It is truly HORRID in sooo many ways. I do appreciate the fact that it is out there and provides me with lots of work though!
Thanks for the comments everyone. Good stuff!
An informative article keep it up!
There are many and many benefits of having an index on WO_ID column, something that you can check yourself by creating the index and comparing the IO and time statistics as well as the CPU and IO cost.
Farther more SQL Server will most of the time use a nonclustered index not just if the WO_ID column s the first or any column in the index but also if it is the second column! sometimes even when there is a nonclustered index with WO_ID as its primary column.
This also has major implications, consider a query “SELECP TOP 1 WO_ID FROM WHDR” while one might expect that it will return the actual first entry, it is not the case because it might turn out to use an index which WO_ID is the secondary column (based on the internal cost staistics), and to get correct results one must add “ORDER BY WO_ID” or use an index hint to force the desired index.