Do indexes being on different db file on different physical disk affect disk IO?



  • Well, this is a very vague question, and you are going to have to be more specific.  Assuming you mean deifferent from the base table (meaning the clustered index or heap) then yes.  The question really needs to be "does it help?" but unfortunately that answer will be "it depends" and it all depends on the architecture of your machine too.  They are starting to build machines for virtual machines that just have massive numbers of spindles all treated as one "physical disk" that are so fast it doesn't matter. 

    So the serious, logical answer is that pretty much anything you do with disks affects IO, and it is up to the situation as to whether it is a "good" effect or a "bad" effect.  If you have a specific scenario it would help


    11 เมษายน 2555 3:44
  • I read in the following article

    "For frequently accessed tables containing indexes, put the tables and the indexes in separate file groups. This would enable reading the index and table data faster. "

    In what scenario will that applies?

    If have have the following objects and filegroup, would IO be optimized, INTERFACE and PRIMARY are on different disk?

    PK_Global_Record INTERFACE
    IX_GlobalNode_ID INTERFACE
    IX_Global_Record_1 PRIMARY
    11 เมษายน 2555 21:51
  • Hi ASP808,

    Regarding to your description, which is really depends on your IO Capacity .  You can try to use an I/O stress tool to validate performance and ensure that the system is tuned optimally for SQL Server.

    You can download this tool here:
    And I guess the link as below may help you to start your analysis. SQL Server IO with SQLIO:

    For more information, please refer to SQL Server Best Practices Article

    To create index on separate drive will help performance because, the load will be balance between different disks.  I think placing indexes on a separate disk can provide a couple of benefits:
    1. Putting heavily accessed tables and indexes on a different disks improves performance.
    2. Provide scalable systems that make large indexes more manageable.
    So it depends whether or not there is really a benefit to splitting indexes from data.

    The following document helps you understand the database files across multiple disks.

    Regards, Amber zhang

    • ทำเครื่องหมายเป็นคำตอบโดย amber zhangEditor 19 เมษายน 2555 7:07
    12 เมษายน 2555 8:03