up vote 1 down vote favorite
share [g+] share [fb]

I have 2 servers set in Mirroring High-safety. One is Principal and another in Mirror. Currently I have 2 snapshots of a Production database (100 GB size) created on Principal server (for no_lock purpose of massive select processes) and 2 snapshots on the mirror server for the same database for reporting purposes.

I know snapshots reduce performance of source databases but I am not sure if snapshots from mirror server have any impact on principal server's performance.

thanks,

link|improve this question
feedback

2 Answers

up vote 3 down vote

Snapshots do not directly affect the mirroring session. To allow the principal to continue, the mirror must harden the log, not commit. This simply means the received log from the principal must be written and persisted into the log files.

It is the recovery process that continuously applies the log to the mirror that will have to handle the copy-on-write required to maintain the snapshot, when it applies the changes in the log to the data. This process can lag behind the principal, it is not required to be up to the current principal LSN position.

That being said, maintaining the snapshot does incur IO, Memory and CPU overhead, which will reflect in the overall performance of the mirror hosting instance. There will also be the additional query workload that will be supported by the snapshot. So obviously, in this sense, having a snapshot causes overhead on the mirror which diverts resources from the task of keeping up with the principal (receiving, processing and hardening the log). But it is not true that a snapshot incurs direct overhead. The principal does not have to wait for the copy-on-write required by the snapshot to occur.

link|improve this answer
Along with that, the load that the query operations on the snapshots will impact production, as they slow down the disks, slowing down the rollforward on the mirror, which then slows down the ACKs coming back from the mirror to the active instance. – mrdenny♦ Jan 27 '11 at 2:57
+1 for this good explanation of how mirroring works. – yrushka Jan 27 '11 at 9:26
feedback
up vote 3 down vote

My guess is that yes it would,but I cannot be certain unless I test it out for myself. BOL looks like it shares my opinion, but in vaguer terms!.

Any pages on the mirror that change need to be copied to the snapshot file first. Not sure how optimised the process is & how much more performance degradation you get when you create a 2nd snapshot.

From BOL:

Depending on the configurations of the mirror server and principal server, having an excessive number of database snapshots on a mirror database might decrease performance on the principal database.

link|improve this answer
my thinking too: maintaining the snapshot will affect the transaction of the mirroring process – gbn Jan 26 '11 at 13:05
thanks, I thought that too, just needed a second opinion ! – yrushka Jan 26 '11 at 13:51
Remus is right bout high safety meaning hardening of the log. It's NOT a 2 phase commit like DTC. – Nick Kavadias Jan 26 '11 at 23:50
feedback

Your Answer

 
or
required, but never shown
discard

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