Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 10, 2011

Tempdb Space Usage in SQL Server

By Claire Hsu

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.

  1. 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
  2. Work files
    • Hash join or hash aggregate operations
  3. 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

SQL Server 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:

SQL Server dynamic management view

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.

See all articles by Claire Hsu



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date