July 31, 2014 at 9:03 am
I use a query similar to the one below to identify candidate indexes, but I would like to be able to see the queries that actually cause the missing index results.
Does anyone know of a way to find that? I would like to be able to look at the stored procedure first to see if it is a candidate for tuning before I create an index.
select
*
from
sys.dm_db_missing_index_groups mig
INNER JOIN
sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
where
migs.avg_total_user_cost*(migs.avg_user_impact/100.0)*
(migs.user_seeks+migs.user_scans) > 10
and last_user_seek > dateadd(ss,-3600*12,getdate())
August 2, 2014 at 5:49 am
August 2, 2014 at 9:01 am
There's no easy way (and the link previously given just shows the queries to fetch the missing index information, which is not what was asked)
Grant has, on his blog somewhere, a query to shred the contents of the plan cache (don't do that on prod 🙂 ) to fetch the missing index information and associated queries out of the plans. That, of course, only works if the query is still in cache. If it's not, you're outa luck.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2014 at 10:43 am
Here is a query to shred the plan cache for missing indexes.
I am a little more partial to this one. There are updates for it in the works too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 2, 2014 at 11:16 am
GilaMonster (8/2/2014)
There's no easy way (and the link previously given just shows the queries to fetch the missing index information, which is not what was asked)
Nor being implied either, just a simple question as I think Greg's article gives a good overview of the dm's capabilities.
😎
August 5, 2014 at 7:29 am
This will probably be heresy so up front I will apologise to Gail,
but,
what I did was select the proc stats from dm_exec_procedure_stats and order by total logical reads descending.
THEN
Copy and paste into EXCEL.
THEN
Select all objects from DB which have TEXT from sys comments containing the table of my most needed index.
Copy paste into EXCEL.
THEN
Do a vlookup from the procstats and see which procs from your top 10 ( which exist in the syscomment query) are hitting the tables from the missing indexes with the biggest benefit index.
I know that I can create an almighty view to do this but it becomes cumbersome and unreadable, so this is a bit manual but quick and easy for me.
Shouldn't take more than 3 minutes.
August 5, 2014 at 7:38 am
MadAdmin (8/5/2014)
This will probably be heresy so up front I will apologise to Gail,but,
what I did was select the proc stats from dm_exec_procedure_stats and order by total logical reads descending.
THEN
Copy and paste into EXCEL.
THEN
Select all objects from DB which have TEXT from sys comments containing the table of my most needed index.
Copy paste into EXCEL.
THEN
Do a vlookup from the procstats and see which procs from your top 10 ( which exist in the syscomment query) are hitting the tables from the missing indexes with the biggest benefit index.
I know that I can create an almighty view to do this but it becomes cumbersome and unreadable, so this is a bit manual but quick and easy for me.
Shouldn't take more than 3 minutes.
Or you could run the query I provided that already does that for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply