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.
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
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.
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
Another nice one. I’m going to have to explore this more thoroughly.
Nice. I’ve been meaning to write something like this for a while.
Guess I need to learn XQuery sometime.
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.
[…] 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 […]
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;
Thanks. That’s a nice one. I ended up writing my own. This was the latest version I believe:
http://scarydba.wordpress.com/2009/03/02/more-refinements-on-the-missing-indexes-query/
Thanks for the latest link, looks good.
I find this XQuery stuff really interesting.
[…] – an XQuery. Sunglasses on please. That query, originally found in his article here: Missing Index Information and Query Stats, is listed […]