A system we are developing consists of a Web app frontend, and a backend that does a lot of data processing using stored procedures in SQL Server 2008 R2 (please, don't ask why...). These stored procedures make heavy use of temp tables (creation, insertions, joins), so that tempdb i/o rate is high in writes and reads. Our clients need speed, so we are about to recommend the following:
- Buy a server with a RAID 1 SSD array for storing the main database (maybe RAID10 if they have the money), using another hard drive for the OS and SQL Server installation, so that vital data is stored with replication in a fast drive, and 64 GB RAM.
- Use a Ramdisk for storing the tempdb database, so temp tables (the biggest performance bottleneck, we think) are processed in RAM.
Some context data:
- Our database uses no more than 10 GB, with a very low expected growth rate. Tempdb usually grows up to no more than 2-3 GB.
- The server will be used for the DB and the Web Server.
- The Ramdisk software can mount the ramdisk at windows startup.
We have tested the ramdisk approach in a laptop with a lot of ram. The speedup is remarkable (stored procedure execution times reduced to 1/3) at least.
I need help to determine whether this is a good solution or not, and to detect any flaws (obvious or less obvious) that I might be missing.
EDIT: Thanks for the answers so far! I forgot to mention explicitely that there will be concurrent users using the application, so there will be multiple temp table operations running. Also, Mixing web server and DB server is not our choice, we already know it's not optimal ;)