Seems SQL Server couldn't optimize optional parameters I am trying to optimize a query which has many parameters via Profiler. I tested below queries:

declare @location nvarchar(200) = null;
select count(*) from Places where @location is null or Location = @location 


cpu:1249 duration:64 reads:456760


declare @location nvarchar(200) = null;
select count(*) from Places


cpu:315 duration:20 reads:15001


As you can see, both queries are identical because the @location parameter is optional and it is null, but performance metrics are showing very different results. It seems SQL Server couldn't understand both queries are exactly the same. 

Why SQL Server can not optimize such a simple query?
both queries are identical because the @location parameter is optional and it is null


They are on this invocation, but the execution plan for the query will be cached and may be reused when the @location parameter has a different value. SQL Server has to build a plan that will produce correct results for all possible cases.

To resolve this, you could write the query using Dynamic SQL, or you could use an option (recompile) query hint. More details, including the trade-offs for each method, in the references below:


Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White
Dynamic Search Conditions - Erland Sommarskog
Catch-all queries - Gail Shaw