Seems SQL Server couldn't optimize optional parameters
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