We all know how important tempdb is and know that tempdb gets recreated every time SQL Server is restarted. We place tempdb on a fast disk array exclusively hoping it will run as fast as possible. Here we dig a little deeper to focus on how space is being used inside tempdb.
Space Usage categories
Roughly, we can separate tempdb usage into three categories:
- User object
- Internal job
- Version store
I would like to focus our discussion on few of the common operational tasks that utilize tempdb.
User Objects
Any user with permission to connect to the SQL instance can create a temporary table and table variables. You can’t back up or restore tempdb, so any temporary object or table variable created previously (before SQL Server was restarted) will be wiped out and cannot be recovered.
The scope of user objects are either in the user session or in the routine. Routine can be in the form of a stored procedure, function or trigger. User objects in tempdb can be like one of the list below:
- User table and index (created explicitly in tempdb)
- Global temporary table and index
- Local temporary table and index
- Table variable
There are two ways you can query to get the space used by tempdb user objects.
select convert(numeric(10,2),round(sum(data_pages)*8/1024.,2)) as user_object_reserved_MB
from tempdb.sys.allocation_units a
inner join tempdb.sys.partitions b on a.container_id = b.partition_id
inner join tempdb.sys.objects c on b.object_id = c.object_id
Returned result:
or:
select
reserved_MB= convert(numeric(10,2),round((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024.,2)) ,
unallocated_extent_MB =convert(numeric(10,2),round(unallocated_extent_page_count*8/1024.,2)),
user_object_reserved_page_count,
user_object_reserved_MB =convert(numeric(10,2),round(user_object_reserved_page_count*8/1024.,2))
from sys.dm_db_file_space_usage
Returned result:
Note that the returned value for [user_object_reserved_MB] from both queries is almost the same.
Internal objects
There are three types of objects considered to be internal objects.
- Work tables
- Spooling, to hold intermediate results during a large query
- DBCC CHECKDB or DBCC CHECKTABLE
- Temporary large object storage (LOB)
- Processing SQL Service Broker Objects
- Common table expression
- Keyset-driven and static cursors
- Work files
- Hash join or hash aggregate operations
- Sort units
- ORDER BY, GROUP BY, UNION queries
- Index rebuilt or creation (with sort in tempdb is specified)
Unfortunately, there is no catalog view or dynamic management object that can provide the detailed page count information for each internal object. You can get the total number of pages in tempdb allocated for internal objects from DMV — sys.dm_db_file_space_usage.
select
reserved_MB=(unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024.,
internal_object_reserved_page_count,
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage
Returned result for internal object space used in tempdb:
Version Store
Version store is a collection of data pages that holds the data rows used for row versioning. Below are the features that use version store:
- Triggers
- MARS
- Online Index operation
- Row versioning-based transaction isolation level
- Read-committed isolation level (statement-level read consistency)
- Snapshot isolation level (transaction-level read consistency)
For read-committed isolation level, version store only needs to keep the row version until the end of the select statement.
For snapshot isolation level, version store will need to keep the version of the row when the transaction started so the transaction can refer back the original version of the row. If the transaction happens to be unreasonably long, with many rows modified by some other transactions, the snapshot query may fail if tempdb has space shortage and version store can no longer retain all the version required.
There are performance counters that can be used to monitor the size of tempdb and version store.
SQLServer: Transactions performance object
Object counter Description Free Space in tempdb Free space in kilobytes for tempdb Version store size in KB, tempdb being used to store snapshot isolation level row versions Version generation rate row versions are added to tempdb version store (Kilobyte/sec) Longest Transaction Running Time The length of time (in seconds) since the start of the transaction that has been active longer than any other current transaction. Snapshot Transactions The number of currently active transactions using the snapshot isolation level.
You can also query dynamic management view — sys.dm_db_file_space_usage — to get the total number of pages currently allocated in tempdb for versions tore.
select
reserved_MB=(unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024.,
version_store_reserved_page_count,
version_store_reserved_MB =version_store_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage
Returned result for version stored occupant space on tempdb:
Now let’s look into some features that use tempdb and how to estimate the space required on tempdb for performing these operations.
DBCC CHECKDB
For DBCC CHECKDB, we can use DBCC CHECKDB WITH ESTIMATEONLY to check how much space it requires without actually executing the command. It returns resultset for [Estimated TEMPDB space needed for CHECKALLOC(KB)] and [Estimated TEMPDB space needed for CHECKTABLES(KB)].
You may want to run this estimation for a very large database and ensure your tempdb has sufficient space to complete the task.
Index Creation and Rebuild
Tempdb space is only needed if SORT_IN_TEMPDB is on. The tempdb space is needed to store the intermediate sort result for index building. Let’s take a look at both online index operation and offline index operation.
Important note; you cannot use SORT_IN_TEMPDB when dropping an index.
1. Offline Index operation and SORT_IN_TEMPDB
Here are examples for how much space we need on tempdb when creating/building index offline with the sort in tempdb option.
Create Non-clustered index:
tempdb should be big enough to store leaf rows of the indexes
index leaf row size * number of rows
Create Clustered index:
tempdb should be big enough to store data rows of the table.
table row size * number of rows
Clustered and non-clustered indexes from same table:
tempdb should be big enough to carry largest index, usually clustered index is largest among all indexes from one table.
Largest index row size * number of rows
If rebuilding clustered index with different cluster key columns:
Since new clustered index leaf row size and old clustered index leaf row size are identical, so it’s basically the total data rows size of the table
clustered index row size * number of rows
If there are cases when SQL optimizer doesn’t do any sorting or if the sorting area can be performed in memory, then SORT_IN_TEMPDB will be ignored and no space will be needed in tempdb.
2. Online Clustered Index Operation and SORT_IN_TEMPDB
When performing an online index operation for a clustered index, a temporary mapping index is created to fulfill the operation. Below is the formula for calculating a temporary mapping index.
Temporary mapping index size = (number of rows of the table) * (old key size + new index key size - overlapping index key size) / (fillfactor in percentage) If there are key columns overlapping on the index between the old index key and new index key, then then we need to subtract the overlapping key size.
Create Clustered Index online on a heap table:
tempdb should be big enough to store data rows of the table and temporary mapping index
Data rows of the table= Table row size * number of rows
Temporary mapping index size = (number of rows of the table) * (RID+ new index key size) / (fillfactor in percentage)
Total space = Data rows of the table + Temporary mapping index
Rebuilding Clustered index with different clustered key columns:
tempdb should be big enough to store data rows of the table and temporary mapping index
Data rows of the table = Table row size * number of rows
Temporary mapping index size = (number of rows of the table) * (old index key size+ new index key size - size in overlapping) / (fillfactor in percentage)
The total tempdb space needed = Data rows of the table + Temporary mapping index
Conclusion
It’s imperative to understand how SQL Server uses tempdb so we can do better perform capacity planning when setting up tempdb. A proper sizing and configured tempdb can improve the overall performance of a SQL instance.
