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

Query Store Indexes

Arthur Daniels shows what you can learn from the indexes on Query Store tables: It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. […]

Read More

Gotchas When Indexing Partitioned Tables

Andrew Pruski gives us a couple of considerations when creating indexes on partitioned tables in SQL Server: Looking at that data page, we can see that SQL has added a UNIQUIFIER column. Now this is standard SQL behaviour, SQL does this to all non-unique clustered indexes whether they are on a partitioned table or not. But also […]

Read More


August 2017
« Jul Sep »