CODEWITHSUNDEEP
sql-server
Adam Anderson
Adam Anderson

Reputation: 508

Why isn't the optimizer using my unique filtered index?

Sample schema:

create table dbo.Person (
    ID int identity(1,1) not null
        constraint PK_Person primary key,
    UserName nvarchar(50) null,
    EncryptedPassword nvarchar(100) null
)
create index IX_Person_Login
    on dbo.Person (UserName, EncryptedPassword)
    include (/* other columns */)
create unique index IX_Person_UserName
    on dbo.Person (UserName)
    where (UserName is not null)

So now, if I do a lookup of ID by UserName, I would expect that the smaller, more selective index would be chosen by the optimizer. IX_Person_UserName should also be covering because ID is the clustering key (and the resulting plans do bear this out, but that's not the point of the question).

select ID
from dbo.Person
where UserName = @UserName
and UserName is not null

Yet instead, the optimizer chooses to perform an INDEX SEEK on IX_Person_Login, which is not unique, has more columns in the key, and whose leaf nodes are much larger. If I force the use of IX_Person_UserName, the estimated costs are the same. In both cases the estimated row counts are over 100 but the actual row count is 1. I tried updating statistics but that didn't make any difference in the chosen plan or estimated row counts either. Is it because SQL Server's plan is accounting for the possibility that @UserName might be null? Even if I put a literal non-null string value in the query, it still does not use the unique filtered index. Can anyone explain this behavior?

Upvotes: 0

Views: 83

Answers (1)

Tom Page
Tom Page

Reputation: 1241

The Query Optimiser doesn't necessarily choose the optimal plan.

This makes sense as it's sometimes better to run with the good plan that you've got right now rather than waste a lot of time looking for a faster one.

If you want to force your query to use that index then you can by using a TABLE HINT

SELECT Id FROM dbo.Person p 
WHERE UserName is not null
OPTION  (TABLE HINT(p , INDEX (IX_Person_UserName)))

https://msdn.microsoft.com/en-us/library/ms181714.aspx

If you run both versions and Include the Actual Execution plan

SELECT Id FROM dbo.Person p 
WHERE UserName is not null
OPTION  (TABLE HINT(p , INDEX (IX_Person_UserName)))

SELECT Id FROM dbo.Person p 
WHERE UserName is not null
OPTION  (TABLE HINT(p , INDEX (IX_Person_Login)))

You'll be able to see from the query cost relative to the batch whether it actually makes any difference.

I expect you'll see 

Query1: Query cost (relative to the batch): 50%

Query2: Query cost (relative to the batch): 50%

Upvotes: 1

Related Questions