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?
We'll look at a few of the reasons in more detail, and also talk about some of the general limitations of the feature.
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.
Missing index requests also don't cover 'special' indexes, like:
Missing Index key columns are generated from columns used to filter results, like those in:
Missing Index Included columns are generated from columns required by the query, like those in:
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
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.
Relax, bucko. We're getting there.
If you enable TF 2330, missing index requests won't be logged. To find out if you have this enabled, run this:
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.
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.
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.
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:
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:
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
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.