SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query source for Missing Index Management Views


Query source for Missing Index Management Views

Author
Message
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5864 Visits: 11771
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())



Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15384 Visits: 18614
Did you look at Greg's Robidoux article Using SQL Server DMVs to Identify Missing Indexes?
Cool
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88993 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32958 Visits: 18560
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.
http://bit.ly/rnnrmissidx



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15384 Visits: 18614
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.
Cool
MadAdmin
MadAdmin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 1923
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.

Catch-all queries done right
Gail Shaw's Performance Blog
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32958 Visits: 18560
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search