This post has been sitting in the draft folder for way too long, March of last year to be precise.  So although this is no longer new information, it has not been talked about all that much.  I’m talking about the new feature in SQL Server 2012 that allows you to put TempDB on local storage in a failover cluster.  That’s right.  You no longer have to house TempDB on your SAN, and why should you?

TempDB gets destroyed and re-created with each restart of the SQL Server Service so there is no need to have that happen on shared storage.  When we fail over from one node to another we don’t need the data in TempDB to still be sitting there.  So you can stop yelling at your SAN admin to “Give me more power!”  He doesn’t like you calling him Scotty anyway.

What’s so great about putting TempDB on local storage?  One acronym..SSDs.  Now we can put TempDB on local storage that is fast like SSDs or even a RAID 10 that very well may be faster than your corporate SAN.  Of course this might not even be a concern for you if your application is not a heavy user of TempDB, but if you have something that’s a big hitter for TempDB this can yield some significant gains in IO.

Let me also say that this is not a get out jail free card or a cure if you are experiencing contention in TempDB.  We’re good DBAs and we’ll make sure everything is tuned and configured properly before we go throw hardware at problems, right?  That means we need to discover if we are even experiencing TempDB contention and if we are there are some other configurations we need to look at first.  For instance, multiple TempDB data files that are equally sized.  That’s a whole other post or 2, but Robert Davis (Twitter|Blog) has done a much better job in his white paper than I ever could.  Here’s a link:

http://www.sqlsoldier.com/wp/sqlserver/demystifytempdbperformancemanagement