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


SharePoint 2010 database maintenance whitepaper

As you may know, I've been teaching the SQL Server portion of the SharePoint MCM since it started. The old database maintenance whitepaper for SharePoint 2007 had all kinds of things wrong with it and the publishing of the updated whitepaper for SharePoint 2010 was eagerly awaited by the community. Unfortunately it too had a bunch of problems so I offered to get involved and comprehensively review and rewrite it, and did so just before the summer.

It's finally been republished with all my revisions and you can download it from:


SQLskills at PASS and Connections in October

The Fall conferences are a little earlier this year – October rather than November – and we'll be at both PASS and SQL Connections.

We hope to see you there!

PASS Summit

This year's PASS Summit is in Seattle again from October 11-14 – see here for registration details.

Kimberly and I decided not to submit workshops this year so we're only doing Spotlight Sessions. Jonathan's doing a workshop and a Spotlight Session and Stacia's doing two workshops and a panel discussion.

Here's the line-up:

  • Workshops
    • Extended Events Deep Dive (Jonathan)
    • MDX, DAX, and DMX: An Introduction to the Languages of BI (Stacia)
    • So how does the BI workload impact the database engine? (Stacia, 1/2 day)
  • Spotlight Sessions
    • Using Event Notifications in SQL Server 2005/2008 (Jonathan)
    • Isolated Disasters in VLDBs (Kimberly)
    • More DBA Mythbusters (Paul)
  • Panel Discussion
    • Are you a Linchpin? Career management lessons to help you become indispensible. (Stacia)

SQL Connections

The Fall SQL Connections show will be in Las Vegas as usual, from October 30 – November 4 – see here for registration details.

We've put together a great show (Kimberly and I are the conference co-chairs) and we'll both be doing workshops and sessions, as will Stacia.

Here's the line-up:

  • Workshops
    • 10/30: SPR201: The Foundations of a Healthy SQL Server Database (Kimberly and Paul)
    • 10/31: SPR202: Collaborative Business Intelligence: Putting the Pieces Together (Stacia)
    • 10/31: SPR303: Designing for Performance and Scalability (Kimberly)
  • Paul's sessions
    • SQL216: More DBA Mythbusters 
    • SQL419: Wait Statistics: Avoiding 'Knee-Jerk' Performance Tuning 
    • SQL422: Advanced Recovery Techniques 
    • SQL225: Follow the Rabbit: Wrap-up Q&A
  • Kimberly's sessions
    • SQL207: Precarious? Nefarious? Strategies that Work for Nonclustered Indexes! 
    • SQL310: Taking the Sting Out of Statistics
    • SQL313: Filtered Indexes and Filtered Statistics: The Good, the Bad and the Ugly
    • SQL225: Follow the Rabbit: Wrap-up Q&A
  • Stacia's sessions
    • SQL209: Managing Self-Service BI in PowerPivot for SharePoint 
    • SQL312: Building a BI Performance Monitoring Solution
    • SQL226: Understanding BI Security Best Practices