If you have spent any time performance troubleshooting your SQL Server, you have probably run across Glenn Berry’s SQL diagnostic queries. In one of these, using the missing index DMVs, Glenn calculates a column called Index_Advantage. I was reminded recently that this value is relative.
Normally when I work with an SQL Server, seeing values of10,000 is high, so when I saw 11 million as the Index Advantage value, I was thinking, something is really bad here. Then I remembered the DMVs are reset in one of three cases:
- When there is a SQL Server restart
- When an index is added to a table
- When an index on a table is rebuilt
In the case of the customer that had values in the millions, they had been running for about 6 months without an SQL Server restart, so the user seeks counts just kept accumulating and the index advantage kept growing.
So how do you effectively use this diagnostic data?
Note that the values in index advantage are not absolute. A value of 1000 after a day since SQL reboot will be just a bad as a value of 10,000 after a few days, or a million after a few months. The top values show the relative important of the index. Use that to prioritize the addition of new indexes. Remember that if you recently added indexes or rebuilt indexes, the affected tables will not show high on this table. I will try to schedule a SQL Server restart after doing index tuning so that I can get fresh counts from the DMVs.
There is a lot more to choosing appropriate indexes. Hopefully this reminds you of one 'gotcha' as you are doing your tuning work.
