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.
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.
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.
Everything has 200 executions, except the internal parameter table scan that does the #temp table insert:
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!
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.
One thought on “How #Temporary Tables Can Cause Plan Cache Pollution In SQL Server”
Comments are closed.