Why does a parameterized query produces vastly slower query plan vs non-parameterized query

It appears that the query planner has made a decision in the literal query which is based upon information that it already has. It would have statistics which it can query efficiently based on the spread of data given in your specific literal.

The parameterized query has chosen the query that it believes is fairest for all the data in your table, which you'll notice is many nested loops (performance = bad).

Perhaps you might try and run the database optimization tools on your database to see if some indexes could help you here?

Specifically in your query, try this:

declare @p0 int
set @p0 = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @p0
OPTION ( OPTIMIZE FOR (@p0 = 1000))

But I would be wary of doing this without being certain that the data contained in this query won't change and that your query on this plan will ALWAYS be more efficient.

I think you're running afoul of "parameter sniffing". Basically, what this means is that SQL Server tries to use as much information as it has to calculate an optimal execution plan for your query. In the case of your first query, you have a constant value that is known at compile time, so the engine can directly optimize the query plan for that value.

In the second one, the fact that you are using a variable masks that value from the engine at compile time (you'd think it should be able to figure it out, but I've actually had similar issues with a simple constant expression!), leading to poor performance.

One way you can try to get around this would be to wrap the query in a stored procedure that takes the parameter directly and then applies it to the query -- something like this:

CREATE PROCEDURE test
  @p0 int
AS
BEGIN
  select *
  from foo
  join bar on bar.x = foo.x
  join baz on baz.y = foo.y
  where foo.x = @p0
END

This should allow the optimizer to accurately "sniff" the parameter you use and generate an optimal query plan for you.

In my case in DB table column type was defined as VarChar and in parameterized query parameter type was defined as NVarChar, this introduced CONVERT_IMPLICIT in the actual execution plan to match data type before comparing and that was culprit for sow performance, 2 sec vs 11 sec. Just correcting parameter type made parameterized query as fast as non parameterized version.

Hope this may help someone with similar issue.