InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 21:45:25 GMT20http://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxThat's actually a very good question Low Rider. The snapshot is a read-only, point-in-time database. As such, the queries run against it do not create locks nor do they honor locks. There can be no dirty reads since the data doesn't change so there's no need to lock anything. This means greater concurrency. More queries can read the data at the same time without being blocked by writes.However, the read of the data still hits the source database's data files, so it still has an impact on the source database. This is why a majority of people (in my experience) use database snapshots for a read-only reporting database on the mirror database in a mirroring partnership. This moves the IO completely off of the live database and server.Sat, 04 Dec 2010 10:27:23 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxI have a doubt regarding the performance part of database snapshots.Now it's said that one can use a database snapshot as a reporing database where the reports can point to the snapshot database instead of the original db.Now if the data in the pages is not modified doesn't a query which runs against the snapshot db would retrieve the same from the pages/extents of the original database.Since the orginal database has to serve the request how exactly the database snapshot will give any performance benefit ?thanksSat, 04 Dec 2010 03:33:39 GMTLow Riderhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxGail,Good job on delivering a quantitative measure of the impact of snapshots.Mon, 23 Nov 2009 11:47:13 GMTdavid_lundellhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Robert Davis (9/24/2008)[/b][hr]Great article Gail!!! I'd love to see a follow-up where you measure the effects that snapshots on a mirrored database (on the mirror partner) running in synchronous mode has on the principal partner.[/quote]Interesting idea. I'll put it on the list of things to do.Thu, 25 Sep 2008 10:06:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Robert Davis (9/24/2008)[/b][hr][quote][b]matt stockham (9/24/2008)[/b][hr]Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size? Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created.[/quote]You're assuming that inserts are put into new pages. That's not always true. Inserts will be added to existing pages until SQL needs a new page. So inserts will be affected by snapshots in these cases.[/quote]Correct ... the article stated that the table was set to force 2 entries per page with an identitifer clustered index, and my assumption is that the identity seed had not been reset. Wed, 24 Sep 2008 14:26:34 GMTmatt stockhamhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxGreat article Gail!!! I'd love to see a follow-up where you measure the effects that snapshots on a mirrored database (on the mirror partner) running in synchronous mode has on the principal partner.Wed, 24 Sep 2008 13:49:46 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]matt stockham (9/24/2008)[/b][hr]Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size? Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created.[/quote]You're assuming that inserts are put into new pages. That's not always true. Inserts will be added to existing pages until SQL needs a new page. So inserts will be affected by snapshots in these cases.Wed, 24 Sep 2008 13:46:09 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]matt stockham (9/24/2008)[/b][hr]Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size? Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created. Following that train of thought makes me wonder why there is any overhead at all unless there is some requirement on the index pages or any of the system-type pages - maybe contention on whichever set of pages determines if the snapshot file holds the page to look at? Am I missing something obvious here? [/quote]Possibly the overhead is caused by necessary changes to the allocation pages. I'm just guessing here, as I don't know the exact details of what changes are made to what and when when it comes to snapshots.I do know someone to ask though. :cool:[quote] I only asked because the article covered all the other obvious scenarios - there are probably a number of good questions that could be answered on the read side - does the snapshot have its own procedure cache entries etc.[/quote]I'll put said article on the to-do list.From what I recall, the snapshots do have their own cache entries, both procedure and data. This applies even to data pages read from the source database.Wed, 24 Sep 2008 13:05:28 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxRe. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size? Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created. Following that train of thought makes me wonder why there is any overhead at all unless there is some requirement on the index pages or any of the system-type pages - maybe contention on whichever set of pages determines if the snapshot file holds the page to look at? Am I missing something obvious here?re. read performance ... I wouldn't expect a great deal as there shouldn't be any locking (snapshots don't lock because they are read-only, correct?). I only asked because the article covered all the other obvious scenarios - there are probably a number of good questions that could be answered on the read side - does the snapshot have its own procedure cache entries etc.Wed, 24 Sep 2008 08:03:06 GMTmatt stockhamhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]matt stockham (9/23/2008)[/b][hr]Nice article. I have a few questions:1) why do inserts scale so much better than updates and deletes on a workstation? A page is a page, assuming that the same number of pages were modified in the insert load as the update and delete loads I would expect them to scale similarly. Did you happen to look at the wait stats by any chance?[/quote]I'm not sure. I suspect it may have something to do with what needs to be copied to the snapshot. In the case of an insert that's writing into a new page added to the table, all that needs to be done to the snapshot file is that empty page adding. In the case of updates and deletes, the entire old version of the page (all 8k of it) has to be written intact to the snapshot.[quote]2) any plans for an article on read performance with multiple snapshots?[/quote]I don't. I can look into it, but I suspect there will be little to see. Regardless of how many snapshots are in place, when a read occurs aginst a snapshot, SQL will check to see if the affected pages are in the snapshot, if so read from snapshot otherwise read from the source DBWed, 24 Sep 2008 00:36:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxNice article. I have a few questions:1) why do inserts scale so much better than updates and deletes on a workstation? A page is a page, assuming that the same number of pages were modified in the insert load as the update and delete loads I would expect them to scale similarly. Did you happen to look at the wait stats by any chance?2) any plans for an article on read performance with multiple snapshots?Tue, 23 Sep 2008 21:16:44 GMTmatt stockhamhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxExcellent article, Gail, thanks for doing it.We don't use snapshots currently, heck, 90% of my servers are still 2000. :D But I can definitely see some uses for it in the future.Tue, 23 Sep 2008 13:27:04 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxDunno. Probably.I've seen people suggest snapshots for month-end reporting with the intention of keeping the snapshots in place for a year.Tue, 23 Sep 2008 10:56:13 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxExcellent 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?Tue, 23 Sep 2008 10:20:15 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxI 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.Tue, 23 Sep 2008 09:37:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxHi Gail,Your post is making an important pointCould 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,IgalTue, 23 Sep 2008 09:30:09 GMTigalgrehttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxExcellent article Gail. Very well explained ....:)Tue, 23 Sep 2008 09:06:54 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Alberto De Rossi (9/23/2008)[/b][hr]Nice article. Would you please briefly explain us how did you get the measures to do those graphics? Did you use profiler? [/quote]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.Tue, 23 Sep 2008 08:45:44 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Steve Jones - Editor (9/23/2008)[/b][hr]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.[/quote]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 processiirc, 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.Tue, 23 Sep 2008 08:43:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxNice 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.AlbertoTue, 23 Sep 2008 08:41:18 GMTAlberto dbLearnerhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxWe 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.Tue, 23 Sep 2008 08:22:28 GMTDCPetersonhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxWe 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. Tue, 23 Sep 2008 08:04:15 GMTSudhakar-339037http://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxSo the performance impact is most noticeable when your database is UPDATE/DELETE heavy, but not if its INSERT heavy.Tue, 23 Sep 2008 07:01:41 GMTRay Mondhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxI 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.Tue, 23 Sep 2008 06:51:58 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Andreas Berchtold (9/23/2008)[/b][hr]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?[/quote]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 - [url]http://msdn.microsoft.com/en-us/library/ms175158(SQL.90).aspx[/url]Tue, 23 Sep 2008 05:49:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxHi GailI 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 greetingsAndreasTue, 23 Sep 2008 05:19:21 GMTAndreas Berchtoldhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxcompletely different :)Tue, 23 Sep 2008 04:19:42 GMTMike Metcalf-160221http://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxIs 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?Tue, 23 Sep 2008 04:18:54 GMToraculumhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]GilaMonster (9/23/2008)[/b][hr]In the tests I did, I was very, very careful that the inserts, the updates and the deletes affected different pages.[/quote]Yeah - i was just editing my post to say i'd missed that bit :)Tue, 23 Sep 2008 02:50:27 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Matt Whitfield (9/23/2008)[/b][hr][quote][b]Chirag (9/23/2008)[/b][hr]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.[/quote]If that were the case, then delete performance wouldn't be affected, and neither would update... ??[/quote]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.Tue, 23 Sep 2008 02:49:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Chirag (9/23/2008)[/b][hr]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.[/quote]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! :)Tue, 23 Sep 2008 02:47:11 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Chirag (9/23/2008)[/b][hr]Nice article Gail. 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.[/quote]Yes to both.Tue, 23 Sep 2008 02:47:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Mike Metcalf (9/23/2008)[/b][hr]We use Snapshots in our production system, what I am wondering is whether the snapshots for the tests were on the same disks as the main database file, or seperate ones, and also whether the snapshots themselves were on seperate disks?[/quote]For the first set of tests (insert, update, delete) everything was on 1 drive. It was a workstation PC, it only had one drive.The second test that I did (deletes on the server) the snapshot files were separate from the data and log files. iirc, I put them onto the drive used for backups (which was not in use at that time)I didn't have enough drives (even on the server) to test with one snapshot per drive. If they are, I would imagine the effect is drastically reduced.Tue, 23 Sep 2008 02:45:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxNice article Gail. 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.Tue, 23 Sep 2008 02:43:01 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxWe use Snapshots in our production system, what I am wondering is whether the snapshots for the tests were on the same disks as the main database file, or seperate ones, and also whether the snapshots themselves were on seperate disks?People should also be incredibly wary of putting a snapshot on the same disk as a DB, due to the nature of how snapshots work they become very fragmented (this should probably be addressed by MS), should your filegroup grow on that disk then the fragmentation will carry over to that too, leading to a performance loss.Tue, 23 Sep 2008 02:22:43 GMTMike Metcalf-160221http://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxI would say the conclusion is a bit of an understatement - with 5 snapshots the graphs show performance degradation of 500% at best, and 5000% at worst...Put another way, unless your SQL Server is under about 2% load or less, then putting in 5 snapshots would have the distinct possibility that your server would then be under 100% load.Of course, that's an over-simplification, and the tests aren't very specific about the bandwidth of disk IO etc etc - but even so, those graphs are distinctly scary.Tue, 23 Sep 2008 02:21:32 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspx[quote][b]Phil Melling (9/23/2008)[/b][hr]All the more reason for me to read up some more on them! :)[/quote]:hehe: When I get a chance I'll try and finish the follow-up to this article - what snapshots are and what they're not.Tue, 23 Sep 2008 01:28:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxAll the more reason for me to read up some more on them! :)Tue, 23 Sep 2008 01:10:18 GMTSQLPhilhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxThanks. Glad you liked it.Just one thing though. Snapshots are not a backup strategy. They're great for rolling back accidental changes, but if the source DB is damaged, dropped, corrupted, offline, etc, the snapshot will be inaccessible and will have to be dropped.Tue, 23 Sep 2008 01:06:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574056-1390-1.aspxBrilliant article! It just goes to show that there are a whole host of things to consider whenever you're devising a backup strategy for your systems. Thanks to the information you've provided Gail, I'm sure you've saved a lot of people a considerable amount of time if they're looking to evaluate the effectiveness of snapshots as a backup and recovery solution.Tue, 23 Sep 2008 00:49:12 GMTSQLPhil