Systems Engineering and RDBMS

Tempdb usage

Posted by decipherinfosys on November 30, 2008

Not many people understand how tempdb gets used in a SQL Server instance. Here are the basics –

a) Data in tempdb does not persist across service re-starts.

b) Tempdb system database inherits all the db properties from the model database just like other user databases – in the case of tempdb it happens upon the re-start of the service while in the user databases, it happens upon the creation of the user database(s).

c) Only 1 filegroup is allowed for the data file and one for the log file. You can configure the file sizes as well as autogrow but when the service re-starts, the size is re-set to the configured value.

d) Tempdb is used for a lot of things – temp tables (also table variables beyond a threshold), cursors, row versioning if you are using snapshot isolation levels, temporary user objects like work tables which store the results of sort or spools (for operations like joins, union, order by, group by etc.), rowversions generated by operations like MARS and online index operations.

e) Not all database options can be changed for tempdb for example: AutoShrink – this is always off for tempdb) – likewise there are quite a few restrictions on the kind of operations that can be performed on tempdb – you can see this list from the BOL page here.

The most challenging aspect to managing tempdb is to understand what all operations use the tempdb space. Point #d briefly covered those aspects – these are essentially objects which fall under 3 categories: User Objects, Internal Objects, and Rowversion data. There is a very good whitepaper from MSFT which covers these in detail and how you can go about monitoring the usage of your tempdb database – it’s one of the must reads if you are responsible for managing/monitoring your SQL Server instances. You can access this whitepaper here.

And regarding monitoring the space, the whitepaper has a lot of SQLs as well as a stored procedure to help you collect the data and then analyze that data. From a DMV perspective, in SQL Server 2005 (haven’t checked what all has been added in SQL Server 2008 in this regard) one can make use of this DMV:

sys.dm_db_file_space_usage

It will report the space used by tempdb and gives you the page count for all the three categories – version store, user objects and internal objects.

Some other useful posts for tempdb:

  • Changing Tempdb collation – our blog post here.
  • Understanding the new isolation levels in SQL Server 2005 – our blog post here.
  • Moving Tempdb files – out blog post here.
  • Working with tempdb – MSFT whitepaper here
  • Tempdb capacity planning and concurrency considerations – SQLCAT whitepaper here.

2 Responses to “Tempdb usage”

  1. Rob said

    I think you have a mistake in your article:
    “Not all database options can be changed for tempdb for example: AutoShrink – this is always on for tempdb”

    AutoShrink is set to off and cannot be changed.

  2. Thanks Rob – you are right. We had a typo in the post. We meant to write “Off” instead of “On”. It does make a world of a difference🙂 Thanks for pointing it out.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: