March 28, 2011 at 7:46 am
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.
March 28, 2011 at 7:52 am
The updates to the indexes are done in memory. The query processor doesn't write directly to disk.
It'll be sequential, but to pages in memory, not to the disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2011 at 8:16 am
I'm confused. Doesn't the added row for the key of each index get added physically to the index file or removed if that's the case?
March 28, 2011 at 8:19 am
It gets added to the index immediately, but that's in memory only. SQL will write the changed pages back to disk later on, not as part of the transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2011 at 8:23 am
Ok then. Later on when it goes to write it, will it update each file in parallel or sequentially? And how much later on?
Basically trying to determine if the drop in Average disk second per transfer (read/write) across the board is a good thing or deceptive since to update all 3 indexes it will take just as long as it did before.
also, what happens if it is out of physical memory. does it just store the changes in the pagefile on disk only to write those to the index disk files later?
Thanks!
March 28, 2011 at 8:31 am
shifty1981 (3/28/2011)
Ok then. Later on when it goes to write it, will it update each file in parallel or sequentially? And how much later on?
Depends on the underlying disk architecture, but it's done along with lots and lots of other changes. Read up on Checkpoint
Basically trying to determine if the drop in Average disk second per transfer (read/write) across the board is a good thing or deceptive since to update all 3 indexes it will take just as long as it did before.
I would say good. You're allowing more to happen in a shorter time. It's still a little high, but whether it's affecting SQL or not is hard to tell. you'd have to check wait stats
also, what happens if it is out of physical memory. does it just store the changes in the pagefile on disk only to write those to the index disk files later?
If SQL's running low on memory it will start writing changes to disk so that it can reuse memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply