Post reply

Separate drives for mdf / ldf on same storage

  • 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.

  • April 14, 2015 at 5:49 am

    #1790747

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • April 14, 2015 at 5:55 am

    #1790752

    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.

  • April 14, 2015 at 6:07 am

    #1790759

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • April 14, 2015 at 6:11 am

    #1790762

    Hi Grant,

    Thanks very much, that helps a lot.

  • April 14, 2015 at 9:49 am

    #1790871

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 14, 2015 at 12:09 pm

    #1790928

    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)
    Intro to Tally Tables and Functions

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

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