Sql-server – Advantage of using a database snapshot for reporting purposes

performancereportingsnapshotsql server

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?

Best Answer

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

Extra write overhead. Snapshots are copy-on-write, so this will obviously impact performance.
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.)

Related Solutions

What methods are available to create a snapshot of an oracle database for testing purposes

If with "export/import" you mean the "traditional" exp and imp commands, than I'd say there is a better solution:

expdp and impdp are much faster and much more flexible when copying a snapshot of a database (or single schema).

If the database is not huge (i.e. Terabytes) then expdp and impdp is the easiest way to achieve this. They are easily scriptable and can be started using SQL also. So no commandline access to the servers is needed (provided the dump files are stored on a shared storage).

Sql-server – How to query for existing database snapshots in SQL server

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new.

IF NOT EXISTS(
    SELECT 
        * 
    FROM 
        sys.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
END

Best Answer

Related Solutions

What methods are available to create a snapshot of an oracle database for testing purposes

Sql-server – How to query for existing database snapshots in SQL server

Related Question