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 😉
Best Answer
It's not just the rate, it's the wait. Benchmark properly. Check the IOPS, plus the disk queue length. Use Perfmon and SQL profiling. Go ahead - I'll wait.
You already know that the OS should be on one set of spindles, MDFs another, LDFs another, and tempdb files yet another, if you do have actual performance concerns. If you can't commit to doing that, benchmark it and find out your priorities. Also, the different read and write patterns can dictate different RAID levels for each of those.
You may find out that standard disks with the right RAID configs can get you where you need to be, and not plump down for enterprise SSD. Although, if tempdb is getting hammered enough, a single SSD might be a good fit for it. Probably no need for RAID for performance, although for redundancy it might be a good idea. Depends on your budget and how long you can be down, of course.
You also know that the SQL server should be separate from the web server, right? If performance is a concern? Even if you're not having a problem now, if you grow, you'll have a difficult time determining which is being hammered harder and what the appropriate fix is.