Post reply

Splitting DBs into multiple files for IO spread

  • January 12, 2014 at 2:50 pm

    #1679472

    Now now you two, no fighting 😀

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 13, 2014 at 7:05 am

    #1679570

    Perry Whittle (1/12/2014)

    Now now you two, no fighting 😀

    Ah...no... no fighting. I was just curious.

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

  • January 13, 2014 at 8:57 pm

    #1679772

    Perry Whittle (8/13/2012)

    george sibbald (7/10/2009)

    sounds to me like the basic problem is the fact the sql server i/o is shared with i/o from other types of servers. the i/o patterns for SQL are radically different to say a file server and the two should not go through the same i/o channels.

    you would get the biggest bang for your buck by seperating them out. that would probably give you improvements in SQL performance as well. I don't think adding more luns if the i/o is still mixed is the best option.

    +1

    i dont think more LUNs are the answer.

    I'm with george and perry on this. After setting up a fairly large virtualized environment we ended up with sqldata, sqltempdb, sqllogs all on their own storage groups (multiple sql servers). We also had a storage group that was used for pretty much everything else.

    I have a question to add to this...would separating a database into multiple files on the same storage group have much of an impact? (Not considering SAN caching capabilities like EMCs fast cache)

    Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale

  • January 14, 2014 at 7:29 am

    #1679894

    GilaMonster (1/12/2014)

    Jeff Moden (1/12/2014)

    GilaMonster (1/12/2014)

    Please note: 4 year old thread (bumped by a spam post)

    Why does that matter to you so much?

    So that people who don't check the dates don't spend large chunks of time (as I have done several times) writing out questions and requests for clarification for a problem likely long-solved. If it bothers you, ignore the note.

    Thanks Gail. Nah. Doesn't bother me. I've always thought that if an improved answer is posted, it doesn't matter how old a post is because it might help someone else. I've been known to resurrect very old posts simply because there's a better answer to be had for those that may run into it during their search for a solution to a given problem.

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

  • January 15, 2014 at 6:40 am

    #1680240

    sql4gnt (1/13/2014)

    I have a question to add to this...would separating a database into multiple files on the same storage group have much of an impact? (Not considering SAN caching capabilities like EMCs fast cache)

    Are the files part of the same database filegroup (PRIMARY for instance)?

    Once you start adding multiple files to a filegroup (a technique used for tempdb to overcome allocation contention) SQL server has to start performing extra ops against those files to track the free space and increase the file sizes. SQL Server applies a proportional fill method and the more files you add the more SQL server has to file switch. This will negate any performance you gained (if any) and cause the opposite reaction, sloooooowwwwwwwwww!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 15, 2014 at 8:19 am

    #1680294

    There is another possibility: Use the four LUNS to create a single volume on the server for the database datafiles.

    The data would be spread across the four LUNS automatically by the OS when you create files there, and there wouldn’t be the problem of trying to figure out where the hot data spots are.

  • January 15, 2014 at 11:27 am

    #1680383

    Michael Valentine Jones (1/15/2014)

    There is another possibility: Use the four LUNS to create a single volume on the server for the database datafiles.

    The data would be spread across the four LUNS automatically by the OS when you create files there, and there wouldn’t be the problem of trying to figure out where the hot data spots are.

    That's a really bad idea. To do this requires creating dynamic volumes in windows and then striping a volume across the 4 disks. You're creating a software RAID across LUNS that are already presented from a hardware array (s). The big problem which no one seems to be grasping is that if all the LUNS don't have the same response time you'll get I\O slowdown as the others will have to wait!

    Keep it simple!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 7 posts - 16 through 21 (of 21 total)

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