Should data and log files be on separate physical disks?

Question

Post reply

Should data and log files be on separate physical disks?

Suresh B.

SSC-Insane

Points: 22986
More actions
May 11, 2009 at 6:01 am

#209166

There are 20 databases. MDF and LDF files are on same physical disk. I think moving them (to different physical disks) may not improve the performance.
Please share your experience/openion about this.
Thanks

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

More info on this please?

On what physical disks have yuo got them?

Yes seperation them to 2 different physical disks does improve performance . Ideally you should be keep them in RAID1/RAID10 if the costs permits.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Usually physically separating your Log files from the data files does improve performance. However, it is possible, if your IO load is very low, that you will not notice any difference.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Suresh B. (5/11/2009)
MDF and LDF files are on same physical disk. I think moving them (to different physical disks) may not improve the performance.

Your statement contradicts what Microsoft documentation, common sense and experience say. I/O load balancing is a pretty well proven tool to attain better performance.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

There is no doubt that separating your logs and data files is a best practice. However, if you want to know if you are having bottlenecking/performance issues from not having them separated, look at your Wait Stats information and see if you have high wait_time_ms for the WRITELOG and LOGBUFFER wait_types:

select * from sys.dm_os_wait_stats

where wait_type IN ('WRITELOG', 'LOGBUFFER')

If you do, then you probably would do better to separate your logs from your data files. If your workload is small enough, then it probably won't be a noticeable difference. The only way to know for sure is to monitor your wait stats.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

PaulB (5/11/2009)
Suresh B. (5/11/2009)
MDF and LDF files are on same physical disk. I think moving them (to different physical disks) may not improve the performance.
Your statement contradicts what Microsoft documentation, common sense and experience say. I/O load balancing is a pretty well proven tool to attain better performance.

It's actually not quite so common sense as one might think. It is documented pretty well, but the argument could be made that if it was so important, why would the default configuration be Logs and Data files on the same disk, and worse yet with tempdb as well. For a lot of workloads that I have seen, this configuration works just fine, but it doesn't make it perfect or ideal. Depending on a number of factors, it may not make a difference, or it might be a day and night difference. The answer to these kinds of questions is almost always "It Depends."

I have learned in consulting that you need to demonstrate the need before throwing what a "best practice" might be out, otherwise you might get thrown out. A lot of those papers target high end environments and don't necessarily fit the requirements of smaller implementations that can get by without problem on a different single RAID disk configuration. The power is in the numbers.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

Jonathan Kehayias (5/11/2009)
There is no doubt that separating your logs and data files is a best practice. However, if you want to know if you are having bottlenecking/performance issues from not having them separated, look at your Wait Stats information and see if you have high wait_time_ms for the WRITELOG and LOGBUFFER wait_types:
select * from sys.dm_os_wait_stats
where wait_type IN ('WRITELOG', 'LOGBUFFER')
If you do, then you probably would do better to separate your logs from your data files. If your workload is small enough, then it probably won't be a noticeable difference. The only way to know for sure is to monitor your wait stats.

Thanks Jonathan,

I run the above query. The wait_time_ms is not much higher than our other servers.

As suggested by you I will monitor it.

Regards,

Suresh

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Suresh,

Glad it could help. If you have the available means, moving logs separate from data is definately a thing to do. If it means laying out more money for hardware, be sure that you are going to get your bang for your buck before doing so. It is definitely a worthwhile thing to do, but if you don't have to absolutely do it, and there is a cost associated, then it may not be worth doing.

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 8

There really is only one answer to this and that is ALWAYS. And not just a software partition but a separate device altogether. Even if it's a small system and IO is not an issue, think about that single point of failure. The single drive it is all sitting on ! I hope you have a good backup schedule.

Code On

😛

ondrej.bouda SSC Enthusiast Points: 164 More actions · Answer 9

Malcolm Daughtree (5/13/2009)
There really is only one answer to this and that is ALWAYS ... Even if it's a small system and IO is not an issue, think about that single point of failure. The single drive it is all sitting on !

Well, two drives have two times higher chance for a failure... 🙂

So, having data files on one drive and log files on another drive is definately not the solution - if any of those disks fails, your server is down...

In a two drives enviroment, I'd rather use the second drive for backups only (may be RAID 1), at least until the server performance is sufficient.

Ted Krueger Ten Centuries Points: 1171 More actions · Answer 10

Well, two drives have two times higher chance for a failure... 🙂
So, having data files on one drive and log files on another drive is definately not the solution - if any of those disks fails, your server is down...

I think you didn't understand the point and you're kind of out there on a limb in being close to accurate. Yes, if you have 2 physical drives you're sitting on a time bomb. That's why the intelligent people that invent4ed the disk also gave us so many RAID levels to choose from in order to prevent one or multiple failed disks from actually hurting or losing our valuable data.

Also agree that ldf and mdf files should never be no the same spindles. This is kind of database server build 101. The other key factor is the means in which the controls get to the data in kits like multiple internal cabling kits. Example is having your OS drive running over a separate SAS cable than your data and logs etc... This goes in-depth on servers that meet database server requirements and configurations, but the point it, no log file that is in full recovery mode with a high rate of update transactions should be sitting and fighting with your data files. Your baselines will be way off if they are and you’ll never be able to scale anywhere with the server. This will cost the company in business and money in the long run.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

ondrej.bouda (5/14/2009)
Malcolm Daughtree (5/13/2009)
There really is only one answer to this and that is ALWAYS ... Even if it's a small system and IO is not an issue, think about that single point of failure. The single drive it is all sitting on !
Well, two drives have two times higher chance for a failure... 🙂
So, having data files on one drive and log files on another drive is definately not the solution - if any of those disks fails, your server is down...
In a two drives enviroment, I'd rather use the second drive for backups only (may be RAID 1), at least until the server performance is sufficient.

Yes, your MTBF (mean time between failure) is higher when you are distribute your files like this, however your MTBUL (mean time between unrecoverable loss) is much lower. One of these is more important than the other. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ondrej.bouda SSC Enthusiast Points: 164 More actions · Answer 12

Ted Krueger:

I still think that two drives are no good configuration: you cannot have RAID on two drives while having mdf and ldf on separate spindles.

It is clear that separating mdf, ldf and temp is one the basic ways of increasing server performance.

Anyway, in the most common scenario, the server is under rather light workload and one drive is fully sufficient so there is no need to spend for RAID as it would have no real impact on perfomance.

Ted Krueger Ten Centuries Points: 1171 More actions · Answer 13

Disk it far cheaper than poorly configured hardware 🙂

ondrej.bouda SSC Enthusiast Points: 164 More actions · Answer 14

Hey, what's wrong?

Consider a small firm: Could you please explain me why I should make my customer spend money for more disks while both 🙂 users are content with the app performance?

What value will my customer gain for that money? What would YOU tell him?