Almost everything around us is temporary starting with your job and ending with your beer supply. You may argue that a job may last for a life time but even life is temporary. In SQL Server temporary means tempdb, a database where all ephemeral things happen. As you may know by now, SQL Server 2005 uses tempdb more than its predecessors, a lot more. The goal of this article is to tell you why.

SQL Server 2005 has far more features than previous versions of SQL Server, and yes, this is one of the reasons for increased tempdb usage. More features mean a greater need of temporary objects and obviously of tempdb space. Some of the new features that may use tempdb are Service Broker, Database Mail, CTEs (Common Table BLOCKED EXPRESSION, Event Notifications or MARS (Multiple Active Result Sets).

The second reason for an extensive use of tempdb is that some of the features, that do not use tempdb in earlier versions of SQL Server, use tempdb in SQL Server 2005. For example the deleted and inserted tables, used by AFTER triggers, are created in tempdb (in earlier versions the transaction log is used for the same purpose).

The third and the last reason I will mention here, is that some of the features that used tempdb in earlier versions of SQL Server will require more tempdb space in SQL Server 2005. For example the DBCC CHECKDB statement, will use more tempdb space due to more comprehensive logical checks, more complex structures to be checked and some new checks to be performed for the new features in SQL Server 2005.

To get a better image of features that may require tempdb disk space (and because I like lists) I will give you a list with tempdb “offenders”:

• Row versioning – row versions are held in the tempdb version store. Row versioning supports features such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers. They are also used  by databases that use read-committed snapshot isolation or snapshot isolation levels.

• Bulkload operations with triggers enabled - row versioning is used for this feature.

• Keyset-driven cursors and static cursors use work tables built in tempdb.
  Common-table-expression (CTEs) queries - work tables are created in tempdb for spool operations when CTEs queries are executed.

• Service Broker – uses tempdb for various reasons such as caching and preserving dialog context. Some of the features that rely on Service Broker like Database Mail, Event Notifications and Query Notifications can also use tempdb.

• DBCC CHECKDB statement (already mentioned) uses tempdb for work tables.

• Indexes – creating and rebuilding indexes can use tempdb when you set the SORT_IN_TEMPDB option to ON. Concurrent user modification (updates or deletes) during online index operations will require tempdb space.

• Large object (LOB) data type variables and parameters of types like varchar(max), nvarchar(max), varbinary(max) text, ntext, image, and xml and string functions operating on LOBs may use tempdb.

• Multiple Active Result Sets (MARS) can use tempdb

• Queries that contain DML statements (INSERT, UPDATE, DELETE) and use internal objects for storing intermediate results (in case of hash joins, hash aggregates, or sorting).

• Temporary tables and table variables

• Tables returned by table-valued functions

• User-defined tables and indexes

Why should you care about tempdb disk space? Because it can become one of your greatest performance pains if the disk space becomes insufficient. I will not give you the cure (the cure being beyond the purpose of this article) but I will give you some resources at the end of the article, resources that do an excellent job in teaching you how to deal with tempdb usage. However I want to mention three of the new Dynamic Management Views and Functions that can help you monitor the tempdb disk space usage:

• The sys.dm_db_file_space_usage dynamic management view allows you to monitor the disk space used for each of the tempdb files.
• The sys.dm_db_session_space_usage dynamic management view will display the page allocation or deallocation activity in tempdb at the session level. 
• The sys.dm_db_task_space_usage dynamic management view will do the same thing as the previous DMV (but at the task level).

To get a feel of how you can use them, I will give you an example:

Example:
DECLARE @i int
SET @i = 0
CREATE TABLE #T (c char(8000))

WHILE @i < 100
BEGIN
INSERT INTO #T SELECT ('Test')
SET @i = @i + 1
END

SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = @@SPID;

--Results
user_objects_alloc_page_count
-----------------------------
104

In this simple example, I’ve created a temporary table and then I used the sys.dm_db_session_space_usage dynamic management view to check the number of pages reserved for user objects in tempdb. I filtered the result for my session using the @@SPID function. You can use the same DMV to build more complex queries and get the pages reserved for internal objects or for row versioning.

I will end my article here, reminding you again that the SQL Server 2005 uses tempdb for more things than SQL Server 2000 does, so please consider this when you upgrade or build your applications using SQL Server 2005.

You have been warned!

Thank you!

Additional Resources:
Books Online – “Troubleshooting Insufficient Disk Space in tempdb” http://msdn2.microsoft.com/en-US/library/ms176029(SQL.90).aspx
Books Online – “Capacity Planning for tempdb” http://msdn2.microsoft.com/en-US/library/ms345368(SQL.90).aspx
“Troubleshooting Performance Problems in SQL Server 2005” http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#ESVAE

Published in Culminis Compass

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro