Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?

Question

I have a SQL Server database where the queries are pretty slow, and there's a lot of locking and blocking.

When I look at the missing index DMVs and query plans, there aren't any suggestions.

Why is that?

Answer 1

There are many reasons why you may not have missing index requests!

We'll look at a few of the reasons in more detail, and also talk about some of the general limitations of the feature.

General Limitations

First, from: Limitations of the Missing Indexes Feature:

• It does not specify an order for columns to be used in an index.

As noted in this Q&A: How does SQL Server determine key column order in missing index requests?, the order of columns in the index definition is dictated by Equality vs Inequality predicate, and then column ordinal position in the table.

There are no guesses at selectivity, and there may be a better order available. It's your job to figure that out.

Special Indexes

Missing index requests also don't cover 'special' indexes, like:

• Clustered
• Filtered
• Partitioned
• Compressed
• XML-ed
• Spatial-ed
• Columnstore-d
• Indexed View-ed

What columns are considered?

Missing Index key columns are generated from columns used to filter results, like those in:

• JOINs
• WHERE clause

Missing Index Included columns are generated from columns required by the query, like those in:

• SELECT
• GROUP BY
• ORDER BY

Even though quite often, columns you're ordering by or grouping by can be beneficial as key columns. This goes back to one of the Limitations:

• It is not intended to fine tune an indexing configuration.

For example, this query will not register a missing index request, even though adding an index on LastAccessDate would prevent the need to Sort (and spill to disk).

SELECT TOP (1000) u.DisplayName
FROM dbo.Users AS u
ORDER BY u.LastAccessDate DESC;

Nor does this grouping query on Location.

SELECT TOP (20000) u.Location
FROM dbo.Users AS u
GROUP BY u.Location

That doesn't sound very helpful!

Well, yeah, but it's better than nothing. Think of missing index requests like a crying baby. You know there's a problem, but it's up to you as an adult to figure out what that problem is.

You still haven't told me why I don't have them, though...

Relax, bucko. We're getting there.

Trace Flags

If you enable TF 2330, missing index requests won't be logged. To find out if you have this enabled, run this:

DBCC TRACESTATUS;

Index Rebuilds

Rebuilding indexes will clear missing index requests. So before you go Hi-Ho-Silver-Away rebuilding every index the second an iota of fragmentation sneaks in, think about the information you're clearing out every time you do that.

You may also want to think about Why Defragmenting Your Indexes Isn’t Helping, anyway. Unless you're using Columnstore.

Adding, Removing, or Disabling Indexes

Adding, removing, or disabling an index will clear all of the missing index requests for that table. If you're working through several index changes on the same table, make sure you script them all out before making any.

Trivial Plans

If a plan is simple enough, and the index access choice is obvious enough, and the cost is low enough, you'll get a trivial plan.

This effectively means there were no cost based decisions for the optimizer to make.

Via Paul White:

The details of which types of query can benefit from Trivial Plan change frequently, but things like joins, subqueries, and inequality predicates generally prevent this optimization.

When a plan is trivial, additional optimization phases are not explored, and missing indexes are not requested.

See the difference between these queries and their plans:

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2;

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2
AND 1 = (SELECT 1);

The first plan is trivial, and no request is shown. There may be cases where bugs prevent missing indexes from appearing in query plans; they are usually more reliably logged in the missing index DMVs, though.

SARGability

Predicates where the optimizer wouldn't be able to use an index efficiently even with an index may prevent them from being logged.

Things that are generally not SARGable are:

• Columns wrapped in functions
• Column + SomeValue = SomePredicate
• Column + AnotherColumn = SomePredicate
• Column = @Variable OR @Variable IS NULL

Examples:

SELECT *
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 1000) > 1000;


SELECT *
FROM dbo.Users AS u
WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 5000


SELECT *
FROM dbo.Users AS u
WHERE u.UpVotes + u.DownVotes > 10000000


DECLARE @ThisWillHappenWithStoredProcedureParametersToo NVARCHAR(40) = N'Eggs McLaren'
SELECT *
FROM dbo.Users AS u
WHERE u.DisplayName LIKE @ThisWillHappenWithStoredProcedureParametersToo 
      OR @ThisWillHappenWithStoredProcedureParametersToo IS NULL;

None of these queries will register missing index requests. For more information on these, check out the following links:

• Optional Parameters and Missing Index Requests
• SARGable WHERE clause for two date columns
• What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?

You Already Have An Okay Index

Take this index:

CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);

It looks okay for this query:

SELECT p.OwnerUserId, p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20070101'
AND   p.CreationDate < '20181231'
AND   p.Score >= 25000
AND 1 = (SELECT 1)
ORDER BY p.Score DESC;

The plan is a simple Seek...

But because the leading key column is for the less-selective predicate, we end up doing more work than we should:

Table 'Posts'. Scan count 13, logical reads 136890

If we change the index key column order, we do a lot less work:

CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);

And significantly fewer reads:

Table 'Posts'. Scan count 1, logical reads 5

SQL Server Is Creating Indexes For you

In certain cases, SQL Server will choose to create an index on the fly via an index spool. When an index spool is present, a missing index request won't be. Surely adding the index yourself could be a good idea, but don't count on SQL Server helping you figure that out.