Separate drives for mdf / ldf on same storage

Question

Separate drives for mdf / ldf on same storage

Steve Smith-163358

SSCommitted

Points: 1750
More actions
April 14, 2015 at 5:04 am

#296884

Hi,
I've been trying to read articles on this, but the information often seems conflicting.
I'm aware that it's best practice to separate mdf and ldf files onto separate drives.
However, I see a lot of servers where the underlying disk array is the same for drives on the server.
Is there still any performance benefit to separating mdf and ldf files in this situation?
For example, a single virtual server running SQL Server, with multiple drives attached.
All of the drives are connected to a shared storage via iSCSI.
There drives C:\, D:\, E:\ etc are all actually sharing the same underlying disks.
Obviously, there are some benefits from an administration perspective whereby individual drives can be reconfigured without affecting the others.
However, I'm more concerned about the performance aspect.
Thank you for any help you can offer.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 1

With modern SAN "drives" actually consisting of some number of shared drives, the whole idea of splitting your storage between data and logs gets kind of weird. For most systems, just letting the SAN manage storage, assuming it's set up appropriately, is fine. If you're looking at a very high degree of I/O, you are going to want to work with the SAN team to rearrange the storage such that you do get isolation of the data and the logs. I'd also toss in isolating tempdb from the others as well.

But, I'd still only do all that if I thought that it was likely that I was going to be facing serious I/O issues. It's a ton of work and a lot of added maintenance for the SAN team, so I'd really want to be able to justify it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Smith-163358 SSCommitted Points: 1750 More actions · Answer 2

Hi Grant,

Thanks for the reply.

The majority of systems that I see are very small, with minimal IO.

A few have SAN's, most others use internal storage, or even NAS for storage.

I'm assuming though that the principal remains the same whether it's a local SQL Server installation on a client PC running on a single drive logically partitoned, or a high spec SQL Server with a SAN.

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 3

Yeah, it's all largely still applicable. Having more than one disk helps I/O. Further, having more than one file/filegroup helps too. You do have to take into account the number of controllers you have as well though. You might have 6 disks, but if they all go through a single pipe, guess where the bottleneck is now? But yeah, the logic never quite goes away, regardless of scale or technology. Now, some of the new SSD array systems like PURE or Violin, the rules seem to be different there. All the other technology I've worked with or heard about, you still want to explore that split in storage.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Smith-163358 SSCommitted Points: 1750 More actions · Answer 4

Steve Smith-163358

SSCommitted

Points: 1750

April 14, 2015 at 6:11 am

#1790762

Hi Grant,

Thanks very much, that helps a lot.

ScottPletcher SSC Guru Points: 101073 More actions · Answer 5

There used to be both recoverability and performance reasons for the split. Now it's more about recoverability than performance.

If possible, you want the data and logs on separately "fail-able" systems. That is, if possible, you want at least two disk drives/LUNs/partitions/whatever that when one fails the other doesn't. Then, if you have data and logs on separate ones, you can always recover the db.

Btw, even then, you can mix data and logs, just not for the same db. For example, driver-set-1 may contain DbA data and DbB log while driver-set-2 contains DbB data and DbA log. That is fine. What you don't want is DbA data and DbA log on the same fail-able disk resource if possible.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 6

Shifting gears a bit, I really miss HS-LASD (Hot Swappable Large Arrays of Small Disks) with a really good controller. Not only could you split MDF/LDF and TempDB, you could easily make multiple file groups on individual disk(s) for some nasty fast IO. Having these all-in-one Sans is fine for NetOps ease of setup but they seem to go cross-eyed when you want to assign certain disks to certain luns. Even then, with the size of hard disks nowadays, it seems an incredible waste of space to allocate an entire Terabyte disk to a 50GB "T:" drive for TempDB. I miss multiple racks of HS 100GB drives.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)