Missing Index Information and Query Stats

February 12, 2009 at 3:49 pm (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , )


So the goal was to find a way to pull information from sys.dm_exec_query_stats so that we could identify poor performing procedures that were in cache at the moment and combine it with missing index information from sys.dm_db_missing_index_details. We’re combining these because we’re working with a Microsoft Dynamics CRM database that is almost all ad hoc queries and lots of them are against tables with missing indexes. The hope was to identify necessary indexes merely by looking at the longest running queries.

Unfortunately there is no way to combine data from the missing indexes set of DMV’s and all the execution DMV’s that show query stats, execution plan, etc. None of the missing index tables has a plan handle or a plan hash column that would allow you to combine that data with the query data to identify which queries would directly benefit from the index if it were created.

But, if you look at the query plans in sys.dm_exec_query_plan, you can see the missing index information there. What to do? XQuery.

Since the query_plan is stored as XML, simply writing a small XQuery exist() function will do the trick:

SELECT  TOP 10 *
FROM sys.dm_exec_query_stats s
   CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
WHERE  p.query_plan.exist(
‘declare default element namespace “http://schemas.microsoft.com/sqlserver/2004/07/showplan”;
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan//MissingIndexes’) = 1
ORDER BY s.total_elapsed_time DESC

This is a pretty simple example, and yes, in reality you would not want to use SELECT *.  You would want to specify those columns that you were really interested in. Also, this allows you to get performance information from queries that show a MissingIndexes element in the XML of the showplan,  but I haven’t pulled the Missing Index data out and displayed it. That can be done, but I’ll leave it as homework for you for now (and because I’m still having a hard time with XQuery).

A quick nod to Tim Ford for the advice on the DMV’s. Please finish that book soon.

10 Comments

  1. Jason said,

    February 12, 2009 at 9:21 pm

    I wrote one that grabs more info but I couldn’t figure out how to get the columns since there could 1 to N.
    http://sqlserverpedia.com/wiki/Find_Queries_in_the_Plan_Cache_That_Are_Missing_an_Index

  2. scarydba said,

    February 12, 2009 at 9:42 pm

    Nice. Definately more than bit more sophisticated than mine. I’m going to work on it some more just to see what I can do with it. My Xquery is so shaky though it takes me forever to figure out what to do. But the practice is good.

    Thanks for sharing.

  3. Jason said,

    February 12, 2009 at 10:06 pm

    Yah, it is definitely something I have found useful. Here is another one that finds scans that may be because of implicit conversions. Although, xquery makes my head hurt.

    http://sqlserverpedia.com/wiki/Queries_with_Index_Scans_Due_to_Implicit_Conversions

  4. scarydba said,

    February 13, 2009 at 7:20 am

    Another nice one. I’m going to have to explore this more thoroughly.

  5. Gail said,

    February 13, 2009 at 7:58 am

    Nice. I’ve been meaning to write something like this for a while.

    Guess I need to learn XQuery sometime.

  6. scarydba said,

    February 13, 2009 at 8:14 am

    Thanks. I think it’s pretty obvious that I haven’t learned XQuery yet either. More to come on this one I think. We’ve got quite a few Microsoft Dynamics development projects coming our way. They’re all going to need indexing added after they’re developed. Having a mechanism to more quickly identify indexes will be helpful.

  7. Links for the Week of 2009-02-20 | facility9 said,

    February 20, 2009 at 8:01 am

    […] Missing Index Information and Query Stats Grant Fritchey put together a nice little query to pull XML query plans out of the DMVs. Thankfully, these plans can also tell use which indexes are missing […]

  8. Marios Philippopoulos said,

    May 12, 2009 at 12:09 pm

    Hi,

    Here is a query returning missing-index info from cached query plans:

    –initial code taken from:
    –http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/
    —
    –returns Missing-Index info in cached query plans

    WITH
    XMLNAMESPACES
    (
    DEFAULT N’http://schemas.microsoft.com/sqlserver/2004/07/showplan’
    )
    , CachedPlans
    (
    MissingIndexesPortion,
    QueryPlan,
    QueryText,
    RefCounts,
    UseCounts,
    SizeBytes,
    CacheObjectType,
    ObjectType
    )
    AS
    (
    SELECT
    RelOp.op.query(‘.’) AS MissingIndexesPortion,
    qp.query_plan AS QueryPlan,
    st.[text] AS QueryText,
    cp.refcounts AS RefCounts,
    cp.usecounts AS UseCounts,
    cp.size_in_bytes AS SizeBytes,
    cp.cacheobjtype AS CacheObjectType,
    cp.objtype AS ObjectType
    FROM
    sys.dm_exec_cached_plans cp
    CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) st
    CROSS APPLY
    sys.dm_exec_query_plan(cp.plan_handle) qp
    CROSS APPLY
    qp.query_plan.nodes(N’//MissingIndexes’) RelOp (op)
    )
    SELECT
    MissingIndexesPortion,
    QueryPlan,
    QueryText,
    RefCounts,
    UseCounts,
    SizeBytes,
    ObjectType
    FROM
    CachedPlans
    WHERE
    CacheObjectType = N’Compiled Plan’
    ORDER BY
    UseCounts;

  9. scarydba said,

    May 12, 2009 at 1:44 pm

    Thanks. That’s a nice one. I ended up writing my own. This was the latest version I believe:

    More Refinements on the Missing Indexes Query

  10. Marios Philippopoulos said,

    May 12, 2009 at 1:48 pm

    Thanks for the latest link, looks good.
    I find this XQuery stuff really interesting.

Leave a comment