locked
filtered index unmatched index RRS feed

  • Question

  • I have the following query.

    DECLARE @UserId INT=114,@DATE DATE='2018-06-04',@Amount MONEY 
    SELECT @Amount=  SUM(CH.Amount) 
    FROM COMMISSION_HISTORY CH 
    WHERE CH.UserId = @UserId 
    AND CH.Created_Date  BETWEEN @DATE AND GETDATE()

    and the filtered index as below.

    CREATE NONCLUSTERED INDEX FNcIdx_comm_history ON COMMISSION_HISTORY (UserId, Created_Date) 
    INCLUDE (Amount)
    WHERE Created_Date >= '2018-01-01'

    Tthis query does not use this filtered index and instead scans the clustered index because of parameterization.

    But after following BOLs I found option(recompile) uses the filtered index which is so in the demos put in BOLs but that didnt work in my case. It still scans clustered index. 





    • Edited by Curendra Thursday, June 4, 2020 8:22 AM
    Thursday, June 4, 2020 8:20 AM

Answers

All replies

  • Hi Curendra,

    You could explicitly include the filtered index predicate in your query predicate or rewrite the query to be a dynamic query, refer to:

    How to Overcome the SQL Server Filtered Index UnMatchedIndexes Issue.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, June 4, 2020 8:54 AM
  • I followed your link. That blog has solved issue by using option(recompile) but that didnt work for me. I want to know reason of this.
    Thursday, June 4, 2020 9:01 AM
  • I followed your link. That blog has solved issue by using option(recompile) but that didnt work for me. I want to know reason of this.

    I think the variable assignment is the culprit. If you need to assign the scalar result to a variable, try this workaround:

    DECLARE @UserId INT=114,@DATE DATE='2018-06-04',@Amount MONEY 
    DECLARE @result TABLE(Amount money);
    
    INSERT INTO @result
    SELECT SUM(CH.Amount) 
    FROM COMMISSION_HISTORY CH 
    WHERE CH.UserId = @UserId 
    AND CH.Created_Date  BETWEEN @DATE AND GETDATE()
    OPTION(RECOMPILE);
    
    SELECT @Amount = Amount FROM @Result;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Curendra Thursday, June 4, 2020 12:53 PM
    Thursday, June 4, 2020 12:43 PM
  • See https://www.sqlservercentral.com/articles/strange-filtered-index-problem

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Curendra Thursday, June 4, 2020 12:59 PM
    Thursday, June 4, 2020 12:44 PM
    Answerer