Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query source for Missing Index Management Views Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 9:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
Points: 3,105, Visits: 11,494
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())







Post #1598318
Posted Saturday, August 2, 2014 5:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:10 PM
Points: 1,914, Visits: 5,271
Did you look at Greg's Robidoux article Using SQL Server DMVs to Identify Missing Indexes?
Post #1598922
Posted Saturday, August 2, 2014 9:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
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 2008, MVP
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

Post #1598940
Posted Saturday, August 2, 2014 10:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 17,628, Visits: 15,487
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1598949
Posted Saturday, August 2, 2014 11:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:10 PM
Points: 1,914, Visits: 5,271
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.
Post #1598955
Posted Tuesday, August 5, 2014 7:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:00 AM
Points: 56, Visits: 352
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.


Post #1599700
Posted Tuesday, August 5, 2014 7:38 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 17,628, Visits: 15,487
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1599709
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse