Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Performance Implications of Database Snapshots Expand / Collapse
Author
Message
Posted Tuesday, September 23, 2008 2:47 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, 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! :)


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #574126
Posted Tuesday, September 23, 2008 2:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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 2008, MVP
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

Post #574129
Posted Tuesday, September 23, 2008 2:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, 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 :)


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #574130
Posted Tuesday, September 23, 2008 4:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 3, 2012 6:35 AM
Points: 97, 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
Post #574184
Posted Tuesday, September 23, 2008 4:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53
completely different :)
Post #574185
Posted Tuesday, September 23, 2008 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 8, 2009 8:09 AM
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
Post #574214
Posted Tuesday, September 23, 2008 5:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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 2008, MVP
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

Post #574235
Posted Tuesday, September 23, 2008 6:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:45 PM
Points: 31,354, Visits: 15,816
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
Post #574276
Posted Tuesday, September 23, 2008 7:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:57 AM
Points: 343, Visits: 1,520
So the performance impact is most noticeable when your database is UPDATE/DELETE heavy, but not if its INSERT heavy.

Ray Mond
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #574286
Posted Tuesday, September 23, 2008 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:00 AM
Points: 6, Visits: 204
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
Post #574364
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse