February 16, 2016 at 11:46 pm
Hi All,
I have some questions regarding snapshots on mirror which has been raised by my colleague as well , would be grateful for any guidance or pointers on this.
His understanding is as follows :
A snapshot when created only holds physical database pages that have been updated since the snapshot has been created.
Queries can be run on the snapshot databases.
We are seeing a number of instances where this scenario effects performance on the PRIMARY database that is being mirrored against which the snapshot is created.
Logically I believe the following is true but I would like confirmation and some idea of how this can be quantified.
1. Every snapshot that exists on the SECONDARY server generates an overhead on the PRIMARY server by extending the two phase commit to write additional information to any snapshot database. Can Microsoft confirm and quantify this overhead?
2. Any query run against a snapshot database on the SECONDARY that requires access to unchanged data accesses the data on the PRIMARY database. If this is how it works presumably it will create some shared locking to read the data which will have a de facto impact on processes on the PRIMARY server. If this is the case can this also be quantified and can this locking be removed totally by using (NO LOCK) or setting a particular Transaction Level on the snapshot database?
Cheers
Cheer Satish 🙂
February 17, 2016 at 1:38 am
1) BoL does say there's overhead, and it can be quite nasty. It's IO-subsystem related, so moving snapshots (or the source DB) to a faster drive may help.
2) No, there are no locks used. Unchanged data is read from the source DB, data that has been changed in the source is read from the snapshot. A snapshot is read only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2016 at 4:06 am
Thanks For your Quick Response.
Also observed the snapshot creation\recovery goes up significantly if indexes and tables created on source database.
Is it good idea to leave snapshot on secondary site open for 24 hours , does this additional overhead on primary server if data is been loaded on primary ?
Cheer Satish 🙂
February 17, 2016 at 10:57 am
Satish Nagaraja (2/16/2016)
His understanding is as follows :A snapshot when created only holds physical database pages that have been updated since the snapshot has been created.
This is not entirely correct. The snapshot database uses a set of sparse files that hold copies of modified pages but in their original state at the time the snapshot was taken. For example
A snapshot of database BicyclesUnlimited is taken at 15:20pm on the thursday afternoon. The data page 6:1:37800 has only 1 row of data at slot 1 and it identifies a bicycle wheel in stock at £31
At 15:25pm on the Thursday the stock is updated and the bicycle wheel price has been updated to £33. The row at slot 1 on page 6:1:37800 now reflects the new price. What is copied to the snapshot file is the original version of the page showing £31 as the price
Does this make sense?
Satish Nagaraja (2/16/2016)
We are seeing a number of instances where this scenario effects performance on the PRIMARY database that is being mirrored against which the snapshot is created.
if many pages are being updated regularly then disk I\O can be affected but each page is only copied once for that particular snapshot
Satish Nagaraja (2/16/2016)
1. Every snapshot that exists on the SECONDARY server generates an overhead on the PRIMARY server by extending the two phase commit to write additional information to any snapshot database. Can Microsoft confirm and quantify this overhead?
The 2 phase commit only affects the transaction log. When synchronised and in synchronous mode, the 2 phase commit ensures that each transaction is hardened to the mirrors tlog, the redo to in memory pages comes later, these later again may be updated to disk. There's a latency here. The copy on write will move the original pages to the snapshot sparse file as they are updated
Satish Nagaraja (2/16/2016)
2. Any query run against a snapshot database on the SECONDARY that requires access to unchanged data accesses the data on the PRIMARY database. If this is how it works presumably it will create some shared locking to read the data which will have a de facto impact on processes on the PRIMARY server. If this is the case can this also be quantified and can this locking be removed totally by using (NO LOCK) or setting a particular Transaction Level on the snapshot database?Cheers
The ALLOW_SNAPSHOT_ISOLATION database option is set ON regardless of the setting of this option on the source database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply