I’m in the midst of specing out a new server and I know that it will have a SSD drive in it on which the operating system and key applications will sit. The idea came up that this is where I should also put my TempDB. Lightening performance!!!! Right?
After all the literature reports:
with striping and short stroking of 10 high-end 15,000rpm hard disk drives performance gains of more than 16× are attainable, resulting in more than 3,000 random reads per second. [*]
I am coming to the conclusion that this could be a dumb idea if randomly implemented. There are two main types of SSD Drives:
- single-level cell (SLC) NAND memory – typical enterprise.
- multi-level cell (MLC) NAND flash memory – typical consumer, cheaper, slower, as much as 10 times fewer read/writes over its life span
So I looked at the specification of one MLC drive, the Super Talent MasterDrive PX Series 64GB 2.5in SATA II Solid State Drive, SLC NAND Flash and read:
- Read: Unlimited
- Write: 350.7 yrs yrs @ 50GB write-erase/day
So for OS and other stuff – it is sweet. For a TempDB for SQL Server it is definitely not industrial strength – 500 GB of writes/hr into tempDB is not unreasonable so the life expectancy is down to < 2 years. In fact, digging deeper I read:
one SSD drive has specifications of 130 random writes per second and 18,000 random reads per second, enabling the drive only to maintain a random write rate less than 1% of its read rate. In contrast, hard disk drives can typically maintain write rates close to 90% of their read rates.[*]
Bottom line is clear: SSD drives are NOT for TempDB. The 16x performance gain could be reduced to 1% x 16 = 0.16 performance gain or SIX TIMES worst performance… Of course, you may find a SSD drive that could work… but you want at least 16% write rate versus read rate.
The resulting configuration that I am planning:
- Core i7 920/930
- SSD Drive – likely the Super Talent cited above for OS and key applications only
- 12 Gigs Ram (to start)
- Raided SATA drives