Local Variable Gotcha
Published: Oct 24, 2014
We all use local variables and in many cases they’re invaluable… however, there is a lesser known (in my experience anyway) gotcha with them which can badly affect your queries and leave you wondering why performance has unexpectedly dropped.

Sadly there’s no “just use this” style of cure for this problem, but there are things that you can do to alleviate the impact this issue can have on your code.

Basically what happens are local variables throwing a veil over the optimiser and stopping it from being able to obtain the statistics it needs in order to produce an optimal plan.

Therefore a seemingly harmless statement can actually be a complete horror show when it executes, leaving you baffled as to the cause of the problem.

To easily show this, let’s take a simple example using the AdventureWorks database:

select *
from sales.SalesOrderDetail
where salesOrderDetailID >= 60000
go

declare @orderDetailID int = 60000

select *
from sales.SalesOrderDetail
where salesOrderDetailID >= @orderDetailID
go


As you can see, these are logically equivalent and as expected they produce the same execution plan. No problems here:

So where’s the issue? Well, it’s there, you’ve just not seen it yet. Hover over the final arrow of the first plan and you’ll see a tooltip similar to the following:

Now do the same to the second plan and you’ll see something like this:

To those eagle-eyed readers you’ll see the problem… the estimated row counts are completely off for the second plan. Why's that?

Basically the optimiser doesn’t know what the value being passed in actually is and therefore has to rely on a “best guess”, which is the equivalent of “OPTIMIZE FOR UNKNOWN”. Not ideal.

Why would this be a problem? Although this could cause issues by not choosing an appropriate index due to the “guess” this is easily spotted within execution plans, therefore this is not my main concern.

Let’s run that same query again but this time we’ll sort our data:

select *
from sales.SalesOrderDetail
where salesOrderDetailID >= 60000
order by SalesOrderDetailID
go

declare @orderDetailID int = 60000

select *
from sales.SalesOrderDetail
where salesOrderDetailID >= @orderDetailID
order by SalesOrderDetailID
go


Again, logically the same query… so let’s see how the execution plan looks if you’re still running SQL 2008 or below:

Well… strange… this one says that the second one (our local variable version) is actually faster. It takes only 40% of the total cost.

So… we should all be using local variables, yes? This whole article was a fraud?

Actually no. Let’s run Profiler whilst executing the same script…

The second query was actually the most expensive!!! How is that possible? If you’re running SQL 2012 then you’ll be given a VERY large clue by SSMS…

As you can see… tempDB was used in the second query because the sort spilled over. Therefore, with SQL having to use disks to sort the query, this was bound to actually be slower. This was only slight in my example on my test machine, but in production this can make a HUGE difference to performance.

So why has it happened? Well this is because of the “guess” SQL Server has had to make with regard to estimated rows.

SORT is a blocking operator which requires a memory grant in which to complete. Therefore in our example the first query uses statistics to estimate it will have to sort 60,000 records. Therefore it provides a memory grant sized with that in mind:

Compare this to the memory grant in the second query:

As we know (or if you don’t, then you’re about to), a memory grant is fixed at execution time and cannot then be expanded during execution. Therefore the second query suddenly finds that the size of returned records exceeds the size of the memory grant and it is forced to spill the entire sort to disk (not just a section, the entire sort).

If you trace this in Profiler you can clearly see this happening in the second query:

Now… after all that, how do we get around this? Sadly the answer isn’t perfect by any means. Basically all we can do is to use “OPTION (RECOMPILE)” on the statement using the variable. Doing this means that the optimiser will evaluate the variable first and then look to the statement to optimise it rather than having to optimise the statement at compile time before obtaining the variable’s value.

This means that we suddenly get an accurate row estimate (which fixes all our issues)…

Now the reason that I say this isn’t a solution to ALL scenarios is because this is fine if you don’t mind the overhead of a recompile, but if you have a query which joins numerous large tables and is called many times a minute, for example, then forcing a recompile could hurt your system.

In this situation the other options are to use OPTIMIZE FOR and specify a number in order to force the plan you may want to see. Again, fluffy, but may be better than the UNKNOWN default.

Another choice would be to bypass the variable. For example, if you create your variable using the following method:

declare @orderDetailID int =
(
  
select min(salesOrderDetailID)
  
from sales.SalesOrderDetail
  
where ModifiedDate >= '2007-10-29'
)

select *
from sales.SalesOrderDetail
where salesOrderDetailID >= @orderDetailID
order by SalesOrderDetailID
go


Then don’t use the variable at all and simply substitute back into your code accordingly:

select *
from sales.SalesOrderDetail
where salesOrderDetailID >=
(
  
select min(salesOrderDetailID)
  
from sales.SalesOrderDetail
  
where ModifiedDate >= '2007-10-29'
)
order by SalesOrderDetailID
go


Basically it comes down to testing on your system… I just wanted you to be aware of the issue and arm you with a few methods to test and see which works best for you.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron