Temp Tables - The Good, Bad and The Ugly
Jeff Taylor
jssug.org
Every 3rd Wednesday 6-8pm
Except for May, November and December
Atlanta 2025 - AI & BI
March 8th, 2025
Annual Free Data Conference
May 2nd & 3rd, 2025
Jeff Taylor
Principal Data Consultant
Database Consulting, LLC
Temp Tables - The Good, Bad and The Ugly
Jeff Taylor
Temp Tables
- What is a temp table?
- What types of Temp Tables are there?
What are they?
- Local Temp Tables - #MyTempTable
- Global Temp Tables - ##MyOtherTempTable
- Table Variables - @MyTableVariable
Temp Tables
Where do they live?
tempdb
Temp Tables
- Intermediate Data Storage - Complex Queries, Batch Processing
- Data Manipulation - Change data without affecting original tables
- Session Specific Data - Stores data for user session
- Performance Improvement - Break down data into smaller steps
Why Use Them?
Temp Tables
- Storing intermediate results
- Isolation of data
- Dynamic creation and deletion
Pros
Temp Tables
- Resource Consumption - Memory and disk space usage
- Performance Issues - Recompilation and I/O overhead
- Concurrency Problems - Locking and blocking
- Maintenance Challenges - Cleanup and code complexity
- Scalability Concerns - Impact on large-scale applications - increased growth
Cons
Temp Tables
- Resource Consumption - Impact on tempdb
- Performance Issues - Execution plan recompilation and I/O overhead, No Statistics, No Indexes
- Maintenance Challenges - Ensuring proper cleanup and managing code complexity
- Scalability Concerns - Large datasets, more data large tables each insert
Detailed Cons
Temp Tables
- Designed for in-memory
- Designed to reduce or eliminate I/O contention
Table Variables Pros
Temp Tables
- Memory pressure from other processes
- Not enough memory on the server
- Can spill to disk
Table Variables Cons
Temp Tables
- Memory pressure from other processes
- Not enough memory on the server
- Can spill to disk
Table Variables Cons
Temp Tables
- Monitor tempdb - Database Monitoring Tool
- Use Table Variables for smaller datasets
- Optimize Queries - reduce the need for temp tables - write better queries
- Limit Scope - reduce lifespan and scope of temporary tables
Best Practices
Temp Tables
Check Memory Usage
-- Check memory usage
SELECT
type,
pages_kb / 1024 AS memory_mb
FROM
sys.dm_os_memory_clerks
WHERE
type = 'MEMORYCLERK_SQLBUFFERPOOL';
Temp Tables
Check tempdb Usage
-- Check tempdb usage
SELECT
SUM(unallocated_extent_page_count) * 8 AS unallocated_space_kb,
SUM(version_store_reserved_page_count) * 8 AS version_store_space_kb,
SUM(user_object_reserved_page_count) * 8 AS user_object_space_kb,
SUM(internal_object_reserved_page_count) * 8 AS internal_object_space_kb,
SUM(mixed_extent_page_count) * 8 AS mixed_extent_space_kb
FROM
sys.dm_db_file_space_usage;
Temp Tables
Monitoring Tools
- AppDynamics
- Datadog
- New Relic
- Paessler PRTG
- ManageEngine Applications Manager
- Dynatrace
- Redgate SQL Monitor
- SolarWinds SQL Sentry
- SolarWinds DPA
- Quest Foglight
- Quest Spotlight
- SQL Diagnostic Manager
Temp Tables
How To
CREATE TABLE #TempTable (
TempTableId INT,
Name VARCHAR(50)
);
INSERT INTO #TempTable (TempTableId, Name)
VALUES (1, 'John Doe'), (2, 'Jane Smith');
SELECT * FROM #TempTable;
DROP TABLE #TempTable;Temp Tables
How To
DECLARE @TableVar TABLE (
TempTableId INT,
Name VARCHAR(50)
);
INSERT INTO @TableVar (TempTableId, Name)
VALUES (1, 'John Doe'), (2, 'Jane Smith');
SELECT * FROM @TableVar;Temp Tables
How To
CREATE TABLE #SalesSummary (
ProductID INT,
TotalSales DECIMAL(10, 2)
);
INSERT INTO #SalesSummary (ProductID, TotalSales)
SELECT ProductID, SUM(SalesAmount)
FROM Sales
GROUP BY ProductID;
SELECT * FROM #SalesSummary;
DROP TABLE #SalesSummary;Temp Tables
How To
DECLARE @SalesSummary TABLE (
ProductID INT,
TotalSales DECIMAL(10, 2)
);
INSERT INTO @SalesSummary (ProductID, TotalSales)
SELECT ProductID, SUM(SalesAmount)
FROM Sales
GROUP BY ProductID;
SELECT * FROM @SalesSummary;Temp Tables
Performance Comparison
- Small dataset
- Large dataset
- High concurrency
Temp Tables
The Ugly
- Entire table with many columns into temp table
- No filtering
- No indexes
- No statistics
-
Scan of 250 million records, insert 250 million records
- 3000ms read/write latency on tempdb
Temp Tables
The Ugly
SELECT
DISTINCT
REPLACE(REPLACE(REPLACE(REPLACE(PhoneNumber
, '(', ''), ')', ''), '-', ''), ' ', '') AS PhoneNumber
INTO #temp
FROM
dbo.client AS c
JOIN dbo.addresses AS a
ON c.clientid = a.clientid
AND a.isactive = 1
JOIN dbo.contact AS c
ON a.addressid = c.addressid
AND c.isactive = 1
JOIN dbo.phone AS p
ON c.contactid = p.contactid
WHERE
ISNULL(PhoneNumber, '') != ''
AND c.clientid = @ClientId;Temp Tables
The Ugly
SELECT
tp.*
INTO #temp1
FROM
dbo.dncnumbers AS dnc
JOIN #temp AS tp
ON tp.PhoneNumber = dnc.dnc COLLATE Latin1_General_CI_AI;Temp Tables
The Ugly
IF EXISTS
(
SELECT
*
FROM
#temp1
)
BEGIN
INSERT INTO
dbo.MyNewTable
(clientid, startdate, createdby, createddate, isactive)
SELECT
@ClientId,
GETDATE(),
@UserId,
GETDATE(),
1;
END;Temp Tables
The Ugly
Temp Tables
The Ugly
Temp Tables
The Ugly
Temp Tables
The Ugly
Temp Tables
The Ugly
How many times was this executed in an hour?
Thousands
Temp Tables
The Fix
IF EXISTS ( SELECT 1
FROM
dbo.Client AS c
INNER JOIN dbo.Addresses AS a
ON c.ClientId = ar.ClientId
AND a.IsActive = 1
INNER JOIN dbo.Contact AS c
ON a.AddressId = c.AddressId
AND c.IsActive = 1
INNER JOIN dbo.Phone AS p
ON c.Contactid = p.ContactId
INNER JOIN dbo.DNCNumbers AS dnc
ON REPLACE(REPLACE(REPLACE(REPLACE(p.PhoneNumber
, '(', ''), ')', ''), '-', ''), ' ', '') = dnc.DNC
WHERE
c.ClientId = @ClientId
AND p.PhoneNumber IS NOT NULL
GROUP BY
dnc.DNC)
BEGIN
INSERT INTO
dbo.NewTable
(CLIENTID, STARTDATE, CREATEDBY, CREATEDDATE, IsActive)
SELECT
@ClientId,
GETDATE(),
@UserId,
GETDATE(),
1;
END;Temp Tables
The Fix
Temp Tables
The Fix
Temp Tables
The Fix
Temp Tables
Recap
- Monitor
- Improve tempdb performance
- Filter first
- Minimize data
- Stop using
Temp Tables
Questions?
Thank you
Jeff Taylor
Thank you for attending my session today. If you have any additional questions please don't hesitate to reach out. My contact information is below.
Temp Tables - The Good, Bad and The Ugly
By reviewmydb
