query plan shows key lookup with 99% when search condition value is null

Question

I have a SP the sp execute on a table with an index in three columns col_1,col_2,col_3,col_4,col_n. Col 1 is an identity. there is an index on col_2,col_3,col_4 in that order.

When I query something like

@parameter=1

SELECT col_2,col_3,col_4
WHERE col_2=@parameter
AND col_3='2'
AND col_4=10.00

It uses the index I mention before but if I query something like

@paramete =null; -- the parameter is null in this case when parameter
has value it uses the index

SELECT col_2,col_3,col_4
WHERE (@parameter is null OR col_2 = @parameter)
AND col_3='2'
AND col_4=10.00

It shows a query plan with a key look up on col_1 with a 99% cost.

Sometimes the value is null and I must remain like that.

Can someone explain this? can it be fixed?

Thanks.

This exactly how this type of query will always work. It is commonly known as a "catch all" query. Here is an excellent article discussing this type of query and how you can fix it. simple-talk.com/sql/t-sql-programming/… — Sean Lange

Vladimir Baranov Vladimir Baranov · Accepted Answer · 2016-10-28T11:20:57

First of all, add OPTION(RECOMPILE) to the query:

SELECT col_2,col_3,col_4
WHERE 
    (@parameter is null OR col_2 = @parameter)
    AND col_3='2'
    AND col_4=10.00
OPTION(RECOMPILE);

See excellent article Dynamic Search Conditions in T‑SQL by Erland Sommarskog for details.

If @parameter is not NULL, with OPTION(RECOMPILE) the query effectively becomes this:

SELECT col_2,col_3,col_4
WHERE 
    col_2 = <actual_parameter_value>
    AND col_3='2'
    AND col_4=10.00

An index on col_2,col_3,col_4 can be used to quickly find necessary rows in this query.

If @parameter is NULL, with OPTION(RECOMPILE) the query effectively becomes this:

SELECT col_2,col_3,col_4
WHERE 
    col_3='2'
    AND col_4=10.00

Now it is obvious that index on col_2,col_3,col_4 is of no help here. You need a second index on col_3,col_4 include (col_2) to make it efficient.

On the other hand, a single index with different order of columns would help in both cases. So, instead of index on col_2,col_3,col_4 create and index on col_4,col_3,col_2 or col_3,col_4,col_2. Put col_2 last in the index definition. It will be used efficiently in both cases (with OPTION(RECOMPILE)).

query plan shows key lookup with 99% when search condition value is null

1 Answers