Query source for Missing Index Management Views

  • 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())

  • Did you look at Greg's Robidoux article Using SQL Server DMVs to Identify Missing Indexes[/url]?

    😎

  • 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
  • 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[/url]
    Learn Extended Events

  • 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.

    😎

  • 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 [/url]
    Gail Shaw's Performance Blog[/url]

  • 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