A while back, my young son thought it would be fun to use a CD as a virtual surfboard on our kitchen floor. While he was a very proud boy, all I could think of was how much that CD cost and how unlikely it was that we were going to ever be able to listen to it again.

As my kids got older, I found significant value in exercising my right to make a copy my children’s CDs for personal use. It became part of the process of buying a new CD to copy it and put the original in a safe place – far away from little hands. If the CD copy gets scratched or otherwise destroyed, it is as simple as pulling out the original and making another copy.

We use these copies extensively and, while we encourage treating them properly, life happens. They get left outside, in the car, under the couch, you get the picture. Similarly, we use TempDB extensively, and ever more so as more features of SQL Server use TempDB internally.

In SQL 2008R2 and older, TempDB had to be on a shared disk when implementing SQL Server in a cluster.

This may not seem like a big deal, but think back to the CD analogy. Our user databases are like the original CDs. We need to keep them protected and safe from the children. We keep the databases spread across redundant disks for the highest resiliency. We configure security to be certain that only those who are responsible enough can touch the databases. TempDB is like the copies. Anyone can use it and it gets used all the time by both users and by the system. We have little concern and generally little control over how TempDB gets used.

With the shared disk requirement for TempDB, we have to treat our CD copies in a similar fashion as the originals (the user databases). The data is redundantly protected and, in several cases, spread across the same disks as the user data. Getting the performance necessary out of TempDB in some cases was difficult.

In SQL 2012, this has been changed. A very useful feature of SQL 2012 is the ability to put TempDB on a local disk, even when implementing SQL Server on a cluster. This prevents the use of expensive SAN storage for something temporary and volatile. It also introduces the ability to significantly increase performance of some key functionality of SQL Server (Snapshot Isolation, sorts, hash, CheckDB, etc.) by opening up more high-performance options in the local box. SAN based SSD is possible for shared storage, however, enterprise class shared SSD is still incredibly expensive. Local SSD is not as cheap as spinning disk, but is cheaper, and in some cases, faster than SAN based SSD.

Moving TempDB local is not the ultimate solution for all environments and the significance of this feature may not be universal. However, it is another very useful tool in the DBA toolbox that has the potential to reduce the cost of and increase the performance of a SQL Server deployment.

Links to Microsoft concerning this new feature:
http://msdn.microsoft.com/en-us/library/bb500459%28v=SQL.110%29.aspx
http://msdn.microsoft.com/en-us/library/ms143506.aspx#storagetypes

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter