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

답변

  • 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: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163
    And I guess the link as below may help you to start your analysis. SQL Server IO with SQLIO: http://www.mssqltips.com/tip.asp?tip=2127

    For more information, please refer to SQL Server Best Practices Article http://msdn.microsoft.com/en-us/library/cc966412.aspx

    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.
    http://msdn.microsoft.com/en-us/library/ms187087(SQL.90).aspx


    Regards, Amber zhang

    2012년 4월 12일 목요일 오전 8:03
    답변자

모든 응답

  • 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


    Louis

    2012년 4월 11일 수요일 오전 3:44
    중재자
  • I read in the following article

    http://www.codeproject.com/Articles/43629/Top-10-steps-to-optimize-data-access-in-SQL-Server

    "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
    2012년 4월 11일 수요일 오후 9: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: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163
    And I guess the link as below may help you to start your analysis. SQL Server IO with SQLIO: http://www.mssqltips.com/tip.asp?tip=2127

    For more information, please refer to SQL Server Best Practices Article http://msdn.microsoft.com/en-us/library/cc966412.aspx

    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.
    http://msdn.microsoft.com/en-us/library/ms187087(SQL.90).aspx


    Regards, Amber zhang

    2012년 4월 12일 목요일 오전 8:03
    답변자