Post reply

Why is sql ignoring my covered index?

  • February 27, 2018 at 1:16 pm

    #104533

    Sql Server 2014.  Simple table, as seen below...about 400 million rows

    table tbl_member
       first_name VARCHAR(50)
       last_name VARCHAR(50)
       dob DATE
       fk_client INT
       pk_member INT

    Non-clustered index on (last_name, first_name, dob, fk_client) INCLUDE (pk_member)

    SELECT pk_member FROM tbl_member WHERE first_name = 'pat' AND last_name = 'xyz' AND dob = '11/1/82' AND fk_client = 5

    Why would sql choose to ignore my index and instead scan the table on the clustered index on pk_member?
    Also, the execution plan recommends this partial index instead: Index on (fk_client,dob) INCLUDE ([first_name],[last_name],[pk_member])

    Thanks in advance.

  • February 27, 2018 at 1:26 pm

    #1981361

    Can you post the CREATE TABLE, CREATE INDEX statements and the actual plan please?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 27, 2018 at 2:31 pm

    #1981371

    I began to post the table and index here...but found the culprit.  The query was ran from a stored procedure.  Someone decided to make the proc parameters NVARCHAR while the data is actually VARCHAR and the plan was doing an IMPLICIT_CONVERSION

  • February 27, 2018 at 2:35 pm

    #1981373

    That'll do the job nicely.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply