Post reply

separate large table onto separate physical disk?

  • September 6, 2007 at 10:35 am

    #180943

    Hi,

    We have a Data Warehouse we are moving to a Clustered SQL 2005 Setup on a SAN. My question is related to file/filegroups. We have 2 particularly large fact tables (100 million rows) which will continue to grow. We are planning on a monthly partitioning scheme.

    My question is: Is there any performance benefit to placing either or both of these tables on there on separate filegroup and/or separate physical disk related to Query performance?

    I appreciate any advice, as I have never planned for this. Thanks.

  • September 6, 2007 at 10:51 am

    #731925

    Well, I've heard two sides to this. On one hand, if everything's on the SAN anyway, you've already got a certain number of disks working for you. However I've seen situations where SAN administrators were able to partition certain drives to create a logical disk with several dedicated physical disks. This could have some performance gains as you are then able to move those tables to those disks and you know they won't be competing for disk resources with other objects. If you can get those dedicated disks, then you might try experimenting with it to see if you can get some performance gains.

  • September 7, 2007 at 11:14 am

    #732230

    Well it all depends ... on a lot of SAN related 'stuff' ... first the assumptions:

    • You have dual HBAs - minimum 2 Gb (the 4Gb HBA are out now too)
    • You are dual channeled and dual patherd to the SAN
    • Your SAN network is isolated

    Now let us start with the first group of 'all depends':

    • What RAID are you using (1, 5 or 1+0) ?
    • Are you using LUNs or Meta LUNs ?
    • Are you using something like EMC TimeFinder and BCVs (Business Continuance Volumes) ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 3 posts - 1 through 2 (of 2 total)

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