Give this article a Google +1!
Issue 6 Number 8 August 2016Tweet This Article.
In its quest to sell SQL Server as a database that can be installed and maintained by someone who is completely untrained, Microsoft has added some questionable functionality to SQL Server over the last few versions. For example, clients may believe that the push of a button will create all the indexes needed for good performance. Any DBA who has ever invoked the tuning advisor knows that this is patently untrue.
The truth is that the missing index algorithm that Microsoft touts is riddled with problems. If you blindly create the indexes it suggests, you will have plenty of costly unused and unusable indexes. You may have duplicate and overlapping indexes. Along with those you will find some usable indexes.
If you look at the results of the missing index algorithm you will see that it depends heavily on creating nonclustered covering indexes which often have a dozen or more included columns. Adding these huge indexes to your database can be a performance problem in itself.
If you are not familiar with covering indexes, click here.
It is true that included columns take up much less space than the other columns in the index. That mitigates one problem but still, each time a row that is in this index is inserted, deleted or updated, the index will need to be updated too. There is a cost to this operation, sometimes a considerable cost. Also frequent changes in the data also often lead to rapid fragmentation of the index.
With a little bit of training, human intelligence can decide whether the cost of this index is likely to be higher than its benefits. The missing index algorithm cannot make this decision with any degree of accuracy.
It's very,very fast. In a few milliseconds it can gather important metadata about statistics, size and characteristics of the table and the column. Using that data it can make decisions about the indexing. However its decisions are of the cookie-cutter variety. It cannot know how this table is going to be used or the many other critical considerations necessary when creating indexes.
After reading what I wrote above, you might think I don't use the missing index very often, but I do use it almost every day in my work as a SQL Server consultant. there is a way to use the speed of the algorithm with much slower but more flexible human intelligence.
Here is the way I use this combination:
When I am confronted by one or more suggested indexes I do an eyeball scan to find those that are most likely to be useful at a reasonable cost. Then I create a few of the suggested indexes on an active database, either a test server or (with permission) the production server. Then I monitor the use of the index If the optimizer uses it often enough, I keep it. If not, I drop it.
Here is the code I use to look at index usage by table.
I have to be careful when dropping the indexes, but sometimes I can see immediately that one is useful. Yesterday I created a missing index that took off immediately. Within 15 minutes it had been invoked by the optimizer for 20,000 seek operations.
this index was a keeper. However there may be important indexes that will only be used for certain operations like end of month processing etc. It is important to be patient and make sure before dropping what might be a critical index.
As you can tell, Missing Indexes is not my favorite feature in SQL Server, but that is probably because it is so often misused. Used as we show here, it can save you a lot of time in tuning a database.
I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.
As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.
Kurt Survance, SQL Consulting, Inc.
On this page:
Elsewhere on this site: