Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Implications of Database Snapshots


Performance Implications of Database Snapshots

Author
Message
Matt Whitfield
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
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... ??

... sorry didn't see that the deletes were on different rows! Smile

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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


Matt Whitfield
Matt Whitfield
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 719
GilaMonster (9/23/2008)

In the tests I did, I was very, very careful that the inserts, the updates and the deletes affected different pages.

Yeah - i was just editing my post to say i'd missed that bit Smile

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
oraculum
oraculum
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 501
Is the snapshot functionality mentioned here the same that is used for snapshot replication? do the same issues apply for this if you run the replication once a day on a schedule?

Oraculum
Mike Metcalf
Mike  Metcalf
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 53
completely different Smile
Andreas Berchtold
Andreas Berchtold
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
Hi Gail
I was very interested reading your article, because we have performance problems with a customer.
We have a replication (many hundred of pocket pc's connected) and thus a snapshot.
What I don't understand: Why more than 1 snapshot, isn't a snapshot "only" used to set up a replication and therefore you need only 1 snapshot?
thanks and greetings
Andreas
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
Andreas Berchtold (9/23/2008)

We have a replication (many hundred of pocket pc's connected) and thus a snapshot.
What I don't understand: Why more than 1 snapshot, isn't a snapshot "only" used to set up a replication and therefore you need only 1 snapshot?


I'm not talking about snapshot replication, I'm talking about database snapshots. They're completely unrelated concepts.

Database snapshots are a 'new' feature in SQL 2005 that allows for a point-in-time 'copy' of a database. They have nothing to do with snapshot replication or with snapshot isolation level.

For more info on what database snapshots are, see -
http://msdn.microsoft.com/en-us/library/ms175158(SQL.90).aspx


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


Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36340 Visits: 18752
I think that snapshots are really only useful in two places. This is theoretical and from research in talking to people.
- Quick restore after upgrades/patches. Take one before making changes, make the changes, if you need to rollback, you can do it with the snapshot.
- Short term comparisons, for things like EOM/EOY work. Grab a view of the database for some point in time, perhaps 31st of the month or year, do work, compare backwards.

The problem with anything more than short term is that you have to delete the snapshot to restore the database or recover anything. IMHO, the biggest feature missing from SS2K5 in this area, is that we can't backup snapshots. Doesn't make sense to me. Let me stream out pages in a backup just like other databases, then I have the chance to recover the snapshot in a DR situation.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ray Mond
Ray Mond
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 1542
So the performance impact is most noticeable when your database is UPDATE/DELETE heavy, but not if its INSERT heavy.

Ray Mond
Get a SQL Size 2.0 single-instance license for FREE
Claim one here!
Sudhakar-339037
Sudhakar-339037
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 206
We have a 2TB database in a mirrored environment. We take snapshot on the mirrored instance for various reporting applications. It reallys helps a lot.

But however few points to remember:

1. During our index maintenance, there is a heavy I/O operation. And this causes, actions that are attempting to modify pages may become stalled attempting to write to the snapshot file and checkpoint can be a secondary victim. So its better to drop snapshot and recreate after the reorg or indexing jobs.

2. Also SQL Server will be very slow when there is so much to write in its checkpoint and thus causing performance problems also. You might see "insufficient resource error".

The root cause is Windows limits the amount of data that may reside in the file( for snapshot sparse file). Microsoft senior engineer has posted this in his recent blog just yesterday.
http://blogs.msdn.com/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx gives you the limitations of snapshot for VLDBs.

thks,
//S
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search