SQL Server Snapshots – Use Cases and Problems
It about eight years since I last used Database Snapshots. Through no real fault of their own, we haven’t been friends for a long time.
As with most things, Database Snapshots are great in moderation. We had valid use-cases. Users were looking for a Static point to use for Month End reporting. On a “late Data Import day”, a Database Snapshot could be used to allow historic Reporting to continue until the Data Load had completed. Snapshots could also be used to check the effect of a Change.
In summary, Database Snapshots provide a facility to keep a historic view of the Data… at a cost. They begin by presenting you with another Database that is identical to the one that you already have. Under the covers, a “Sparse file” is created. From that point onwards, for any Data that changes in the original Database, the original values are stored in the “Sparse file” – describing the difference between the Database now, and how it was when the Snapshot was taken. This all happens invisibly – when you query the Database Snapshot, SQL Server will use the original Database overlaid with changes in the “Sparse file” to provide you with the data as it was when the Snapshot was taken.
This can work really well. Most of the time. Until a point.
One Database Snapshot can be fine, sometimes two is OK but any more than that and you can run into problems.
In our case, performance dropped off a cliff. The daily Data Load was looking like it may not complete within the day. At first, we thought it may have been hardware related. The Storage had a fair amount of resillience but this means that it can slow right down in the event of a partial hardware failure. This was investigated, but reported back to be fine, albeit a bit slow. Next, the health of the Server itself was checked and that all checked out too. All looked normal, except for the slow disk speed. I don’t recall much about Locking, nor the state of Wait Types at the moment – I’m not entirely sure that they were checked (hindsight is a marvellous thing).
After a bit of a heated discussion between a team member and the Infrastructure team, I spotted that we’d ended up with three Database Snapshots on the same Database. Given the amount of “Data Churn” each day, this meant that for every Data Page being updated as part of the load, repeated updates to the Snapshot sparse files were required.
A bit of searching on Google and I found the following excellent article by Gail Shaw:
http://www.sqlservercentral.com/articles/Performance+Tuning/64080/
Look at the graphs – it’s amazing how the timings suddenly increase exponentially as you add more than two Snapshots.
After spotting that, I dropped the Database Snapshots and everything returned to normal.
This episode stopped us using Database Snapshots completely. Not even one or two were allowed – in fact, it became a higher-management staple during performance related issues – “Have you taken a Database Snapshot” was up there with “defragment the Indexes”, just plain “Indexing” or “reboot the Server”.
Fast forward six years… why am I using Database Snapshots again now?
I am currently working on a Project that requires a small amount of data to be deleted on a very large Database. This offers a few potential solutions for repeated Testing:
- Use Database Transactions
- Restore Backups after each test run
- Utilise Database Snapshots
Use Database Transactions
At the beginning of every set of data modification steps, BEGIN TRANSACTION can be used to open a Transaction. All modifications within this Transaction can either be rolled back (i.e. the Database is set as though they never happened) or committed.
Data involved in a Transaction can only be viewed within the same Query window.
In a multi-user environment, an open Transaction can hold up other users of the Database as they will not be able to access Data / Tables that are involved in that Transaction, but then that’s not an issue because you wouldn’t be doing this on Production, would you?
I could have used a Transaction, rolling back after every update set but then that’s a bit fiddly. Rejected.
Restore Database Backup after each test run
The Database is approx 200Gb in size on a Virtual Server – this could take some time. Rejected.
Utilise Database Snapshots
This works.
To use Database Snapshots, I am setting a “baseline” – i.e. getting the Database to exactly how I need the start position to be before any changes are made.
Next step is to take a DATABASE SNAPSHOT – i.e.
CREATE DATABASE [Medrano_preDeletions] CONTAINMENT = NONE ON ( NAME = N'Medrano_ExtractProd', FILENAME = N'H:\Snapshot\Medrano_ExtractProd.ss' ) AS SNAPSHOT OF [Medrano];
After performing the Deletions, it’s simple to go back to the original position.Simply restore the Database from the Snapshot version – i.e.
RESTORE DATABASE [Medrano] FROM Database_Snapshot = 'Medrano_preDeletions';
Finally, once I’m done testing, I can drop the Database Snapshot.
DROP DATABASE [Medrano_preDeletions];
Over time, I might need to change my “baseline” – i.e. after adding Indexes. Care is required to get this right (otherwise you can get in a bit of a mess).
- Keep all of your Changes scripted (you probably do that anyway)
- Restore to the existing baseline
- Apply the Indexes or other changes
- Drop the existing Database Snapshot
- Create a new Database Snapshot
Heeding lessons from the past, and with a nod to Gail, I’m stopping at a single Snapshot and it’s working just great.
Getting back to the Baseline takes less than five seconds – after all, it’s all about the Base line.
Nigel.
