Using database snapshots

Three weeks ago I kicked off a survey about using database snapshots – see here – because I don’t really know how much they’re used or not out in the wild. Here are the results:

 

The “Other” values are:

  • 13 x “No – not at the moment.”
  • 10 x “In our test environment for reverting to the original database state after a test. Much faster than restoring every time.”
  • 6 x “For development, to be able to go back to a previous known state after trying out new scripts and stuff.”
  • 5 x “Haven’t really looked at using them.”
  • 5 x “No – just read about them recently and have yet to determine usefulness.”
  • 3 x “Yes – every time I do a DBCC CHECKDB (okay, it’s hidden, but still a snapshot).”
  • 2 x “During application installation in order to do an easy rollback if smoke testing fails.”
  • 2 x “For rollback of code/data releases.”
  • 2 x “Never used, but did recommend once as a pre-deployment option so that there was a faster rollback in case a developer’s script broke once in Prod. Recommendation was not implemented.”
  • 1 x “As failsafe for monthly production deployments.”
  • 1 x “For EOM reporting copies of the production database.”
  • 1 x “No. Had performance issues so we turned them off.”
  • 1 x “Not supported in SQL2000.”
  • 1 x “When adding articles in replication.”
  • 1 x “Yes – For quick rollback during production changes.”
  • 1 x “Yes for ETL, and via a linked server connection since snapshot isolation isn’t an option.”
  • 1 x “Yes, for instantaneous restore of development environment.”
  • 1 x “Yes, rarely – for investigating issues on copy of production DB, way faster than restore.”

Very interesting! More than 60% of respondents don’t or can’t use database snapshots. I’ve always thought that database snapshots are an under-utilized feature, I’m not entirely sure why though (apart from the obvious Enterprise Edition restriction – lifted in 2016 SP1). It could be some of the limitations of them, such as:

  • You can’t refresh one without all connections to it being dropped (and then dropping and recreating it)
  • You can’t back up a snapshot (would require some interesting plumbing in the Storage Engine, as currently backups don’t use the buffer pool, and it’s the buffer pool that manages what’s in a database snapshot)
  • You can’t modify a snapshot (i.e. like having a base VM with various diff disks)
  • They can have a performance impact on the production/mirror workload depending on how heavily they’re used

I’m really glad that no-one confessed to using database snapshots as an alternative to log backups! See this TechNet Magazine Q&A column I wrote last year that explains why not.

Rather than repeat what’s already been said about why database snapshots can be useful, I’m going to provide you with some links to further reading:

  • Top-level of Books Online on database snapshots
  • Whitepaper on performance implications of using database snapshots
  • Blog post on what happens to a database snapshot when a transaction rolls back
  • Blog post on some of the problems you can see from DBCC CHECKDB using database snapshots under the covers
  • Blog post on a bunch of potential problems when a database snapshot fills up or becomes corrupt
  • Blog post about memory usage from a database snapshot

Enjoy!

2 thoughts on “Using database snapshots

  1. I have been using snapshots for many reasons since they became available. The only performance issue I had was due to the limitation of the size of a sparse file in Windows. In Windows 2003, the size of a sparse file cannot exceed 64GB, in Windows 2008 it cannot exceed 8GB. If your snapshot sparse files exceed that size, for all intents and purposes they become corrupt, and the original database starts throwing 1450 and 665 errors.
    I discovered this issue when i rebuilt all my indexes on a database that I had forgotten to take the snapshot off of.
    This is the MSFT KB article on the issue – http://support.microsoft.com/kb/2002606

  2. Snapshots can be very helpful in mirroring setting. I use them to provide read-only databases for Datawarehouse queries mainly, and other cases when the DEV team does not want to put any extra locks on source databases. I noticed though, that a restore from a snapshot can be very long (much longer than a standard restore from a backup)) and another down point for this is that I can’t monitor the progress of the restore operations. There is no way of finding this out from SQL dynamic views.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.