Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am going to install a new SQL server 2008 instance on my development/testing machine. My machine have one 7200rpm 500GB SATA Disk (C:OS) and one Intel X25-G2 80GB SSD(D:). Details machine config is as follow:
CPU:i7 860
RAM:8GB

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?

Data root directory :C:\Program Files\Microsoft SQL Server


User database directory D:\Data


User log directory C:\Logs


Temp DB directory D:\TempDB


Temp Log directory C:\TempDB


Backup directory C:\Backups

share|improve this question
    
How big are your databases, how much memory are you going to use and are you going to use W2K8 32 or 64 bit? –  Chopper3 Oct 25 '09 at 17:22
add comment

migrated from stackoverflow.com Oct 25 '09 at 16:34

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 1 down vote accepted

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

  1. 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.

  2. 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.

share|improve this answer
3  
>> TempDB lives in RAM, no? Only if you set it up that way... by default, TempDB is NOT in RAM. Disk IO on TEMPDB is very important for perf. –  samsmith Oct 25 '09 at 19:00
    
Plus log write speed is critical for commits / inserts. So, regardless of data - put the log files on a SSD. Tempdb data file is irrelevant unless the server overflows to it. It should have very little IO. –  TomTom Feb 14 '12 at 16:49
add comment

I just find this http://technet.microsoft.com/en-us/edge/Video/hh771099 It's a bit long but take the time to complete the video. Very important informations on SQL server and SSD disk usage.

share|improve this answer
add comment

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.

share|improve this answer
    
Does your SSD support TRIM? Your SSD seems an enterprise grade. –  Kuroro Nov 19 '10 at 3:20
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.