I may be on vacation, but the alphabet goes on. We’re up to the letter T and that stands for tapas, toreadors, and tempdb!
Tempdb is a system database with one very special characteristic: it gets recreated every time SQL Server starts up.
What is tempdb used for?
The first use for tempdb is user created temporary objects, such as global or local temp tables, table variables. You can even create stored procedures in tempdb. SQL Server also uses tempdb for storing intermediate results for internal processes such as sorting. If you’re using row-versioning in your database, the various row versions generated by statements like UPDATE and DELETE are stored in tempdb.
Tempdb physical structure
Tempdb has datafiles and logfiles just like any user database. By default, it’s created with one of each, but you can add additional data and log files as you deem necessary. The old rule of thumb was that you should have one datafile per processor core. This is not a hard and fast rule anymore. Some systems can get away with one datafile in tempdb, others need somewhere along the lines of 1/4 or 1/2 the number of processors. And yes, some may need as many datafiles as processors. How many you need depends on how your tempdb is being used and whether you’re seeing contention for tempdb in the form of PAGELATCH waits. I’ve included links to some great resources for more information on this at the end of this post.
One thing you do want to do is be sure to size tempdb properly. Yes, tempdb can autogrow if needed, but just like your user databases, these filegrowths take time and resources and you want to avoid them if possible. Don’t know how big your tempdb should be? After your system has been up and running for a while, take a look at how big tempdb is. At minimum, that’s what you should set the initial size to.
Can I move tempdb?
Absolutely. And it’s even easier than moving other databases in that you don’t even need to move the physical files. Because tempdb is recreated on startup, moving it is just a matter of issuing an ALTER DATABASE statement and specifying a new location for the data and/or log files. When SQL Server is restarted it will create tempdb in the new location.
What about the collation?
Tempdb uses the server collation and you can’t change that. Objects created in tempdb use that same server collation. This can wreak havoc on your application if your database uses a different collation. For example, I’ve created a database using a case-sensitive collation.
select name, collation_name from sys.databases
where name in ('tempdb', 'CollationDB')
I then create a temp table and try to join it with a table in my CollationDB:
USE CollationDB GO CREATE TABLE #tmpCustomersCI ( LastName varchar(50)); GO INSERT INTO #tmpCustomersCI SELECT LastName from AdventureWorks.Person.Contact; GO SELECT * from Customers c JOIN #tmpCustomersCI tc ON c.LastName = tc.LastName
What I get is an error that may look very familiar to you:
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CS_AI” in the equal to operation.
So what do I do? Well I can either add the COLLATE clause to my SELECT statement, or I can tell SQL Server to use the user database’s collation when I create the temp table:
USE CollationDB GO CREATE TABLE #tmpCustomersCS ( LastName varchar(50) COLLATE database_default); GO INSERT INTO #tmpCustomersCS SELECT LastName from AdventureWorks.Person.Contact; GO SELECT * from Customers c JOIN #tmpCustomersCS tc ON c.LastName = tc.LastName
And now I get the results I expect.
Further Reading
As promised, here are some awesome resources for more info on tempdb.
Paul Randal debunks the myth about one datafile per core.
Kimberly Tripp talks about tempdb collation and what goes in tempdb.
And for some basic information on tempdb and its properties and restrictions, check out BOL.