Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Jun 26 '13 at 8:58

This question came from our site for computer enthusiasts and power users.

    
As a sidenote: I hope you have some kind of clustering or another replica of your server running in sync.By keeping logs and data on the same RAID volume you would not be able to recover to the point in time of the failure. –  Greg Robson Jun 28 '13 at 12:40
add comment

4 Answers 4

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.

share|improve this answer
1  
Good answer. I just want to underscore for the OP that this applies to a single database. If there are log files from multiple active databases, that's random access, not sequential. –  Jon Seigel Jun 26 '13 at 13:41
    
+1 @RemusRusanu Excellent explanation ! –  Kin Jun 26 '13 at 14:28
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
To clarify: traffic to data files is mostly random reads with burst writing during checkpoint operations; traffic to log files is mostly sequential writes. Imagine the work that one disk read/write head has to do to satisfy both operations. So, general practice is data and log files on different spindles. However, as several posters here point out, this is "general" advice - YMMV. –  Greenstone Walker Jul 1 '13 at 4:29
add comment

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

share|improve this answer
4  
-1 Not even wrong. –  Remus Rusanu Jun 26 '13 at 9:08
    
And ignorant. Separating data and logs is baseline performance boosting. –  TomTom Jun 26 '13 at 9:41
1  
"You won't see a huge performance increase" - rot. "You might not see a huge performance increase" - that's a sensible answer. The real answer is, as is so often the case, "it depends". –  Thomas Rushton Jun 26 '13 at 10:25
    
So spreading files across drives is a real problem with relational databases? Can you elaborate please? –  John Alan Jun 26 '13 at 11:41
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.