Ever have a WHERE statement that looks like this:

...
WHERE 
(T.ColumnName IS NULL OR T.ColumnName LIKE @ColumnName)
AND (T2.ColumnName IS NULL OR T2.ColumnName LIKE @ColumnName)
AND (T3.ColumnName IS NULL OR T3.ColumnName LIKE @ColumnName)
AND (T4.ColumnName IS NULL OR T4.ColumnName LIKE @ColumnName)

Might as well just rip all the indexes off the tables it’s accessing.  They’re getting invalidated.  Twice.  Apparently the developer really did not want to use indexes and wanted to be sure of it.

So, first things first.  Invite the developer that wrote said code to a friendly match of The Most Dangerous Game.  After that problem has been taken care of, you can replace the functionality with a CTE and some CASE statements.  Observe.

;WITH CTE_Table AS CTE(
	SELECT 
		Column1,
		Column2,
		Column3
	FROM dbo.Table
)
SELECT 
	Column1,
	Column2,
	Column3
FROM CTE_Table
WHERE
Column1 = CASE WHEN @Var1 IS NULL THEN Column1 ELSE @Var1 END
AND Column2 = CASE WHEN @Var2 IS NULL THEN Column2 ELSE @Var2 END
AND Column2 = CASE WHEN @Var3 IS NULL THEN Column3 ELSE @Var3 END

In my case, the developer was just throwing LIKE statements in, even though the variables being passed were from drop-downs, so exact matches were guaranteed.  You could replace the Column1 = with Column1 LIKE …etc…, but be sure to append a ‘%’ at the end of the variable.  If you say LIKE %@Var1% then you’ll invalidate your indexes all over again.  The LIKE needs to start looking somewhere in the index to be effective, and the starting % will cause it to scan everything.

A word of caution though, this will throw your statistics off drastically.  In my particular case, the estimated rows are 6, whereas the actual rows are 81,888.  Logical reads are out of this world.