Placing Tempdb on Solid State Drive (SSD) – Is it a good practice?

Great performance benefits for a resource starved system can come from disc system. Hence DBA or Analysts are often focused to choose appropriate disc systems while designing a database server. As we know tempdb can be a huge bottleneck for a SQL Server system involving huge I/O, it always deserves great attention.

Here I will discuss advantages and disadvantages of placing tempdb on solid state drive and then share my approach on this dilemma as conclusion.

Advantages:

1. Solid state drive is way faster than classic drives. So if you are facing tempdb bottleneck for I/O, placing tempdb on solid state drive will give you immense benefit.

Disadvantage:

1. Cost of solid state drive is still high compared to classic drives.
2. Solid state drive is not easily attachable with multiple computers (unlike SAN). So if you are planning to place tempdb on a solid state drive for a cluster, technically you can do (by tricking cluster) it but it will be a single point of failure. This means if solid state drive fails, it will force a failover. Also tricking a cluster by adding non-standard component may force Microsoft PSS not to support you in a serious condition. (Hopefully this support limitation will go away soon. I recommend referring to Microsoft’s support policy for up-to-date information on this.)
3. As of now, Solid state drive has a limitation of maximum number of write possible on it. However it is a very high number and unlikely to impact your drive (server) even if it is really busy with lots of I/O on tempdb.

Conclusion:

I prefer to place tempdb (on servers where tempdb I/O is a serious bottleneck) on solid state drive as long as I am sure that I will get huge performance benefit. However I always take below defensive steps while incorporating solid state drive the system:

1. Explain every stakeholder about the advantage of using this expensive system.  
2. If this is a clustered system, advice every stakeholder about the trade-off of accepting single point of failure in a cluster for a much better performance. Also make sure that each node of the cluster supports same drive/file path visibility so that tempdb can be rebuilt immediately if a failover occurs. This ensures SQL Cluster really does not loose its high-availability feature even if Solid state drive fails on active node.

So to conclude, it is you who needs to take a final call to place your tempdb on a solid state drive. While it sounds placing tempdb on a cluster can be little tricky, it will give you immense benefit if your system is really grinded for huge I/O from tempdb. However you must have enough business justification and planning to adopt this expensive practice for your system.