Find queries that generate user seeks on missing index DMV's

Question

Find queries that generate user seeks on missing index DMV's

SkyBox

SSCarpal Tunnel

Points: 4288
More actions
July 22, 2011 at 3:14 pm

#244556

Just beginning to dig into DMV's. Wondering if I can tie the query below to another DMV that will show the full query text?
I need to test the effectiveness of adding the missing indices, query by query. Recommendations?
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage]
, migs.user_seeks
, migs.last_user_seek
, mid.[statement] AS [Database.Schema.Table]
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
, migs.unique_compiles
, migs.avg_total_user_cost
, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig (NOLOCK) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid (NOLOCK) ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY ORDER BY index_advantage DESC
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No, there's no DMV you can join to to get the query text. The optimiser just drops the missing index info in there, it doesn't tie it to a query.

If you're feeling brave you can interogate the plan cache, looking for the <missing indexes /> elements in the plan's XML.

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 SSC Guru Points: 281334 More actions · Answer 2

And along the note that Gail mentioned, read this blog post.

http://sqlserverpedia.com/blog/sql-server-2005/can-you-dig-it-%E2%80%93-missing-indexes/">

http://sqlserverpedia.com/blog/sql-server-2005/can-you-dig-it-%E2%80%93-missing-indexes/

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

And Brad Schulz wrote an interesting blog post http://bradsruminations.blogspot.com/2011/04/index-tuning-detective.html

It does delve into the query cache and although I cannot attest to it's usefulness as I haven't tried it yet, it does make for interesting reading and he has some sample queries that you might like.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);