Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2009-09-16 : 16:34:15
|
| Hi,As I understand it, this is how database snapshots work. A snapshot is taken of a database at a point in time. This snapshot is initially empty. If you want to perform a select query on the snapshot it runs the select on the source database. However if the source is modified after the snapshot is taken, the pre-modified pages are copied to the snapshot.So it seems to me that snapshots are only really useful to save space, is that correct? Even then when a lot of modifications have been made to the source, the snapshot grows bigger and bigger.They won't improve select query performance because you are essentially querying the source database a lot of the time? Unless the page being queried is in the snapshot.Are these points correct? I want to make sure my understanding of snapshots is right.Thanks |
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2009-09-17 : 15:22:02
|
| Yes, you are correct. |
 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-09-17 : 15:23:21
|
| More or less. Suppose you have a giant cube build query for your OLAP system. Do you want to let that run on the OLTP system potentially causing large amounts of blocking, or would you rather have that run against a snapshot?Suppose you have database mirroring set up. The mirror database is in a loading state, and inaccessible. However, you can take a snapshot of the mirror database, and open that up for reading, or even backups. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-09-17 : 16:24:18
|
| A snapshot can be used for reporting. Since it is read-only, there is no blocking contention between reporting and updates.You can also use a snapshot to restore the database to the point in time that the snapshot was created. This can be useful for testing to quickly rollback to an initial state after a test.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Database snapshots - what's the point?