Play along and see if you get the questions right....
TRUE - This is just a warm up so we're all on the same page :)
TRUE - Anyone/Everyone can use TempDB as long as you can connect to the instance.
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.
FALSE - Everything here is temporary and SQL Server does not even give you the option to backup TempDB
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.
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.