Mastering Tempdb
Monica Rathbun, Consultant
Denny Cherry and Associates
Consulting
Consultant
Denny Cherry & Associates
Consulting
User Group Leader: Hampton Roads VA
Data Saturday VA Beach Organizer
Monica Rathbun
She, Her
/sqlespresso
@SQLEspresso
SQLEspresso.com
1. Drag & drop your photo onto
the slide.
2. Resize & crop so the key part
of the photo will show up
here. You will want the
picture to go to ALL THE WAY
to the top, bottom, and left
side. On the right, position it
so it only just barely covers
the red line.
IMPORTANT: Only resize photos
using the CORNER SQUARE to
maintain proportions (never have
distorted photos in your slides).
Use that to get the HEIGHT you
need (so it touches the top and
bottom of the slide. Use CROP
(double click the photo and it will
show up under “picture format”
tab) to get the photo to the
WIDTH you need.
Always Ask Questions
Interrupt me
This is a two-way conversation
let’s learn from each other’s
experiences
01
WHAT IS IT
02
CONFIGURE
03
CONTENTIO
N
04
LOOKING
INSIDE
05
SPILLS
06
ALWAYSON
AGs
DEMOS
Understanding
TEMPDB
What is it?
It’s a Global Resource
Used by Everyone
Works in a Round Robin fashion
Non-Durable
What is it?
Minimal Logging
Objects are frequently created and
destroyed at VERY high concurrency
Used for data that doesn’t fit in
memory
Needs to be accessed FAST
Remember
These
Things
It’s a Global Resource
Used By Everyone
Works in a Round Robin fashion
You Only
Have 1
Things
WILL
Disappear
Size
Matters
What’s in
it?
Temporary user objects that are
explicitly created
Internal objects created by the
database engine
Worktables to store intermediate
results
What’s in
it?
Work files for hash join or hash
aggregate operations
Intermediate sort results
Version stores
TEMPDB
Configuration
Pin to High
Performing
Disk
Multiple Files
1 file per CPU
core up to 8
files *
Properly Sized
Auto Growth
Put MDF and
LDF on Same
Disk
Proper Configuration is KEY
TEMPDB
Contention
Types
Object Allocation
Contention
• Contention for
metadata pages
used to manage
space allocation
in data files
• PAGELATCH
database id 2:
waits on PFS and
GAM Pages
Metadata
Contention
• Pages that
belong to
systems object
used to track
table metadata
• PAGELATCH
waits on pages
that belong to
system objects
Temp Table Cache
Contention
• Contention for
memory objects
associated with
Cache
• Either
CMEMTHREAD
waits or
SOS_CACHESTORE
spinlock waits
Slide Credit Sourced from Pam Lahoud
Object
Allocation
Example
Uses “Special”
Pages in
Memory
• Thread 1
• Thread 2
• Thread 3
Physical Disk
• GAM, SGAM, PFS
(The special pages)
• Determine “WHERE”
to write the data
LATCH (light weight
lock)
• Whenever a process
needs to update one
of these special page
it takes a latch
Queuing Causes
Waits/Delays
• Lots of processes
happen to hit at
the exact same
time
• They all need to
find a place to
store some data
Trace Flag
1118/1117
Pre 2016
Heavy Contention
in Metadata fixed
2016 SP1 CU 8 &
2017 CU 5
Heavy PFS page
Contention fixed
2016 SP1 CU9, SP2
CU1 & 2017 CU7
Proper Use of
Table Variables &
Temp Tables
Use new inline
index creation syntax
SQL Server 2014
Remember
It has nothing to do
with I/O
It’s Latch, Object
or
Metadata
Contention
Great Resource by Pam Lahoud
https://blogs.msdn.microsoft.com/sql_server_team/
tempdb-files-and-trace-flags-and-updates-oh-my/
Less Overhead than #Temp tables
Rule of thumb <100 rows
No Statistics on Variables
Trace Flag 2453 allows table variables to trigger recompile
DECLARE @MyFakeTable TABLE
(Field1 VARCHAR(10) NULL,
Field2 INT NOT NULL)
Do not explicitly DROP temp tables at the end of a stored
procedure – they don’t even use it (no operation)
Do not ALTER temp tables
Do not TRUNCATE temp tables
The more temp tables that need to be deleted, the more likely you
are to hit the contention.
CREATE TABLE #MyFakeTable
(Field1 VARCHAR(10) NULL,
Field2 INT NOT NULL)
1. Drag & drop your photo onto
the slide.
2. Resize & crop so the key part
of the photo will show up
here. You will want the
picture to go to ALL THE WAY
to the top, bottom, and left
side. On the right, position it
so it only just barely covers
the red line.
IMPORTANT: Only resize photos
using the CORNER SQUARE to
maintain proportions (never have
distorted photos in your slides).
Use that to get the HEIGHT you
need (so it touches the top and
bottom of the slide. Use CROP
(double click the photo and it will
show up under “picture format”
tab) to get the photo to the
WIDTH you need.
TEMP Table Cache Improvements
Concurrent PFS Updates
Memory Optimized TempDB
Metadata
Table Variable Deferred
Compilation
Table
Variable
Deferred
Compilation
Accurate counts down streamed to other plan operators
Cardinality estimations on ACTUAL rows
Improves overall performance of queries using Table Variables
Improves plan quality
How we used to fix these
Trace Flag 2453
Option (Recompile)
Explicit Plan Hints
Temporary Table
Incorrect Resources granted: note the spill
to disk on the sort because of counts and the
nested loops
Better row counts lead to better operators:
note the hash matches and no spill on sort to
tempdb
TEMPDB
Space
Usage
What’s
taking
space?
sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
What’s in
the Version
Store?
sys.dm_tran_active_snapshot_
database_transactions
sys.dm_tran_version_store
ALLOW_SNAPSHOT_ISOLATION
READ_COMMITTED_SNAPSHOT -
ISSUE
CHECKPOINT
DBCC
SHRINKFILE
SHRINK
?
DONE
DBCC
DROPCLEANBUFFERS
DBCC
FREEPROCCACHE
DBCC
FREESYSTEMCACHE
DBCC
FREESESSIONCACHE
DBCC
SHRINKFILE
DBCC
SHRINKFILE
DONE
SHRINK
?
These operations remove all
caches, which will impact server performance. Don’t do this
stuff unless necessary. Be sure to database scope when
possible.
Spills to
TEMPDB
The spill data to TempDB warning means that the query
was not granted enough memory to finish the operation
and spilled over into the TempDB to complete the
operation and had to go to DISK.
Based on Statistics
THE FIX
UPDATE YOUR
STATISTICS
UNDER ESTIMATES
Spills to TEMPDB – causing significant performance
degradation
Adaptive
Query
Processing
Addresses
Repeating
Workloads
Recalculate
Actual
Memory
Requirement
s
Are reported to memory grant
feedback
After workload is run multiple
times
Reducing excessive memory
grants that impact concurrency
and fixing under-estimated
memory grants
HOW ALWAYS ON
AVAILABILITY
GROUPS USE
TEMPDB
What
about
AlwaysOn
AGs?
Pertains to READ ONLY Replicas ONLY
Use SnapShot Isolation by default i.e. Version
Store
Any query run uses the version store
Long running queries can fill up TEMPDB with
Version Store data
Statistics are created in TEMPDB
DEFAULTED CHANGED
AND NOTHING YOU
NEED TO DO BUT
UPGRADE TO 2022
TEMPDB Contention & Config by Pam Lahoud
https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-
flags-and-updates-oh-my/
TEMPDB Usage by Kendra Little
https://littlekendra.com/2009/08/27/whos-using-all-that-space-in-tempdb-and-
whats-their-plan/
Always on Statistics by Rajendra Gupta
https://www.sqlshack.com/sql-server-statistics-in-always-on-
availability-groups/
TEMPDB by Microsoft
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-
database?view=sql-server-ver15
Bob Ward Internals of TEMPDB https://www.youtube.com/watch?v=SvseGMobe2w
