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
WHERE StatusCd >= 2
? An index is only useful if it can effectively eliminate enough rows that using it is better than a full scan. In many cases, the scan is actually more efficient, especially if - like Kirk says - the other predicates are more selective than the leading key in the index (another factor isSELECT *
means a key lookup would be required to obtain the other columns - so if you don't really needSELECT *
, trying again with the real set of columns you need may yield to a more useful and more likely-to-be-used index suggestion).StatusCd
if it is not going use it for the actual execution. I think in general it is just because those recommendations are not always the greatest. Brent Ozar shares his opinions on the recommended indexes from SQL Server here: brentozar.com/archive/2019/11/…. (someone else very smart, like @AaronBertrand might know more). For this particular query, the most likely reason it is not utilizing the index onStatusCd
is because of the selectivity those 3 columns.