Matt Whitfield (9/23/2008)
The pages from the source database get written to the snapshot's sparse file the first time the page gets modified in the source database. Is that right?
If that's so, does then the performance degradation happen only for the first time a page gets modified.
If that were the case, then delete performance wouldn't be affected, and neither would update... ??
Sure it would. Both are making changes to the source DB. If the pages aren't already in the snapshot file then they have to be copied there before the update/delete can occur.
In the tests I did, I was very, very careful that the inserts, the updates and the deletes affected different pages. Otherwise the results would be skewed.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass