Microsoft said I have an option to place following directories in different disk. So I plan to place User database & Temp DB on SSD and rest of it on traditional disk. Is it a good choice for gaining a performance boost in fast SSD?

4 Answers
4

So this is your personal machine and not a server? I'd put all the database files on the SSD.

SQL actually writes changes to the data files infrequently. Changes are written to the transaction log immediately and then written out to the data file by lazy writer at some point in the future when the IO subsystem isn't busy. So it's usually not trying to write to both the transaction log and the data files at the same time. This is by design.

TempDB lives in RAM, no? There's a physical backing file but my understanding is that basically SQL caches this in RAM ahead of everything else.

The classical situation where you'll gain performance by putting the transaction log on a separate disk is when you have a fairly even write/read mix and you don't have enough RAM for SQL Server to serve those reads from the pages cached in RAM, forcing it to read those pages from disk. Then you get disk contention if both the data file and the transaction logs live on the same physical disk.

I find it difficult to believe you'll encounter such a situation on a single-user workstation, though. The one exception might be if you have a database too big to fit into your workstation's RAM and you're doing some kind of big, complex data import that involves lots of reads in addition to the writes.

Those Intel SSDs are just fantastic for database work, though. Good purchasing decision.

Realize SSDs slow down with wear, we have a production sql server with 1TB SSD storage and after 6 months the SSDs are removed and back to SAS 15k drives, the performance is significantly better with the HDD than SSD AFTER USAGE - When they were brand new there was no comparison at all - SSD blew away HDD performance in all benchmarks, but after 6 months they are slow. I haven't had time to write up more details yet but will try to share several benchmarks when I have some free time.

If you have the room, put all DB files on the SSD drive, as previously suggested.

One point of clarification: To clear up an urban legend of sorts, tempDB is usually on disk, not in memory. You can force it run from memory, but usually you are better off not.
http://support.microsoft.com/kb/115050