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.
What’s Good About the Missing Index algorithm?
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.
How Should I Handle This?
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.
- declare @tablename sysname
- declare @database_name sysname
- set @database_name = db_name() — must execute from the current database
- set @tablename = ‘YourTableName’ — replace this with the table name.
- — exec sp_helpindex @tablename — uncomment to see columns in index
- select getdate() as date
- , db_name (dm.database_id) as DBName
- , object_name(dm.object_id) as tablename
- ,i.name as indexname
- ,case i.index_id when 1 then ‘Clustered’ else ‘Nonclustered’ end as ‘IdxType’
- ,dm.user_seeks
- ,dm.user_scans
- ,user_lookups
- ,user_updates — this is the number of times the index had to be updated
- ,last_user_seek
- ,last_user_scan
- ,last_user_lookup
- ,last_user_update
- from sys.dm_db_index_usage_stats dm
- join sys.indexes i on i.index_id = dm.index_id
- and dm.object_id = i.object_id
- and dm.object_id = object_id(@tablename)
- and database_id = db_id(@database_name)
- –order by dm.index_id desc
- order by dm.user_seeks desc
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.
Try It
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.