Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

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.)

share|improve this answer
    
I agree that snapshot can make things worse, but if you are using 2008 R2 the hint NOLOCK does nothing to help OLTP performance when you are reporting. stackoverflow.com/questions/15770402/… –  James Jenkins Apr 29 '13 at 13:47
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.