September 23, 2008 at 5:19 am
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
September 23, 2008 at 5:49 am
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
September 23, 2008 at 6:51 am
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.
September 23, 2008 at 7:01 am
So the performance impact is most noticeable when your database is UPDATE/DELETE heavy, but not if its INSERT heavy.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
September 23, 2008 at 8:04 am
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
September 23, 2008 at 8:22 am
We use snapshots extensively in development and they have been a godsend there; they have saved us untold hours. However, for our production systems we avoid snapshots except as Steve says, during some deployments. We don't use them every time, it just depends on what the deployment entails, the risks involved, and the cost/benefiit of using them in that particular situation.
The only place I'd seriously consider using a snapshot in production is on a mirrored database if we wanted to make it available for query, but we opted to use transactional replication instead.
Good article, Gail. Unless we understand the costs of using snapshots we can't intelligently decide if they are worth it.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 23, 2008 at 8:41 am
Nice article. Would you please briefly explain us how did you get the measures to do those graphics? Did you use profiler?
Thanks in advance for any clue you can give us.
Alberto
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
September 23, 2008 at 8:43 am
Steve Jones - Editor (9/23/2008)
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.
I recall reading about that. To backup a snapshot would require merging the pages in the snapshot with the pages in the source database to create a backup file with the entire database in. It wouldn't be restorable as a snapshot, only as a full database. To restore as a snapshot would require that every single page gets examined as the 'snapshot' backup is restored to see whether or not the source DB has the same page or not. If the source DB's copy of the page is the same, discard the page, otherwise write it into the snapshot file.
If that was done, then the backup of the snapshot would be the same size as the backup of the source DB. The backup process would to take longer due to the work necessary to merge the DBs and the restore would take much, much longer as it would have to examine the contents of every page during the restore process
iirc, the conclusion was that if you want a backup of a database at a specific point in time, then backup the DB then. Since a backup that merges the snapshot with the full wouldn't be restorable as a snapshot, there's little advantage to been able to backup a snapshot.
Bear in mind that the only thing in the snapshot's files are the original version of pages that have changed since the snapshot was created. It's not usable without the source DB and if any changes are made to the source DB without checking if the original pages need to be added to the snapshot file, the snapshot can become transactionally and logically inconsistent.
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
September 23, 2008 at 8:45 am
Alberto De Rossi (9/23/2008)
Nice article. Would you please briefly explain us how did you get the measures to do those graphics? Did you use profiler?
I ran the queries with STATISTICS TIME on, copied the output to excel, took the average of the values returned by the different tests, then graphed in excel.
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
September 23, 2008 at 9:06 am
Excellent article Gail. Very well explained ....:)
September 23, 2008 at 9:30 am
Hi Gail,
Your post is making an important point
Could it be that a different indexing strategy would have a considerable different performance profile. I would call the test setup as a non scalable one. The performance degrades exponentially with regards to the number of snapshots. If the tables were heaps rather than clustered indices do you think it would make a positive difference?
Regards,
Igal
September 23, 2008 at 9:37 am
I don't think it will make any difference. The extra time comes from writing the original version of the modified pages into the snapshot files, not from searching through tables to find rows to affect.
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
September 23, 2008 at 10:20 am
Excellent article. Thank you. Interesting also to see the difference between identity inserts and deletes and updates quantified.
But I do wonder why someone would need 5 snapshots. That seems to be a heck of a lot of reporting. By the time I got to needing 5 snapshots, wouldn't I be better off with a data warehouse and cube?
September 23, 2008 at 10:56 am
Dunno. Probably.
I've seen people suggest snapshots for month-end reporting with the intention of keeping the snapshots in place for a year.
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
September 23, 2008 at 1:27 pm
Excellent article, Gail, thanks for doing it.
We don't use snapshots currently, heck, 90% of my servers are still 2000. 😀 But I can definitely see some uses for it in the future.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply