Spread SQL Server data files across devices to improve performance?

Question

Would it improve performance of a database in SQL Server if I place the .mdf (rows data) and .ldf (log) files on separate sets of raided disks, as opposed to having both files on the same set of raided disks?

Answer 1

All performance solutions are based on measurements. As the theory goes, SQL Server requires the transactions to flush to durable storage (disk) before acknowledging commit (WAL). This makes the log write throughput critical for high write loads. Separating the LDF file on a separate physical disk allows the log writes to achieve serial streaming access to disk, which is the fastest way a spinning disk can write (~100-150Mb/s). Having the MDFs on the same physical disk introduces random IO patterns that dramatically reduce the throughput (~10-40Mb/s). This is the background of the separate LDF /MDF disk recommendations. The point is, obviously, mute on SSD storage.

However, to improve the server performance you need to identify the bottleneck and address it appropriately. If your bottleneck is poor indexing, lock contention, CPU, small buffer pool or any other of many more possible causes then separating the LDF and MDF will help nothing. So you should first find the bottlenecks, and I recommend following the Waits and Queues methodology. Once you have measurements that prove a bottleneck that causes performance degradation then and only then you can act appropriately.

Answer 2

Short answer: yes for write heavy loads, but the difference is not always as significant as people expect.

You are most likely to see measurable improvement during periods of high write activity, as updates/inserts result in changes to both the transaction log and the data files so if they are on the same physical disks there will be more head movements to services these write operations. SQL Server does try to bunch writes to the log and data files though, so you might not see any difference for some write loads.

We keep out log files on separate volumes because for some load patterns our application sees it does make quite a difference - but you should run tests to see if it is applicable to your application. You may find the difference so small as to not be worth bothering, and you might find other tricks (partitioning the data over the multiple volumes perhaps) give you better results.

Answer 3

Short answer is yes. The bigger and busier the database the more this helps.

Very simplified explanation is this: you have .mdf file that sql server service reads and it writes to and you have .ldf file that is in most cases only used to write data.

This means you have read/write for MDF and mostly write for LDF and the fact that hard drives can only do one operation at a time.

In reality this is more complex but this is oversimplified reason on why this helps.

Here are two good articles on this topic

http://technet.microsoft.com/en-us/library/cc966534.aspx

http://www.codeproject.com/Articles/43629/Top-10-steps-to-optimize-data-access-in-SQL-Server

Answer 4

Well, it helps with I/O performance, although you won’t see a huge performance increase. That is the real problem with Relational Databases. That's why there are No-SQL databases (MongoDb) that address the performance issues