Local variables don’t behave like true parameters in stored procedures in SQL Server. Join Kendra to explore why local variables are so tricky in this free 10 minute video.
Local variables don’t behave like true parameters in stored procedures in SQL Server. Join Kendra to explore why local variables are so tricky in this free 10 minute video.
22 Comments. Leave new
Is this a matter of local variables being treated differently, or is it really the optimization of the stored procedure that causes different behavior? Parameter Sniffing is a well known issue which causes performance differences based on the initial parameter used when a stored procedure is compiled versus the value used on subsequent executions.
In my limited testing. when the value used in the initial stored procedure execution/compilation is the same as the value used with a local variable, then the execution plans are the same. When this becomes a major issue then I add the WITH RECOMPILE option to the stored procedure to limit the differences found with parameter sniffing.
Your thoughts?
It’s specific to local variables that aren’t real parameters. For example, in the code below a local variable is declared inside a stored procedure. That local variable will be “anonymized” / not sniffed. This isn’t a common usage, but it does happen (it’s the old homegrown version for the “optimize for unknown” query hint before it existed).
create procedure dbo.foo
AS
declare @localvar int
set @localvar = 1
select *
from dbo.posts
where posttypeid=@localvar
GO
Thanks for the clarification.
Next time I need to watch the video AFTER having the first cup of coffee of the day.
Not sure if this article was your inspiration but I’d read it a little while back and it helped greatly with your (excellent as usual) content. I’d wondered why someone would put a default value in the procedure to start with. In any ase, it can’t hurt to share it.
http://michaeljswart.com/2015/02/when-parameter-sniffing-caused-deadlocks/
Don’t listen to that chump Michael Swart. He doesn’t know what he’s talking about.
Just kidding, Kevin, you’re check’s in the mail
That’s a great article!
…*your
This was very interesting, something I honestly had no idea occurs. So this same phenomenon occurs when using sp_executesql with parameters as well (they are treated as local variables)?
If you use parameterized dynamic SQL with sp_executesql, the parameters act like they do in a stored procedure.
Wow the Temp Stored procedure is a great idea! Usually I would just recreate the proc with a different name. What versions of SQL Server is this feature available on?
Really informative stuff as usual 🙂
Also who painted those bird silhouettes on the wall and door? Completely unrelated, but they had my attention for most of the video 🙂
It’s been around for a long time– even mentioned in the sql 2000 documentation: https://technet.microsoft.com/en-us/library/aa258259%28v=sql.80%29.aspx
And thanks! The birds are stickers from Blik: http://www.whatisblik.com/
Well I learned two important things today. I’m going to use that feature soon and those stickers are awesome. Thanks!
Thanks! I didn’t know you could do temporary procedures like that.
Very helpful. I wonder why this is? I can’t think of advantages of not treating variables like parameters. Any ideas why variables are not “sniffed into” like parameters?
Sort of makes debugging a long sproc painful, because trying to troubleshoot the middle of a long sproc by hardcoding some local variables into it means you’re not getting the same execution plan as the parameterized version.
But now I know, I guess!
When you build a query optimizer, there’s a lot of hard choices to make. I don’t actually think the behavior is bad, personally, just a bit confusing when you’re first working with it.
Hi – Liked your post – perhaps you should mention that local variables will not use histograms
(from Microsoft SQLServer 2014 – Query Tuning and Optimization – Benjamin Nevarez)
Yep, exactly Leon! What I show in the video is that it uses the “density vector” instead. Another way to say that is “it doesn’t use the histogram” (which is below the density vector in that part of the video). Great point, though– it’s definitely useful to say it both ways for people who already know about the histogram.
The video is no longer working. Is this just me?
Not working, but I found the video on YouTube. I actually really needed to watch this today!!
https://www.youtube.com/watch?v=DNUArv_DXn4
[…] Local Variables vs. Parameterized Stored Procedures in SQL Server […]
Is there any advantage to reassigning a parameter value to a local variable and using that local variable in the query? Is there a performance advantage?
George – go ahead and watch the video.