SQL Server tempdb Considerations

Since database file location is so important to I/O performance, you need to consider functional changes to tempdb when you create your primary data-file placement strategy. The reason for this is that tempdb performance has a rather large impact on system performance because it is the most dynamic database on the system and needs to be the quickest.

Like all other databases, tempdb typically consists of a primary data and log files. tempdb is used to store user objects and internal objects. It also has two version stores. A versionstore is a collection of data pages that hold data rows required to support particular features that use row versioning. These two version stores are as follows:

• Row versions generated by data modification transactions in tempdb that use snapshot or read committed row versioning isolation levels
• Row versions in tempdb generated by data modification transactions for features such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers

Beginning with SQL Server 2005 and continuing in SQL Server 2012, tempdb has added support for the following large set of features that create user and internal objects or version stores:

• Query
• Triggers
• Snapshot isolation and read committed snapshots
• Multiple Active Result Sets (MARS)
• Online index creation
• Temporary tables, table variables, and table-valued functions
• DBCC Check
• Large Object (LOB) parameters
• Cursors
• Service Broker and event notification
• XML and Large Object (LOB) variable
• Query notifications
• Database mail
• Index creation
• User-defined functions

As a result, placing the tempdb database on a dedicated and extremely fast I/O subsystem can ensure good performance. A great deal of work has been performed on tempdb internals to improve scalability.

When you restart SQL Server, tempdb is the only database that returns to the original default size of 8MB or to the predefined size set by the administrator. It can then grow from there based on usage requirements. During the autogrow operation, threads can lock database resources during the database-growth operation, affecting server concurrency. To avoid timeouts, the autogrow operation should be set to a growth rate that is appropriate for your environment. In general, the growth rate should be set to a number that will allow the file to grow in less than 2 minutes.

You should do at least some type of capacity planning for tempdb to ensure that it’s properly sized and can handle the needs of your enterprise system. At a minimum, perform the following:

1.   Take into consideration the size of your existing tempdb.

2.   Monitor tempdb while running the processes known to affect tempdb the most. The following query outputs the five executing tasks that make the most use of tempdb:

SELECT top 5 * FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC

3.   Rebuild the index of your largest table online while monitoring tempdb. Don’t be surprised if this number turns out to be two times the table size because this process now takes place in tempdb.

Following is a recommended query that needs be run at regular intervals to monitor tempdb size. It is recommended that this is run every week, at a minimum. This query identifies and expresses tempdb space used, (in kilobytes) by internal objects, free space, version store, and user objects:

select sum(user_object_reserved_page_count)*8 as user_objects_kb,
    sum(internal_object_reserved_page_count)*8 as internal_objects_kb,
    sum(version_store_reserved_page_count)*8  as version_store_kb,
    sum(unallocated_extent_page_count)*8 as freespace_kb
from sys.dm_db_file_space_usage
where database_id = 2

The output on your system depends on your database setup and usage. The output of this query might appear as follows:

user_objects_kb      internal_objects_kb  version_store_kb     freespace_kb
-------------------- -------------------- -------------------- ---------------
256                  640                  0                    6208

Taking into consideration the preceding results, when configuring tempdb, the following actions need to be performed:

• Pre-allocate space for tempdb files based on the results of your testing, but leave autogrow enabled in case tempdb runs out of space to prevent SQL Server from stopping.
• Per SQL Server instance, as a rule of thumb, create one tempdb data file per CPU or processor core, all equal in size up to a maximum of eight data files.
• Make sure tempdb is in simple recovery model, which enables space recovery.
• Set autogrow to a fixed size of approximately 10 percent of the initial size of tempdb.
• Place tempdb on a fast and dedicated I/O subsystem.
• Create alerts that monitor the environment by using SQL Server Agent or Microsoft System Center Operations Manager with SQL Knowledge Pack to ensure that you track for error 1101 or 1105 (tempdb is full). This is crucial because the server stops processing if it receives those errors. Right-click SQL Server Agent in SQL Server Management Studio and fill in the dialog, as shown below. Moreover, you can monitor the following counters using Windows System Performance Monitor:
  • SQLServer:Databases:Log File(s) Size(KB): Returns the cumulative size of all the log files in the database.
  • SQLServer:Databases: Data File(s) Size(KB): Returns the cumulative size of all the data files in the database.
  • SQLServer:Databases: Log File(s) Used (KB): Returns the cumulative used size of all log files in the database. A large active portion of the log in tempdb can be a warning sign that a long transaction is preventing log cleanup.
• Use instant database file initialization. If you are not running the SQL Server  (MSSQLSERVER) Service account with admin privileges, make sure that the SE_MANAGE_VOLUME_NAME permission has been assigned to the service account. This feature can reduce a 15-minute file-initialization process to approximately 1 second for the same process.

Another great tool is the sys.dm_db_task_space_usage DMV, which provides insight into tempdb’s space consumption on a per-task basis. Keep in mind that once the task is complete, the counters reset to zero. In addition, you should monitor the per disk Avg. Sec/Read and Avg. Sec/Write as follows:

• Less than 10 milliseconds (ms) = Very good
• Between 10–20 ms = Borderline
• Between 20–50 ms = Slow, needs attention
• Greater than 50 ms = Serious IO bottleneck

If you have large tempdb usage requirements, read the Q917047, “Microsoft SQL Server I/O subsystem requirements for tempdb database” at http://support.microsoft.com/kb/917047 or look in SQL Server 2012 Books Online (BOL) for “Optimizing tempdb Performance.”

Hopefully this section has impressed upon you that in SQL Server 2012, more capacity planning is required to optimize tempdb for performance.