Different disks for data, transaction, etc

  • I've always been told to separate database files into separate disks.

    1 or more disks for tempdb files

    1 or more disks for data files

    1 or more disks for transaction log

    1 or more disks for backups

    Nowadays,we use LUN's which are already spread out in different physical disks.

    In order to improve performance, I wonder if it is important or not to create different disks in Windows.

    I'm asking this because after advinsing one of my customers to create different disks , he told me " My disk (only one) is already spread out in different phyisical disks"

  • If everything underneath is a single pool of disks, then there's little point in creating different logical drives for different things. Maybe some space management, but not performance. Now, if they were different pools of disks underneath, it might be a different matter.

    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
  • We just recently replaced two SANs with some 3par arrays.

    Most SANs these days seem to work with the disk pooling method as it does seem to provide generally good overall performance. I did about a month of testing before putting anything prod.

    Make sure they turn OFF thin provisioning for all your SQL disks this yielded about 12-15% faster write speeds, If the array supports it ask them to optimise the LOG files and TempDB files to the outer most tracks on the disks this gave another 2-4% boost in our situation.

    Also in our situation we found that generally Raid 10 was about 5-10% faster then raid 5 despite vendors sales pitches that Raid 5 is basically as fast as Raid 10.

  • Most of the time, these days, you don't need to sweat disk placement... until you do. If you're experiencing serious IO issues, and you've worked with the SAN team to ensure the setup is correct, sometimes the best answer is to split the pools as Gail mentions and go back to old school approaches of placing different functions onto different disks (or disk pools, as long as there is true separation of both the storage and the storage access, if it's all going through the same pipe, nothing else will matter).

    "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

  • Just be careful manually configuring modern arrays like that. They aren't really designed for it and you could heavily impact availability. These arrays do a lot of Magic spreading out the data across the disks and you might end up in a situation if a shelf goes offline or multiple shelves your luns go down as well.

Viewing 5 posts - 1 through 4 (of 4 total)

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