Performance Implications of Database Snapshots

Database snapshots are a new feature of SQL Server 2005. They offer a read-only, point-in-time view of a database. There have been some articles published here explaining what they are and discussing possible uses for them, but not about the performance impact of multiple snapshots, so I thought I'd take a look at that.

All examples shown here were done on a Windows XP, service pack 2 machine, running SQL Server 2005 Developer edition, RTM.

Statistics

The SQL Books Online states that performance may be decreased due to increased I/O on database snapshots. After running into a performance problem involving snapshots I decided to do some tests to see exactly how the performance of some typical operations degraded when using snapshots.

I set up a database with a sample table with 40 000 rows in it. The table had an int and a char(3000) column, ensuring that only two rows would fit onto a page. The int column was an identity and the clustered index.

For the first test, I inserted four sets of 5000 rows, taking care that each set affected different rows, and hence different pages. The database was restored from backup after each set. The first set of tests were done with no snapshots on the database. I then ran tests with one, two, three, four and five snapshots. For each, I measured how long the data modification would take and how long a subsequent checkpoint operation would take.

Likewise, I did tests where I deleted four sets of 5000 rows and updated four sets of 5000 rows, again with the database restored after each set of tests and the tests were done with differing numbers of snapshots in place

As the graphs show, the duration of the operations increases significantly as the number of snapshots that need to be updated increases.

From the results, it would seem as though large numbers of snapshots can seriously degrade database performance. The tests were done on a workstation, with all the files on a single drive, so the effects won't be so pronounced on a properly configured server with multiple properly configured drives, but it is still something to be watched.

While I was expecting an increase in the durations, I was not expecting a increase of this magnitude. In the case of deletes and updates, the difference in time to affect 5000 rows with no snapshots and with five snapshots is a factor of fifty!

I also did a quick test on a server (16 processor, 64 GB memory, SAN storage, SQL 2005 Enterprise SP2) to see how a number of snapshots affected delete speed on a high-powered machine. I wasn't being as careful as with the earlier experiments, but this does go to show that the performance degradation affects well-configured servers as well as desktop PCs.

I had a table with several million rows in and I was deleting 5000 at a time. 8 tests were done with each number of snapshots and the resulting times were averaged. The below graph shows how the delete time varied with the number of snapshots present.

A recent post by the PSS Engineers described in detail how IOs work in a snapshot, and the reason for the increase in duration.

Normally, when SQL modifies a data page, the data page is changed in memory and only the log record is hardened to disk before the transaction is considered complete.

Since snapshots do not have a transaction log, changes to the snapshot cannot be written to the log, but must be written directly to the data file. Hence, when a transaction modifies a page that must be written to a snapshot, to ensure durability the write of that page must be completed to disk before the transaction that modified the page is considered complete. This increases the number of physical disk writes that must be done during the transaction.

If the IO system that the snapshot is stored on is slow (as in my case with the initial experiment, since I was using a workstation PC), these additional writes (1 per snapshot file) can cause quite substantial slow downs in the source database.

This also explains why the checkpoint process does not show any appreciable increase in duration as the number of snapshots increases. Since the copy-on-write is done as part of the data modification statement, the checkpoint has no extra work to do.

Conclusion

While database snapshots have their uses, they are not without side-effect. Planning should be carefully done when using snapshots, especially if large numbers of snapshots are needed, or they are needed for long periods of time.

Depending on the capacities of the IO system, the extra load that the snapshots impose may be unnoticed, or it may drag the system down completely. Careful testing should be done before using them in production environments, especially in systems where fast transactions are essential.