יום רביעי 11 אפריל 2012 00:55
Do indexes being on different db file on different physical disk affect disk IO?
יום רביעי 11 אפריל 2012 03:44מנחה דיון
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
- הוצע כתשובה על-ידי Uri DimantMVP, Editor יום רביעי 11 אפריל 2012 05:31
יום רביעי 11 אפריל 2012 21:51
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
יום חמישי 12 אפריל 2012 08:03משיב
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.
Regards, Amber zhang
- סומן כתשובה על-ידי amber zhangEditor יום חמישי 19 אפריל 2012 07:07