3

I'm speccing a new database server for a Microsoft SQL Server environment. In the past, I would create four separate RAID arrays for OS, data, transaction logs, and tempdb. Now, I am looking at the possiblity of adding a few SSDs to boost performance more. I only have the budget for two SSDs, and I want to mirror them in case one fails. So, I can only use the SSDs to replace one of my four standard arrays.

Which option would give the biggest performance improvement?

  • Use SSDs for OS
  • Use SSDs for Data
  • Use SSDs for Transaction Logs
  • Use SSDs for Tempdb

6 Answers 6

1

If you have a current system with the data/log/tempdb already partitioned, I would look at your IO statistics for each partition and base the decision on that.

I've mostly seen people using SSDs for their TEMPDBs. 2005 and on put a lot more dependence on the TEMPDB then past versions. And depending on what you are running on your SQL Server, it may be the highest utilized database (looking at you SharePoint).

But I would first start by looking at you're environment and seeing what is happening there.

HTH, Dan

3

Paul Randal has a series of SSD benchmarking:

From his conclusion the sweet spot for SSDs is intensive random IO loads, which would make them a candidate for Data or Tempdb. Which one is best, depends on your expected load specifics. I would look into sys.dm_io_virtual_file_stats and sys.dm_db_index_usage_stats on an existing server that handles the same load (or same type of load) and see where the IO pressure for MDF/NDF accumulates, and place those files on the SSDs.

LOGs don't make much sense on SSD, a typical SCSI array will outperform them on sequential writes. Neither does OS, a SQL Server host should never touch the OS files nor the paging file.

2

The one where random read and write access will be performed, and usually on a database host that is the data partition (and tempdb data). The log partition in turn is normally only accessed sequentially as most logs are circular buffers.

OS partitions backed by a SSD provide most benefit if you are planing to reboot your server quite often.

1
  • That sounds like what I expected... Is there any benefit to using the SSD for the OS drive other than reboots? Dec 13, 2010 at 18:59
2

Complicated - depends what is done.

  • Logs. Yes, they ARE sequential, but they are also flushed on every write, so the short turn times may come in nasty. Write heavy databases ARE speed limited by the log performance. It is not so much MB/S but the fast return on a 2-3 mb write that can give a boost here.
  • Data. Read heavy. Not for 2, but if you run a heavy RAID 10 there, the higher IO budget could mean you could replace the RAID 10 witha a RAID 5... and still be faster. This could be price interesting.

Anything between or not being extreme on either side - an SSD is likely wasted money.

1

It also depends on the RAID type set on your SSD drives. If it is RAID 10 than Log files and tempdb database would be better stored on SSD's since the Logs and tempdb are mostly used for write operations and RAID 10 offers the best write performance. Occasionally after Checkpoint you will see Read activity on Log files. If contrarily, it is RAID 5 than the Data files are better stored on SSD since RAID 5 is optimized more for read activity. If you have enough storage on SSD's you can also install the OS on SSD's. But the fact that really matters is to isolate Data from Log files and if possible from Tempdb as well.

Also, a very good analyze has been done by Paul Randal is this blog. I was really impressed on how he detailed his analyze by presenting a lot of possible cases.

1
  • +1 for the link to Paul's blog. Great amount of detail on SSDs in there.
    – SQL3D
    Dec 14, 2010 at 17:13
0

You could argue that having the Windows pagefile on a separate SSD mirror would assist. Having said that, the best way to improve paging performance is to have no paging. Other than that, there isn't a great deal of random IO with the boot/system volume.

1
  • 2
    Agreed, hopefully you aren't relying on the page file. If you are, you may want to look at investing in memory first.
    – SQL3D
    Dec 13, 2010 at 20:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .