Temp Tables - The Good, Bad and The Ugly

Jeff Taylor

jssug.org

Every 3rd Wednesday 6-8pm

Except for May, November and December

https://sqlsaturday.com

https://sqlsaturday.com/atl

Atlanta 2025 - AI & BI

March 8th, 2025

https://sqlsaturday.com

https://sqlsaturday.com/jax

Annual Free Data Conference

May 2nd & 3rd, 2025

Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io

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;