Post reply

Single MDF or MDF+NDF

  • December 14, 2015 at 6:54 am

    #310119

    Hi and sorry if this already exists.

    I'm looking for some info or performance benchmarks for what will grow to a large Db around the usage of a single mdf vs splitting across MDF and ndf.

    I fully understand there are many impacting factors so I need something high level. Unfortunately, the only MSDN article I can find is in relation to SQL 2005 and was published on 2007.

    Hope you can help

  • December 14, 2015 at 8:47 am

    #1845830

    Are you planning to put all the files on the same set of drives?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 14, 2015 at 11:48 am

    #1845897

    It's down to disks and controllers. If you're separating storage onto multiple physical locations (and I use that word because if you're on a SAN, using LUNs, and those are all using the same set of shared disks, they're not physically separate), then yeah, splitting the data storage up will achieve benefits. If the storage is just the same disk or disks, then, no. You won't see benefits.

    After that, you'll have to consider what it is that you're splitting up? Just general storage? That can be helpful, but usually you're going to want to isolate one type of storage from another. For example, most tables are reads, put them in one filegroup on one disk, some fo the stuff is very write heavy, put it somewhere else.

    There are tons and tons of detail that I'm glossing over, but at a high level, this is what it's all about.

    "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

  • December 14, 2015 at 1:24 pm

    #1845918

    Parallel I/O aside, it's worth mentioning that files and file groups can be backed up, restored, detached, attached separately, which can potentially decrease downtime for very large databases when performing disaster recovery or other types of maintenance operations. I'd prefer not to manage single TB sized mdf files, if the tables can be partitioned horizontally across multiple files.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • December 14, 2015 at 3:31 pm

    #1845945

    Eric M Russell (12/14/2015)

    Parallel I/O aside, it's worth mentioning that files and file groups can be backed up, restored, detached, attached separately, which can potentially decrease downtime for very large databases when performing disaster recovery or other types of maintenance operations. I'd prefer not to manage single TB sized mdf files, if the tables can be partitioned horizontally across multiple files.

    With what Eric said above, lookup "Piecemeal Restores" and then plan on what goes where very carefully.

    --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)

  • December 15, 2015 at 1:17 am

    #1846010

    Hi All and thanks for the comments.

    Please note that I'm not a DBA or storage expert so I apologise if my terms are a bit vague.

    @gilamonster Given what I have seen, I'm thinking of using multiple file groups which can be set against different mount points.

    @Grant I hadn't thought about the different workloads so thanks for bringing that up.

    @eric . The RTO considerations are one of the initial reasons I'm looking at this. From my simplistic view, several smaller files will have less impact of bandwidth than 1 large file . As I anticipate the DB to get quite large, this seemed to be a sensible option. Thank you for confirming my thinking.

    @jeff. I've had a quick look and think this will make the DBA's very happy.

    Taking all the above into consideration, for testing I'm planning on having subject orientated Schemas aligned to file, file aligned to file group.

    MDF on separate mount just with system objects.

    dbo aligned to default file & file group on separate mount.

    SchemaA fileA & file groupA on separate mount.

    SchemaB fileB & file groupB on separate mount.

  • December 15, 2015 at 2:08 am

    #1846012

    To follow up on Grant and Eric's comments.

    Splitting to spread out IO load and splitting to allow partial database restores require completely different decisions about what tables/indexes go onto different filegroups. Make sure you know why you're splitting and design appropriately and very, very carefully, otherwise you can easily end up with all the complexity and none of the benefits.

    Taking all the above into consideration, for testing I'm planning on having subject orientated Schemas aligned to file, file aligned to file group.

    You can't put objects into specific files, only into specific filegroups.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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