you're reading...
SQL Server, SQL Server Internals

Tempdb Part 1: WHAT IS tempdb

This is part 1 of a several part post on tempdb of which we will explore many aspects of tempdb usage and tunning. For many tempdb is an enigma and can also be the root cause for some very poor performance of your database environmant.  Any discussion of tempdb should begin with an explanation of what tempdb is and how it is used. tempdb is your work area for the SQL Server database engine. One analogy that works well is a well-organized junk drawer.
The database engine uses tempdb for all of the following:
Temporary user objects
• Temp tables
• Table variables
• Temp procedures
• Global temp tables
• Cursors
Work tables / work files / intermediate results
• Sorts
• o Index rebuilds
• o Some Group By, Order By, or Union operations
• Spools
• Hash joins and hash aggregates
• Spills to tempdb
• Temporary LOB storage
• Returned tables for table valued functions
• Service broker caching
• Common Table Expressions (CTEs)
Version store for data modification transactions from
• Read committed snapshot isolation
• Snapshot isolation
• Online indexes
• After triggers
• Multiple Active Results Sets (MARS)

Looking at the types of objects and information that the database engine uses tempdb for it is easy to see how this “Junk Drawer” may get cluttered. In addition to utilizing tempdb for a myriad of operations SQL also has to keep track of everything in tempdb.
Objects in tempdb are organized into three groups: internal objects, external objects, and the version store. You can see how things are allocated across these three categories by querying the following DMV sys.dm_db_file_space_usage

SELECT FreePages =SUM(unallocated_extent_page_count),
FreeSpaceMB =SUM(unallocated_extent_page_count)/128.0,
VersionStorePages =SUM(version_store_reserved_page_count),
VersionStoreMB =SUM(version_store_reserved_page_count)/128.0,
InternalObjectPages =SUM(internal_object_reserved_page_count),
InternalObjectsMB =SUM(internal_object_reserved_page_count)/128.0,
UserObjectPages =SUM(user_object_reserved_page_count),
UserObjectsMB =SUM(user_object_reserved_page_count)/128.0
FROM sys.dm_db_file_space_usage;

You should see something similar to the following:


More to follow on tempdb … Tempdb Part 2: Configuration and Contention

Bonn Appetite


About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: