ScottPletcher (2/9/2016)
Perry Whittle (2/9/2016)
Luis Cazares (2/8/2016)
DB Snapshots use the database as the main source of data and only store pages that get modified. That means that querying against the snapshot is probably going to impact the database.This article explains it: https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
The only time it's liable to affect the source database is when a query performs reads from disk to populate the buffer pool, disk I\O can be hit in this case.
Once in memory the page access is the same as any other database.
the way snapshots work is by using sparse files. After the point of the snapshot any database pages which change are copied to the snapshot databases sparse files to preserve the data from the snapshot point.
Every modification to the table data also has to be done twice, which naturally can also slow down main table processing.
done twice for what??
You modify a table and subsequently a page in memory and that's where it stays. If the page is flushed to disk and replacing the page on disk before the snapshot was taken then this original will be copied to the sparse file for the snapshot database. It's not going to be every page every time and actions aren't exactly done twice
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉