Home Forums Article Discussions Article Discussions by Author Discuss content posted by Gail Shaw Performance Implications of Database Snapshots RE: Performance Implications of Database Snapshots

  • September 23, 2008 at 2:49 am

    #875491

    Matt Whitfield (9/23/2008)

    Chirag (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.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass