Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Missing Index Information
Published: Dec 02, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.