locked
Why isn't my Filtered Index working? RRS feed

  • Question

  • Greetings. Using SS 2012. 

    I've been doing some testing in a large data warehouse environment. 

    The first sad news I realized (and confirmed in Brent Ozar's article here "Use date functions such as DATEADD for a rolling date range") was that Filtered Indexes won't work with any sort of date functions. This is a bummer, as it was my main goal for these guys. 

    Moving on, I found this article which gave me the idea to simply drop/ recreate the index on the first day of each month, as a lot of our queries deal only with data for this calendar month.

    Now in my test environment I don’t have a complete set of data. That said I wanted to improvise my index just to verify it would work as expected, so I created this index:

    create index myFilteredIndex
    on myTable(RecLoadDt)
    where RecLoadDt > '04/27/2015'

    I then ran this query:

      select 
      *
      from myTable
      where RecLoadDt > '04/28/2015'


    However, my query is still using the index that’s always existed:

    CREATE CLUSTERED INDEX [IX_myIndex] ON [dbo].[myTable]
    (
    	[RecLoadDt] ASC

    Effectively, I discovered my new index is no good whatsoever. Then I discovered this snippet from Brent's article: " the value in WHERE clause must be exact." Is this for real? Do I truly need to specify the exact date in my FI? 

    That, plus seeing that my new index simply isn't being used, leads me to believe that FI's really aren't much good for date values -- I mean how often do we know the exact date in these scenarios?

    Can someone please confirm/ deny this for me? Is there any way to make use of FI's on date columns?

    Thanks!



    Thanks in advance! ChrisRDBA


    • Edited by ChrisRDBA Friday, January 6, 2017 10:55 PM
    Friday, January 6, 2017 10:53 PM

Answers

  • Hi Chris, 

    This problem is not the filtered index issue. It's about the index itself. Query Optimizer (QO) never uses such index for your query. When designing indexes, columns in select clause are also important. When you use select * it means all columns in your table. First, we need to know how QO will decide to use your index, then we can add a filter on it. In this specific case, for example, if you create the following index, it will use it. (assuming that your table has for example four columns which are Col1, Col2, Col3, ReLoadDt).

    create nonclustered index myFilteredIndex
    on myTable(RecLoadDt)
    include ( Col1, Col2, Col3 )
    where RecLoadDt > '04/27/2015'

    • Proposed as answer by philfactor Saturday, January 7, 2017 2:39 PM
    • Marked as answer by Olaf HelperMVP Saturday, February 18, 2017 11:34 AM
    Saturday, January 7, 2017 7:52 AM

All replies

  • Hi Chris, 

    This problem is not the filtered index issue. It's about the index itself. Query Optimizer (QO) never uses such index for your query. When designing indexes, columns in select clause are also important. When you use select * it means all columns in your table. First, we need to know how QO will decide to use your index, then we can add a filter on it. In this specific case, for example, if you create the following index, it will use it. (assuming that your table has for example four columns which are Col1, Col2, Col3, ReLoadDt).

    create nonclustered index myFilteredIndex
    on myTable(RecLoadDt)
    include ( Col1, Col2, Col3 )
    where RecLoadDt > '04/27/2015'

    • Proposed as answer by philfactor Saturday, January 7, 2017 2:39 PM
    • Marked as answer by Olaf HelperMVP Saturday, February 18, 2017 11:34 AM
    Saturday, January 7, 2017 7:52 AM
  • Hi Chris, 

    This problem is not the filtered index issue. It's about the index itself. Query Optimizer (QO) never uses such index for your query. When designing indexes, columns in select clause are also important. When you use select * it means all columns in your table. First, we need to know how QO will decide to use your index, then we can add a filter on it. In this specific case, for example, if you create the following index, it will use it. (assuming that your table has for example four columns which are Col1, Col2, Col3, ReLoadDt).

    create nonclustered index myFilteredIndex
    on myTable(RecLoadDt)
    include ( Col1, Col2, Col3 )
    where RecLoadDt > '04/27/2015'

    I just did a test and see that you're correct, but I'm not understanding why this would be the case -- here is the index that's always existed:

    CREATE CLUSTERED INDEX [IX_myIndex] ON [dbo].[myTable]
    (
    	[RecLoadDt] ASC

    If I do a "SELECT *...", I get a Clustered Index Seek. However, to use my shiny new filtered index, I have to use the Include clause in the index creation, AND specify only those columns in the select list, like this?

      create index myFilteredIndex
    on myTable(RecLoadDt)
    include (startDate, endDate, [subject], dbName)
    where RecLoadDt > '04/27/2015'
    
    select 
      startDate, endDate, [subject], dbName
      from myTable
      where RecLoadDt > '04/28/2015'

    Why does a Filtered Index require all of this, but a regular index does not?

    Thanks!


    Thanks in advance! ChrisRDBA

    Monday, January 9, 2017 2:41 PM
  • The selectivity of your data and the age of your statistics will determine whether the optimizer will choose to use one index over another.

    Your select * from MyTable where RecLoadDt involves returning more columns than the more selective

    select startDate, endDate, [subject], dbName from MyTable where RecLoadDt>'04/28/2015'  - where your filtered index is a covering index. If it was to use the filtered covering index in your select * query it would have to do a bookmark lookup to retrieve all the columns and that is more expensive than the clustered scan.

    Monday, January 9, 2017 3:05 PM