Now, this post is one that I’ve had in the pipeline for quite a while but I’ve always been VERY reluctant to write it… why, I hear you ask… well it’s because it’s incredibly dangerous…
We’ve all seen the “Missing Index” hint in our query plans (and if you haven’t then contact me and I’ll fill you in)… and I hope we’ve all taken them with a pinch of salt… but sadly MANY, MANY people don’t… and if you’re one of those then please read the many caveats in this post…
Okay, I’m going to dive right into this… PLEASE take the green “Missing Index” hint with a pinch of salt… I won’t ever deny that it CAN be useful, but I prefer to see it as an indication and nothing more.
What I mean is that the indicator COULD be correct, but whichever way you consider it, it’s an indication that your query isn’t optimal… that’s all… I would much rather you pulled up the execution plan and trawled through it with a fine tooth comb than simply accept the recommendation. I don’t want this post to become a rant into the pros and cons of the Missing Index text so I’ll stop there… but please don’t take it as gospel… it isn’t… it’s an indicator.
Now, with that small rant out the of the way, I’ll continue…
This query is built on top of the missing index information, but I want you to take it as an indication as to which queries tables you need to analyse, look at, and potentially tune to satisfy the workload you’re running… I don’t want you to simply create the indexes it suggests…
select s.avg_total_user_cost * (s.avg_user_impact / 100.) * (s.user_seeks + s.user_scans) cost,
d.statement, d.equality_columns, d.inequality_columns, d.included_columns,
s.*, d.database_id, d.object_id
from sys.dm_db_missing_index_groups m
join sys.dm_db_missing_index_group_stats s
on s.group_handle = m.index_group_handle
join sys.dm_db_missing_index_details d
on m.index_handle = d.index_handle
where s.avg_total_user_cost * (s.avg_user_impact / 100.) * (s.user_seeks + s.user_scans) > 10
and d.database_id = db_id()
order by statement
This query will highlight the indexes that the SQL Server “Missing Index” engine has determined could help… it will give you costings and it will give you key and include columns… it’s an incredibly powerful query to let you know which tables are being hit in a way that you may not have realized and that you can subsequently cater for… hopefully you’ll find it useful and it will help you to add or amend existing indexes to better support your workload… just PLEASE don’t create everything just because it suggests it.