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

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?

Additional Info:
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.)

  • If the orderId is unique, does it help if you make the index unique? Just thinking that this might related to statistics / row estimations (especially the 6/7 day thing).
    – James Z
    Commented Feb 3, 2015 at 19:25
  • It might be better also to use the bigger index than doing recompile every time, if this is just one row lookup.
    – James Z
    Commented Feb 3, 2015 at 19:26
  • @JamesZ - I updated my example to show better the relationship. I am selecting a list of orders that each have a CustomerId on them. The CustomerId can be the same for many orders.
    – Vaccano
    Commented Feb 3, 2015 at 20:32
  • I would expect that performance is the same between using the indexes. The key is used to go through the binary tree of the index to find the range of rows it needs so the only difference between the two is the depth of the binary tree. It does not look like the optimizer considers the depth of the tree when calculating the cost for a index seek so the cost for using the two indexes is the same and what index is used is just a matter of coincidence. It used the filtered index in my tests if I created the filtered index after the non filtered index. Pure coincidence and not to be trusted. Commented Feb 4, 2015 at 8:26