Words: 1100
Time to read: ~ 6 minutes
Nothing
Welcome to T-SQL Tuesday, the monthly blog party started by Adam Machanic and maintained by Steve Jones.
Each month, a different host chooses a topic for discussion. This month, we have Jeff Taylor asking us about “temp tables, friend or foe?“
Clicka-de-bait
Ages ago, I broke one of the stored procedures in Brent Ozar‘s First Responder Kit.
I filed that feat under the heading “Cool; Good to Know“, and then promptly forgot about it. Part of me thinks that I forgot about it because I didn’t understand how I accomplished it. While another part is sure I forgot about it cause “good to know” wasn’t on the list of tasks with looming deadlines.
I’m a man of many parts, but of more deadlines. Now, let’s understand it together!
First, there was the ooze
It’s a tactic of dealing with volatile amounts of data to call different stored procedures based on the number of records coming into it. I swear, I didn’t make this up!
Small numbers of records may be fine, but an odd, massive number may trigger a new plan. One that frequent executions and resources on the machine may not agree with.
So, normal, small, fast, very frequent executions get the “happy” path, but the big guy can go his own way. Free to roam, hog, and frizzle away everything he has!
We encountered this scenario once and decided to implement this tactic.
However, while reviewing this new stored procedure volatility countermeasure, I came across a temp table that wasn’t created anywhere in the procedure.
Stored Procedures are isolated. Aren’t they?
Or was it the sludge?
Let’s talk about Dynamic SQL for a moment.
I’m sure, although I won’t make up statistics, that you’ve used Dynamic SQL to query over different databases and collated their results.
Here’s a completely made-up example (Don’t do this. I’m sure it’s stupid, but it’s also very late right now)
DROP TABLE IF EXISTS #tables;CREATE TABLE #tables( tables_ID int NOT NULL IDENTITY(1,1), [database_name] nvarchar(255) NOT NULL, table_count int NULL);INSERT INTO #tables ([database_name]) SELECT [name] FROM sys.databases;SELECT * FROM #tables;GODECLARE @i int = 1;DECLARE @max int;DECLARE @context nvarchar(MAX);DECLARE @context_stub nvarchar(MAX) = N'.sys.sp_executesql';DECLARE @sql nvarchar(MAX) = N'WITH table_counts AS ( SELECT COUNT(*) AS database_table_count FROM sys.tables ) UPDATE #tables SET table_count = database_table_count FROM table_counts WHERE tables_ID = @ID';SELECT @max = MAX(tables_ID) FROM #tables;WHILE @i <= @maxBEGIN SET @context = CONCAT( QUOTENAME( ( SELECT TOP (1) [database_name] FROM #tables WHERE tables_ID = @i ) ), @context_stub ); EXEC @context @sql, N'@ID int', @ID = @i SET @i += 1;END;SELECT * FROM #tables
I mean, look at line 25 in the code above. We didn’t create any temp table in that dynamic sql, but we’re updating it fine!
There was something I was forgetting: Scope!
Scopé (the é is pronounced)
I had never fully thought about this before.
I thought Dynamic SQL was just strings being executed (and it still kinda is), but it operates under a nested scope of the session.
So, when we say EXEC [sys].[sp_executesql] @stmt = N'INSERT INTO #temp…, for a temp table we’re created in the parent session, we’re ignoring scope boundaries.
The same thing happens with Stored Procedures.
Table Variables? No.
CREATE OR ALTER PROC dbo.TableVar2ASBEGIN INSERT INTO @table_variable (ID) VALUES (1);END;GOCREATE OR ALTER PROC dbo.TableVar1ASBEGIN DECLARE @table_variable TABLE (ID int); EXEC dbo.TableVar2;END;GOEXEC dbo.TableVar1;
Variables? No.
CREATE OR ALTER PROC dbo.Var2ASBEGIN SET @i = 2;END;GOCREATE OR ALTER PROC dbo.Var1ASBEGIN DECLARE @i int; EXEC dbo.Var2;END;GOEXEC dbo.Var1;
Temp Tables? Yes!
CREATE OR ALTER PROC dbo.Proc1ASBEGIN CREATE TABLE #t1 ( ID int NOT NULL ); INSERT INTO #t1 (ID) VALUES (1); SELECT ID FROM #t1;END;GOEXEC dbo.Proc1;GOCREATE OR ALTER PROC dbo.Proc2ASBEGIN CREATE TABLE #t2 ( ID int NOT NULL ); EXEC dbo.Proc3; SELECT ID FROM #t2;END;GOCREATE OR ALTER PROC dbo.Proc3ASBEGIN INSERT INTO #t2 (ID) VALUES (3);END;GOEXEC dbo.Proc2;
That’s what the temp table and the stored procedures were doing. Boundary violations don’t really count when it’s my nested scope!
It’s Actually a Donut
Now that that tangent is all squared away, let’s return to how I broke a First Responder Kit stored procedure.
CREATE OR ALTER PROC dbo.NormalProc
AS
BEGIN
CREATE TABLE #BlitzFirstResults
(
ID int NOT NULL
);
INSERT INTO #BlitzFirstResults VALUES (1);
SELECT ID FROM #BlitzFirstResults;
END;
GO
EXEC dbo.NormalProc;
GO
/*
DROP PROC dbo.NormalProc;
DROP TABLE IF EXISTS #BlitzFirstResults
*/
/*
DROP TABLE IF EXISTS #BlitzFirstResults
*/
IF OBJECT_ID(N'tempdb..#BlitzFirstResults') IS NULL
BEGIN
CREATE TABLE #BlitzFirstResults
(
BlitzFirstResults_ID int NOT NULL
);
END
SELECT * FROM #BlitzFirstResults;
EXEC dbo.NormalProc
Different Columns Does Not A Stored Procedure Temp Table Boundary Violation Good Time Make
So, yeah … in truth, I didn’t so much break the stored procedure as I interfered with it…but that just doesn’t sound great when I say it like that.
Now, I know that my temp table in the parent session …got in the way… of the stored procedure’s inner temp table.
Anyway, things happened
What does this have to do with T-SQL Tuesday? Oh! The barest of passing similarities, but I’m team temp tables. They’re weird and (more) wonderful than I realised, but I’ll leave it to smarter people than myself to give you the raw numbers.
All I know is that I still have billion-row tables, procedures with multiple WHERE col1 = @param1 OR @param1 IS NULL) […] filters, and uptime requirements that make finding an outage window for UPDATE STATS more difficult than a greyscale Where’s Wally book.
I’ll take every trick in the book.
[Speaking of books, all this is wonderfully documented in the Microsoft Docs page, but someone didn’t think to read that part… – Editor Shane]
