I’m watching the FedEx page as my shiny new Dell E4300 travels across country – and bummed that I won’t have it in time for the MVP Summit. The first thing I’m going to do is install a Crucial 256Gb SSD drive. I‘ve heard good things about SSD drives, but some have reported that they can get burned out from repeated constant writes. One forum post noted that an SSD drive on a notebook with insufficient RAM can cause lots of swap file paging to disk which might explain some of the dead drive issues (so I'll turn off the page file.) Which got me to thinking, how can I reduce the SQL Server writes to the disk, which got me to thinking, tempdb is a hot spot, which got me to thinking, why not put tempdb in RAM? It’s not a new idea, but it’s the first time I’ve done it, so here’s how I placed tempdb in RAM ( on my current old Dell Studio noteook). The E4300 will have 8Gb RAM so I'm planning on usign a .5Gb RAM drive for tempdb as a starting point.
Disclaimer: I’m doing this in my development notebook. If you consider this for a production machine ensure that you have enough extra RAM to hold tempdb without risking running out of RAM or disk space.
This article benchmarks several Windows RAMdisks:
I chose the DataRam RAM Disk, once configured it boots with an E drive in RAM:
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'e: \tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = ‘e:\tempdb.ldf')
Stop and restart SQL Server to move tempdb.
There’s a noticable delay the first time it’s accessed, but then SQL screams!
The RAMDrive is configured prior to SQL Server service starting and it’s worked fine with reboots.
If you have other ideas on how to improve the durability of an SSD drive, please comment...