Category: tempdb

I Love #Temp Tables

I solve a lot of problems with #temp tables, indeed I do. And I hate people who are reflexively anti-#temp table.

If you’re into jokes (I’m definitely not into jokes; SQL is serious business), you could even call them #foolish.

Get it?

Ahem 🎤👈

However (!) I learned a lesson recently about how using them in certain ways can cause weird plan cache pollution. When you’re hitting the issue, the optional_spid column in dm_exec_plan_attributes  will be populated with a non-zero value. You can use this query to quickly check for that happening on your system:

SELECT 
    pa.attribute, 
    pa.value, 
    decp.refcounts,
    decp.usecounts,
    decp.size_in_bytes,
    decp.memory_object_address,
    decp.cacheobjtype,
    decp.objtype,
    decp.plan_handle
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_plan_attributes (decp.plan_handle)  AS pa
WHERE pa.attribute = N'optional_spid'
AND   pa.value > 0;

Let’s talk about those!

Creating Across Stored Procedure Executions

Check out this piece of code:

CREATE OR ALTER PROCEDURE
    dbo.no_spid
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #t (id int);
    INSERT #t (id) VALUES (1);

    EXEC dbo.a_spid; --Hi

END;
GO 


CREATE OR ALTER PROCEDURE
    dbo.a_spid
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #t (id int);
    INSERT #t (id) VALUES (2);

END;
GO

In the first proc, we create a #temp table, and insert a row, then execute another proc, where we create a #temp table with the same name and definition and insert a row.

Using the above query, we’ll see this:

And if we run sp_BlitzCache, we’ll indeed see multiple plans for a_spid, though no_spid seems to get plans associated with it because the plans are hashed to the same value. Heh. That plan cache… 🙄

Referencing Across Stored Procedure Executions

Check out this code:

CREATE OR ALTER PROCEDURE
    dbo.internal
(
    @c bigint
)
AS
BEGIN
SET NOCOUNT ON;

    CREATE TABLE #t(id int);
    INSERT #t (id) VALUES (1);

    SELECT 
        @c = COUNT_BIG(*)
    FROM #t AS t
    WHERE 1 = (SELECT 1);

    EXEC dbo.not_internal 0; --Hi

END;
GO 

CREATE OR ALTER PROCEDURE
    dbo.not_internal
(
    @c bigint
)
AS
BEGIN

    INSERT #t (id) VALUES (2);

    SELECT 
        @c = COUNT_BIG(*)
    FROM #t AS t
    WHERE 1 = (SELECT 1);

END;
GO

We’re creating a #temp table in one stored procedure, and then executing another stored procedure that references the same #temp table this time.

Just like above, if we execute the procs across a couple different SSMS tabs, we’ll see this:

And from the plan cache:

Same thing as last time. Multiple plans for not_internal. In both cases, the outer stored procedure has an optional_spid of 0, but the inner procedure has the spid that executed it attached.

Dynamic SQL

My fellow blogger Joe Obbish came up with this one, which is really interesting. It’s necessary to point out that this is Joe’s code, so no one asks me why the formatting is so ugly 😃

CREATE OR ALTER PROCEDURE no_optional_spid AS
BEGIN
    CREATE TABLE #obj_count (
    [DB_NAME] SYSNAME NOT NULL,
    OBJECT_COUNT BIGINT NOT NULL
    );

    DECLARE @db_name SYSNAME = 'Crap';

    DECLARE @sql NVARCHAR(4000) = N'SELECT @db_name, COUNT_BIG(*)
    FROM ' + QUOTENAME(@db_name) + '.sys.objects';

    INSERT INTO #obj_count
    EXEC sp_executesql @sql, N'@db_name SYSNAME', @db_name = @db_name;
END;
GO 

CREATE OR ALTER PROCEDURE has_optional_spid AS
BEGIN
    CREATE TABLE #obj_count (
    [DB_NAME] SYSNAME NOT NULL,
    OBJECT_COUNT BIGINT NOT NULL
    );

    DECLARE @db_name SYSNAME = 'Crap';

    DECLARE @sql NVARCHAR(4000) = N'INSERT INTO #obj_count
    SELECT @db_name, COUNT_BIG(*)
    FROM ' + QUOTENAME(@db_name) + '.sys.objects';

    EXEC sp_executesql @sql, N'@db_name SYSNAME', @db_name = @db_name;

END;
GO 

In this case, we have two procs that create a #temp table with the same definition, and insert into them with dynamic SQL. I have a feeling that this would also occur under other circumstances where you use the INSERT…EXEC paradigm, e.g. a stored procedure.

Same deal here, if we look at the same things, except that it’s more helpful to look at the execution_count column in sp_BlitzCache.

And…

Everything has 200 executions, except the internal parameter table scan that does the #temp table insert:

5k Finisher

This post explores a few scenarios where the *optional_spid* cache pollution does happen. There are likely more, and I’m happy to add scenarios if any readers out there have them.

There are plenty of scenarios where this scenario doesn’t happen, too. I don’t want you to think it’s universal. Using #temp tables with the same name but different definitions, or without the cross-referencing, etc. won’t cause this issue to happen.

I tried a bunch of stuff that I thought would cause the problem, but didn’t.

So yeah. Love your #temp tables, too.

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.

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.