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.
- 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.
- Cost of solid state drive is still high compared to classic drives.
- 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 PSSnot 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.)
- 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.
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:
- Explain every stakeholder about the advantage of using this expensive system.
- 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.