Sql-server – SQL Server 2008 data directiories in SSD

sql serversql-server-2008ssd

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

Best Answer

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.

Related Topic