can I put more than one database per disk?

  • Actually I have one database
    disk C : OS
    disk D:  tempDB
    disk E : MDF fie
    disk F : Ldf file
    disk G :  Backup disk

    now I have to create two new databases
    should I put them on new disks or use the disks above and put all the MDF files on disk E and all the LDF files on disk F?

  • You can use the same disks.  If the databases are heavily used or performance is crucial, you might want to keep them separate, though.

    John

  • dubem1-878067 - Tuesday, November 14, 2017 5:47 AM

    Actually I have one database
    disk C : OS
    disk D:  tempDB
    disk E : MDF fie
    disk F : Ldf file
    disk G :  Backup disk

    now I have to create two new databases
    should I put them on new disks or use the disks above and put all the MDF files on disk E and all the LDF files on disk F?

    Further to John's comment, I'd use the same storage structure for the new databases as for the existing one, to keep things consistent, unless you are forced to do otherwise for performance reasons.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin - Tuesday, November 14, 2017 6:23 AM

    dubem1-878067 - Tuesday, November 14, 2017 5:47 AM

    Actually I have one database
    disk C : OS
    disk D:  tempDB
    disk E : MDF fie
    disk F : Ldf file
    disk G :  Backup disk

    now I have to create two new databases
    should I put them on new disks or use the disks above and put all the MDF files on disk E and all the LDF files on disk F?

    Further to John's comment, I'd use the same storage structure for the new databases as for the existing one, to keep things consistent, unless you are forced to do otherwise for performance reasons.

    Also remember that, on a SAN, what you're calling "separate disks", probably aren't different PHYSICAL disks.  There is no performance advantage unless you can guarantee that the disks are actual physically separated disks.

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

  • If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.

  • Blake McNeill - Tuesday, November 14, 2017 7:10 AM

    If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.

    Again, not likely to happen on a SAN even though you have different Drive Letters.  The same "spindles" are frequently used across multiple Drive Letters.  Check with your SAN administrator and find out.

    As a bit of a sidebar, large or heavily used database benefit much more from writing good code rather than any hardware tricks.  Good code can typically improve performance by 60 to 100X whereas the sum of all hardware tricks might get you a 2X gain and only if you're very, very lucky.  Even if you go the route of MPP (Massively Parallel Processing), which also requires some code rewrites to support it, the MPP appliance vendor will usually only claim "UP TO 30X" and won't guarantee it.

    Although hardware and hardware tricks do help, it's almost trivial compared to what you do with code.  Bad code will still run poorly even on good hardware,  Performance lives in the CODE.  Spend more time and money there than on hardware tricks that don't end buying you much.  If you feel compelled to update your hardware, buy as much memory as the system will hold and the fastest disks you can lay your hands on for the transaction log files.  Just remember that will only give you some performance relief and generally won't help as much as writing correct code for properly designed databases.

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

  • Jeff Moden - Tuesday, November 14, 2017 8:29 AM

    Blake McNeill - Tuesday, November 14, 2017 7:10 AM

    If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.

    Again, not likely to happen on a SAN even though you have different Drive Letters.  The same "spindles" are frequently used across multiple Drive Letters.  Check with your SAN administrator and find out.

    As a bit of a sidebar, large or heavily used database benefit much more from writing good code rather than any hardware tricks.  Good code can typically improve performance by 60 to 100X whereas the sum of all hardware tricks might get you a 2X gain and only if you're very, very lucky.

    We try to work with our SAN admins to make sure that we do indeed have a separate disk although we do get push back from time to time. And I completely agree with you about good code. Life would be much easier if all code was good code, but that would be an unrealistic expectation.

  • Blake McNeill - Tuesday, November 14, 2017 8:41 AM

    Jeff Moden - Tuesday, November 14, 2017 8:29 AM

    Blake McNeill - Tuesday, November 14, 2017 7:10 AM

    If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.

    Again, not likely to happen on a SAN even though you have different Drive Letters.  The same "spindles" are frequently used across multiple Drive Letters.  Check with your SAN administrator and find out.

    As a bit of a sidebar, large or heavily used database benefit much more from writing good code rather than any hardware tricks.  Good code can typically improve performance by 60 to 100X whereas the sum of all hardware tricks might get you a 2X gain and only if you're very, very lucky.

    We try to work with our SAN admins to make sure that we do indeed have a separate disk although we do get push back from time to time. And I completely agree with you about good code. Life would be much easier if all code was good code, but that would be an unrealistic expectation.

    That's a bit fatalistic. 😉  It's not unreasonable at all to expect good code and, if you do it properly and with your "I'm a friendly DBA" hat on, you can instill some pride in the Developers by demonstrating some fixes without the implication that they're not good programmers or other inflammatory position.  I actually sit with the Developers in my company and, after working WITH them in such a fashion, they now recognize and repair performance issues in legacy code, write some damned fine high performance new code, and aren't afraid to ask when there's an issue that they are having difficulty with.

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

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

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