2

We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is our usual practice. However, a DBA has advised that this may not be the ideal configuration and that we may see better performance storing both the data and log files on the 12K HDD.

Not being a DBA, I'm quite happy to defer to an expert, but I'm not fully convinced that this configuration is the best approach. I would have thought that there would have been advantages in separating the data from the logs in terms of parallelism regardless of the specs of the HDD's.

What are your thoughts?

6

The log vs. data separation is sound advice, but the devil is always in the details. A write intensive workload that commits frequently requires the log stream to flush to disk as fast as possible. This in turn requires a sequential write pattern, as the log does, undisturbed by any other operation. Hence, isolate the data writes (and reads!) from the log writes.

But the question is: is your workload write sensitive? Is the log commit flush wait the most important performance bottleneck? You may have a tempdb sensitive workload. Or you may have a workload that is not even touching the disk. Lock contention, memory restrictions, latch contention all could be your nemesis, none would be solved by how you separate LDF and MDF. And lets not forget that the modern SQL Server instance usually hosts several databases, implying several separate log streams.

In lack of further evidence, both you and your admin are equally right/wrong. You need to measure, identify your problems, and address them accordingly. Read How to analyse SQL Server performance for general directions how to identify (and address) your bottleneck(s).

Before I go, I recommend you read:

Not saying that, w/o proper consideration of the workload and value, you should use SSDs (yes, you should, always...), but just food for thought.

1
  • 2
    Also, if there are multiple busy databases on the system and they all have their logs on the same drive, then you can't really maintain sequential writes anyway, since the disk has to cater to all of them. Apr 22 '14 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.