Sql-server – Why does SQL Server not use the Index it recommends

index-tuningoptimizationsql-server-2012

The query

I had a specific query I wanted to optimize:

SELECT * /*12 columns*/
FROM [dbo].[EnterpriseGroup]
WHERE 
    (EnterpriseGroup.ChildId = 123 OR EnterpriseGroup.FatherId = 234)
AND StatusCd >= 2

There is also already an index on FatherId, but not on the ChildId. The primary key is among the 12 selected columns, but none of those used in the WHERE clause here.

The use

This is a simple query but it's run very, very often during daily work. The table is also small, around 8000 rows.

The query is used to find groups of enterprises. There are about 2 Millions of enterprise entries, so only less than 0.5% have a matching group row and thus most of the time, no group will be found.

The recommendation

When using SSMS and inspecting the "Actual Execution Plan" it gives this plan:

The predicate shown is actually the WHERE clause.

And, it recommends to create an index, which is basically indexing on the WHERE clause and adding all the queried columns directly into the index. Seems not very clever to me but maybe this is what this questions is all about:

/*
Missing Index Details from SQLQuery6.sql .....
The Query Processor estimates that implementing the following index could improve the query cost by 68.9052%.
*/

/*
USE [...]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[EnterpriseGroup] ([StatusCd])
INCLUDE (....all the 12 queried columns......)
GO
*/

The result

After I create the recommended index I get the following plan:

It's not used at all! (And the Table Scan hover info is exactly the same, still having a copy of the WHERE Clause as predicate)

The Question

Why does SQL Server not use an existing index that the SSMS adviced to create?

If not, then what is the significance of a missing index recommendation in the execution plan of SSMS (SQL Server Management Studio) against a Microsoft SQL server?

The notes

Note: I am no DBA, but a software developer. I have read a bit into this, including: https://www.brentozar.com/archive/2013/07/dude-who-stole-my-missing-index-recommendation/ but I did not clarify to me.

Note: In case it matters:
– SQL Server Version 11.0.7493.4, running on Windows NT 6.3.
– Microsoft SQL Server Management Studio is Version 11.0.7493.4

Best Answer

Assuming you WHERE condition still utilizes ChildId, FatherId and StatusCd. It is possible that from a statistics perspective, that either ChildId or FatherId is more selective. Taking ChildId and FatherId out of the WHERE clause should result in that new index being used, since StatusCd is the indexed column in the index.

If you hover over the Table Scan section you should see something like the below image:

It is possible that even though it recommended creating that index, that it is still querying by either ChildId or FatherId.

It would do this if ChildId or FatherId is more selective. Lets say StatusCd >= 2 returns 6,000 of the 8,000 rows. But either ChildId = 123 or FatherId = 234 only has 1 row. Then doing a table scan on that one column, and applying the rest of the conditions after the fact is a more efficient query plan (theoretically speaking) than returning all 6,000 rows from StatusCd >= 2 and trying to apply the ChildId or FatherId conditions after.

This was something I learned from a question I asked a little bit ago. The guy who answered it had a really great way of explaining what I tried to say here. Does the Query Optimizer Prefer to Query on Constants before Columns?

Hopefully that helps.

Related Solutions

Sql-server – SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

_{* Disclaimer: I work for SQL Sentry.}

Sql-server – Should these two indexes suggested by the SSMS missing index feature be combined

Well, you could consider a filtered index - if you're always looking for rows where IsSynchronized = 0 and this number should be relatively small, then instead of those two indexes, consider this instead:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized] 
  ON [dbo].[PackageEvents] ([PackageID])  
  INCLUDE ([EventDate], [EventDescription], [EventID], 
    [LastSyncDate], [Notes], [UserName], [Version]) 
  WHERE IsSynchronized = 0;

Of course you may want to make that even smaller and test to see the difference in impact if the query has to look up the data (should be pretty efficient if the number of rows is small), so - assuming PackageID is the clustering key:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized] 
  ON [dbo].[PackageEvents] ([PackageID])
  WHERE IsSynchronized = 0;

The overhead of maintaining this index may very well be worth the space savings compared to a full index, especially if it's only being used to optimize this query (or query pattern, at least).

Filtered indexes are not magic, though; JNK brought up some limitations below:

Caveats with filtered indexes - stats may not stay up to date without maintenance, and you need to use "standard" values for some settings like QUOTED IDENTIFIER and ANSI NULLS. These are small issues but if you have the settings wrong in a session that inserts into the index, the insert will fail.

Also you'll want to read these posts:

If you don't want to use a filtered index, you can probably test variations of these:

CREATE NONCLUSTERED INDEX [IX_Covering_try1] ON [dbo].[PackageEvents] 
  ([PackageID], IsSynchronized)  
INCLUDE ([EventDate], [EventDescription], [EventID], 
  [LastSyncDate], [Notes], [UserName], [Version]);

CREATE NONCLUSTERED INDEX [IX_Covering_try2] ON [dbo].[PackageEvents] 
  (IsSynchronized, [PackageID])  
INCLUDE ([EventDate], [EventDescription], [EventID], 
  [LastSyncDate], [Notes], [UserName], [Version]);

(For a long time I thought that including BIT columns in the key was wasteful but Martin Smith demonstrated a case where it worked quite well - worth a try. I can't find the post now.)

Without your full schema, data, query patterns etc. we can only guide you and have you test our suggestions in your environment. We can't say, "Ding! This is the one that will work for you!"