HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

sp_BlitzIndex compilation

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
compilationsp_blitzindexstackoverflow

Problem

I currently use SQL Server 2012 and have BlitzIndex 4.4
not infrequently the execution plan will say I have a missing index on a table. BlitzIndex will often not say any indexes are missing on that table.
question: How are both of them determining that an index is missing? I assumed both were drawing info from the dmv's however I no longer assume that to be the case.

Solution

sp_BlitzIndex draws on the missing index DMVs, and shows you indexes with an estimated daily benefit >100k. This comes from uses impact average query cost / days uptime. If a missing index request doesn't show up here, it may not break that threshold.

Missing index requests in query plans draw from the index matching portion of the optimization process. They don't pull from DMVs at all, but rather will log to the DMVs that sp_BlitzIndex pulls from.

If you want to look more closely at just missing indexes, run sp_BlitzIndex in @Mode = 3 for that database. It will show you all of them.

Full disclosure: I am employed by Brent Ozar Unlimited, a limited liability corporation, and I contribute code to sp_BlitzIndex.

Thanks!

Context

StackExchange Database Administrators Q#164754, answer score: 3

Revisions (0)

No revisions yet.