Our application developers run a lot of SELECT *
queries against our database system, unfortunately.
The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to create a 100% covering non-clustered index for those cases. For example actually there is an index with 3 index columns and additional 25 included columns suggested. Other indexes with the key columns suggested are already in place but then require lookups.
I am a little surprised about this. However since the underlying queries are running very often with high costs and the suggested index comes with an impact of about 50% this is maybe an interesting option.
I know it is not best practice and bad design to support those queries, but I doubt this could be changed within adequate time, so I wonder if those indexes will help at all or produce more costs than benefit?
Is there a rule or do I just have to try / error the consequences?
- Row counts are between 60,000 and 2.1 million.
- The missing indexes DMVs tell me on average (over multiple servers) 2424 seek operations could be performed for the queries (this is a number counting up continuously for about 3 months and the query cost of 19 in average could be impacted positively of about 50%.
- Removing
SELECT *
would be best, but I am asked to optimize it as is, and since SQL Server itself suggests this, I think it is worth thinking about it anyway. - The existing queries are not really slow, < 1 sec, but this is quite subjective as well I think. Execution plan tells the key lookups cost 80% of the whole, so I guess saving this might be desirable anyways.
I did some tests without / with the 100% covering index. Results:
SELECT *
: Costs before: 0,026 after: 0,00329 (new index used) = -88% improvement
UPDATE
: before: 0,043333 after: 0,0533 (ix used) = +20%
INSERT
: before: 0,1133 after: 0,1233 (ix not used)=equal
DELETE
: before: 0,645 after: 0,655 (ix used) =equal.Conclusion: It tunes the selects much more than it slows down the changes. Why not apply?