1

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

Kuroro
  • 115
  • 5
  • 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

4 Answers4

1

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.

quidam
  • 11
  • 1
1

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.

John Rose
  • 276
  • 1
  • 8
  • 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. – Jonesome Reinstate Monica 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
0

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

0

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.

Steve Radich-BitShop.com
  • 573
  • 3
  • 7