Local Variables Cause Performance Problems With TOP In SQL Server

Number One


In case you missed it for some reason, check out this post of mine about local variables. Though it’s hard to imagine how you missed it, since it’s the single most important blog post ever written, even outside of SQL Server. It might even be more important than SQL Server. Time will tell.

While live streaming recently about paging queries, I thought that it might make an interesting post to see what happens when you use variables in places other than the where clause.

After several seconds of thinking about it, I decided that TOP would be a good enough place to muck around.

Unvariables


Let’s say you’ve got these two queries.

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id;
GO 

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id
OPTION(RECOMPILE);
GO

Without a RECOMPILE hint, you get a 100 row estimate for the local variable in a TOP.

SQL Server Query Plan
???

Which of course might could lead to some problems if you were selecting way more than 100 rows via TOP.

Not Unexpectedly


You can manipulate what the optimizer thinks it’ll get with optimizer for hints:

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id
OPTION(OPTIMIZE FOR(@pagesize = 1));
GO
SQL Server Query Plan
the chump is here

And of course, when used as actual parameters, can be sniffed.

DECLARE @pagesize INT = 10000;
DECLARE @sql NVARCHAR(1000) = 
N'
  SELECT TOP (@pagesize) p.Id
  FROM dbo.Posts AS p
  ORDER BY p.Id;
'

EXEC sys.sp_executesql @sql, N'@pagesize INT', 1;
EXEC sys.sp_executesql @sql, N'@pagesize INT', 10000;
GO 
SQL Server Query Plan
boogers

Got More?


In tomorrow’s post, I’ll look at how local variables can be weird in ORDER BY. If you’ve got other ideas, feel free to leave them here.

There’s not much more to say about WHERE or JOIN, I’m looking for more creative applications ?

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



5 thoughts on “Local Variables Cause Performance Problems With TOP In SQL Server

  1. A little something for parameters. I know, not variables but could easily be mistaken for a variable.
    (Keeping fingers crossed that formatting will prevail).

    create procedure dbo.P
    @I int
    as
    set @I = 200000; -- Oh nooooo, modifying a parameter
    declare @T table(C bit null);
    select top(@I) C.number
    from master..spt_values as C
    left outer join @T on 1 = 0
    -- What value for @I was sniffed in SQL Server 2019?
    go
    exec dbo.P @I = 10;

  2. Yes, change to compat level to 140 and spot the difference in ParameterCompiledValue.

    The use of a table variable (that eventually gets removed) defers compilation in compat level 150 so the changed value of the parameter is sniffed instead of the value the parameter have when to proc is compiled.

Comments are closed.