how does sql insert data into indexes?
Posted on 2011-03-25
We have a high IO database with a table that has a clustered index and 3 non clustered indexes.
when data is inserted into the table (clustered index), does it try to write the columns for each non clustered index at the same time or sequentially? can you point to an article talking about this?
reason I ask is we had a 6 disk raid10 holding all 3 non clustered indexes that had an average disk second per transfer of 120 ms. we separated the set into 3 raid1 sets (2 disks per) and put one non clustered index on each set. the ADs/T for each drive is around 40 ms. I'm wondering if the whole transaction that updates all 3 indexes if it still takes 120ms to complete or if it's done in parallel.
yes I know 40ms is a bad ADs/T to get. Considering we were in the 200's originally, getting down to 40 is peachy for us, though of course we're looking to improve.