Performance Implications of Database Snapshots

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gail Shaw » Performance Implications of Database...

41 posts, Page 2 of 5

««1 2 3 4 5 »»»

Performance Implications of Database Snapshots

Rate Topic

Display Mode

Topic Options

Author

Message

Matt Whitfield

Posted Tuesday, September 23, 2008 2:47 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718

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

GilaMonster

Posted Tuesday, September 23, 2008 2:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 38,076, Visits: 30,371

Matt Whitfield (9/23/2008)

Chirag (9/23/2008)

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

Matt Whitfield

Posted Tuesday, September 23, 2008 2:50 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718

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

oraculum

Posted Tuesday, September 23, 2008 4:18 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 03, 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

Mike Metcalf

Posted Tuesday, September 23, 2008 4:19 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53

completely different :)

Post #574185

Andreas Berchtold

Posted Tuesday, September 23, 2008 5:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 08, 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

GilaMonster

Posted Tuesday, September 23, 2008 5:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 38,076, Visits: 30,371

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

Steve Jones - SSC Editor

Posted Tuesday, September 23, 2008 6:51 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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

Ray Mond

Posted Tuesday, September 23, 2008 7:01 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 343, Visits: 1,514

So the performance impact is most noticeable when your database is UPDATE/DELETE heavy, but not if its INSERT heavy.

Ray Mond

TLogInfo - the only FREE tool to analyse your transaction logs. Download here.
SQL BAK Reader - the only FREE tool to inspect your SQL Server backup files without using SQL Server. Download here.

Post #574286

Sudhakar-339037

Posted Tuesday, September 23, 2008 8:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:45 AM
Points: 5, Visits: 154

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 »

41 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions