Advantage of using a database snapshot for reporting purposes

Question

What is the performance advantage of using a snapshot of a database for reporting purposes?

The way I see it, it would probably downgrade performance since for every write in the original database, another write would have to be done for the snapshot itself.

I can see that you'd use snapshots whenever you want to do reporting of data up to that point, but that doesn't fall into the performance category.

So again, is there a performance advantage?

Answer 1

Using a database snapshot located on your production OLTP server will, in all likelihood, make performance worse. There are two main reasons:

1. Extra write overhead. Snapshots are copy-on-write, so this will obviously impact performance.

2. A snapshot will share pages from the old database on disk, but it uses its own memory in the buffer pool. If you have a large table, it will end up consuming memory for both the OLTP database, and the snapshot, even for the unmodified pages. You can end up with a lot of additional memory usage because of this, which can lead to more disk I/O if your server doesn't have loads of free RAM.

So if you're trying to make reporting less intrusive on only a single server, you're probably better off just querying the OLTP database and using WITH (NOLOCK) hints, and then reigning things in with the resource governor if needed. Beyond that, look into some kind of scale-out system (log shipping, mirroring, etc.)

Answer 2

The one performance advantage I can see of using DB snapshots is that Locking and blocking issues are considerably reduced when using a snapshot for reporting.