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