February 27, 2018 at 1:16 pm
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
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
February 27, 2018 at 2:31 pm
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
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply