I got a very interesting issue about sp_HumanEvents recently, and I couldn’t quite figure out why things were going wonky.
The problem was that at some point when monitoring blocking long-term, something was taking up a whole lot of tempdb.
I’m gonna say up front that the problem was using a recompile hint with LOB variable assignment in a loop. That doesn’t mean you should avoid any one of those things, but be careful when you use them in concert.
Rather than make you go through running and causing blocking, the issue is reproduced through this demo:
DECLARE @lob nvarchar(max) = N'x'; DECLARE @x xml = N'<x>' + REPLICATE(@lob, 1024 * 1024) + N'</x>'; DECLARE @loop integer = 0; DECLARE @t table (x nvarchar(1)); WHILE @loop < 5 BEGIN INSERT @t ( x ) SELECT x = @x.value('(./x/text())', 'nvarchar(1)') OPTION (RECOMPILE); DELETE @t; SELECT ddtsu.internal_objects_alloc_page_count, ddtsu.internal_objects_dealloc_page_count, internal_object_reserved_page_count = ( SELECT SUM(internal_object_reserved_page_count) FROM tempdb.sys.dm_db_file_space_usage ) FROM sys.dm_db_task_space_usage AS ddtsu WHERE ddtsu.session_id = @@SPID; SET @loop += 1; END;
The trick here is to run it with the recompile hint available, and then quoted out.
With recompile available, this is the result:
The internal objects keep going up, except the deallocated number. That’s the column in the middle.
With recompile quoted out, the numbers are a lot different.
All numbers stay stable.
As Mr. White points out in his post, assigning to a variable prevents parameter embedding.
- When it’s allowed, tempdb objects get cleaned up at the end of the statement.
- When it’s not, it gets cleaned up at the end of the batch.
This has been reported to Microsoft for some analysis. Hopefully there’s some remedy for it in the future.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.
- How #Temporary Tables Can Cause Plan Cache Pollution In SQL Server
- When Query Spills Cause tempdb Contention And Performance Issues In SQL Server
- When Should You Use Table Variables In SQL Server? When Queries Execute Thousands Of Times A Minute
- SQL Server Spool Operators Are Just Crappy Temp Tables