matt stockham (9/23/2008)
Nice article. I have a few questions:1) why do inserts scale so much better than updates and deletes on a workstation? A page is a page, assuming that the same number of pages were modified in the insert load as the update and delete loads I would expect them to scale similarly. Did you happen to look at the wait stats by any chance?
I'm not sure. I suspect it may have something to do with what needs to be copied to the snapshot. In the case of an insert that's writing into a new page added to the table, all that needs to be done to the snapshot file is that empty page adding.
In the case of updates and deletes, the entire old version of the page (all 8k of it) has to be written intact to the snapshot.
2) any plans for an article on read performance with multiple snapshots?
I don't. I can look into it, but I suspect there will be little to see. Regardless of how many snapshots are in place, when a read occurs aginst a snapshot, SQL will check to see if the affected pages are in the snapshot, if so read from snapshot otherwise read from the source DB
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