How can I change the where clause of this sp so it can do the index seek?

PK 2017-02-17 21:34:34

There is an index scan on the table with the warning 'operational cause residual io'.
How can I improve the performance of this sp?

Thank you,

Aaron Bertrand 2017-02-17 23:04:51
Seems like a kitchen sink procedure, where all those AND and ORs make it hard for SQL Server to come up with proper estimates. I would suggest changing the procedure to use dynamic SQL, as I describe here and here, so that there is a distinct plan optimized for each set of parameters.
PK 2017-03-27 20:49:59
Hi Aaron,

It has been a while to fix this procedure. It was a parameter sniffing issue and once we changed to use dynamic SQL as you suggested the performance has been much improving. Thanks for your help. Much appreciated.