TempDB - True, False, and It Depends

Play along and see if you get the questions right....

  • TempDB is used for storing temporary user objects, such as global or local temporary tables, temporary stored procedures, table variables, or cursors. 

TRUE - This is just a warm up so we're all on the same page :)

  • TempDB is available for all users who can connect to the instance

TRUE - Anyone/Everyone can use TempDB as long as you can connect to the instance.

  • TempDB database files should exist on separate physical drives

IT DEPENDS - You'll have to get an idea as to the type of contention you're trying to alleviate to really answer this question.  For PAGEIOLATCH waits you're probably going to want to make use of additional physical disks.  There is a great script from Robert Davis here that can help you investigate your TempDB contention.

  • TempDB should be backed up

FALSE - Everything here is temporary and SQL Server does not even give you the option to backup TempDB

  • TempDB is recreated every time you restart SQL Server

TRUE - Pretty self explanitory.  One thing to note here though is that TempDB will be recreated with the initial size settings so be sure these are what you want them to be.

  • TempDB size should be 25% of your largest database

FALSE - Maybe this is true? Maybe it's false? It really does depend on what you're doing with your SQL Server instance but as a general "catch all" statement it is false.  A good way to determine your TempDB size needs is to let your TempDB autogrow for a while (1 week? 2 weeks? whatever you would consider to be a good representation of normal activity)... see where it gets to and then tack on an extra 20-25% for extra buffer.

  • TempDB should be installed using the default location (C:\)

FALSE - TempDB on your C:\ drive is just a bad idea.  From a performance standpoint you'll want your TempDB off of your system drive (OS).  From a capacity planning point of view with the potential change of growth and requirements for TempDB usage it is dangerous to have it co-existing with your Operating System as it can fill up your C:\ drive.

  • TempDB should have 1 data file per processor core

IT DEPENDS - This is a BIG one that has been debated back and forth by some of the best SQL minds in the business.  Paul Randal wrote a great series of posts about Common SQL Server Myths and Myth #12 speaks directly to this statement.  I wont try and summarize it as it is alot of information and a fantastic read that goes into great detail as to why this statement is a big generalization that shouldn't be be followed blindly.

So how did you do? Hopefully this helped reconfirm what you already know and maybe answered some questions that you might not of been sure about.

_____________________________________________________________________________________________

Colin Stasiuk (MCP, MCTS SQL 2005/2008, MCITP DBDEV, and MCITP DBA) is an accomplished Microsoft SQL Server DBA who has been working with SQL Server since 1996. He is the founder of Benchmark IT Consulting and his specialties include SQL Server Administration, Performance Tuning, Security, Best Practice / Standards, Upgrades, and Consolidation. Colin is a proud PASS member, President of EDMPASS (The Edmonton Chapter of PASS), and has recently co-authored a book on SQL Server 2008 Policy Based Management.

Follow Colin Stasiuk On Twitter

Follow Tier1OnSQL on Twitter