LOB Data, Recompile, And Too Much tempdb Usage In SQL Server

Stumper

---

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.

This post wouldn’t be possible without my friends Joe and Paul, who helped me track down the issue and with the internals of it.

Thumper

---

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())[1]', '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.

Jumper

---

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.

Lumper

---

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!

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.