Missing Index DMV Limitations

Brent Ozar goes into detail on why you should not blindly trust missing index recommendations in SQL Server:

SQL Server’s telling us that it needs an index to do an equality search on LastAccessDate – because our query says LastAccessDate = ‘2016/11/10’.

But in reality, that’s not how you access datetime fields because it won’t give you everyone who accessed the system on 2016/11/10 – it only gives you 2016/11/10 00:00:00. Instead, you need to see everyone on that day, like this:

Read the whole thing.  The crux of this is that the missing index recommendation process only gets to see what you’re running at the time you run it, so it can’t generalize all that well; that’s your job.

Related Posts

Smarter Indexes Based On Column Cardinality

Eric Blinn has a function which organizes columns in the missing index DMV by cardinality: Bryan Rebok and Brent Ozar recently opened my eyes to something I didn’t know.  When SQL Server recommends missing indexes to you it puts the columns in order in which they are found in the table.  That’s it.  I always thought […]

Read More

Don’t Forget Those Paused Indexes

Arun Sirpal tries to create a new index on his Azure SQL Database: I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code: CREATE NONCLUSTERED INDEX [dbo.NCI_Time] ON [dbo].[Audit] ([UserId]) INCLUDE ([DefID],[ShopID]) Msg 10637, Level 16, State 3, Line 7 Cannot […]

Read More

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031