This question already has an answer here:
I have a lot of historical data, but 98% of the time, my data entry team only looks up data from the last 5 days. So I added a filtered index to help those lookups be faster.
My test query looks similar to this:
SELECT CustomerId, OrderId, Col2, Col3, Col4 -- about 30 columns FROM CustomerOrder WHERE CustomerId = 22000000 OPTION (RECOMPILE)
And I have this filtered index:
CREATE nonclustered index FIX_CustomerOrder_CustomerId ON CustomerOrder (CustomerId) INCLUDE (OrderId, Col2, Col3, Col4) -- about 30 columns WHERE CustomerId > 21000000 -- This equates to the last 5 days worth of data
When I run that query, the filtered index is chosen and used.
However, if I then add this index (for intended for the few older rows that will get looked up):
CREATE nonclustered index IX_CustomerOrder_CustomerId ON CustomerOrder (CustomerId) INCLUDE (OrderId, Col2, Col3, Col4) -- about 30 columns
It gets picked instead of the filtered index. (NOTE: Both indexes are exactly the same except that the second is not a filtered index.)
So I am confused. The Filtered Index removes 21 million rows from the equation. Why would the optimizer not use it?
If I add the option
with (Index (FIX_Customer_OrderId)) then the index is used, and the performance is still good (about the same). But I thought filtering out all those rows would be much faster...
More Additional Info:
If I extend the size of the filtered index to cover more days it eventually gets selected. The sweet spot seems to be 7 days. 6 uses the normal index, 7 uses the filtered index. Weird!
(NOTE: I have double checked to make sure that my row I am selected is in the original filtered index. The key I am searching on is 21317761. My 5 day filtered index value (that does not get used) is Id > 21289782. My 7 day filtered index value (that does get used) is Id > 21232773.)