Sql-server – Does parameter sniffing exist for views

parameter-sniffingperformancequery-performancesql serversql-server-2016

I've heard of and encountered parameter sniffing in stored procedures before. But today I ran into the first instance of what I can only assume is parameter sniffing in a view.

I have a query that selects from a number of tables and a view. It's basically:

select *
from view v
inner join table t
on v.id = t.id
where v.date = @EndDate;

With the parameter in place, the query will attempt to execute literally for hours. I've let it sit for over 2 hours and nothing gets returned. If I replace the parameter with a specific date where v.date = '2017-11-20' or even a calculated value where v.date = cast(getdate() as date) it will return in 2 seconds. I can keep the parameter in by adding option (recompile) to the end of the query and it will return results in a second or two.

So I know how to fix this, I am really just asking: Is this something that others have run into before? Is it possible it's not parameter sniffing but rather something else?

Thanks.

Best Answer

The performance issue that you're seeing is not caused by parameter sniffing. The cause is that your query does not qualify for the parameter embedding optimization without a RECOMPILE hint. There's a lot of confusion about this online, but one could say that it's the exact opposite of parameter sniffing. The query optimizer doesn't know anything about the local variable so your query plan won't change based on the value of the local variable.

If you're still not convinced, try flushing the plan cache or restarting the server in a development environment. You'll still experience the same performance issue even though there aren't any cached plans available.

It is possible to experience parameter sniffing with a view if you execute the query using sp_executesql. There is an example of that in the earlier linked article.

Related Question