4

For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required.

Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs and mirroring, are there still performance penalties for this over not having a mirror and having the database in simple recovery? If so, what kinds of operations are effected and what causes them to be effected?

|improve this question
5

With an async mirror there should be little to no performance impact on the active system. When you are in full recovery there is more transaction log activity on the system than if the database was in simple recovery mode, but that's about it. Are you seeing specific waits or something that prompted the question?

|improve this answer
  • This is just study more than it is practical. How might the change in transaction log activity effect certain queries? – Kyle Brandt Mar 27 '11 at 17:06
  • It wouldn't unless your transaction log and data files are on the same disk (which they shouldn't be), and the query generated physical IO from the data file (which most don't). – mrdenny Mar 27 '11 at 17:08
  • Although the waits are negligible, IIRC I started to see WRITELOG after switching to the Full model, so if it is true that that is part of the Full model and not the Simple model I am wondering how that effects the lifetime of an Updates, Inserts, and Deletes (even if it only ads .5 MS). – Kyle Brandt Mar 27 '11 at 17:19
  • So I guess the question boils down to: Is there any difference in the write to transaction log and then write to the buffer cache process in Full vs Simple? – Kyle Brandt Mar 27 '11 at 17:42
  • The only difference should be that minimally logged transactions aren't minimally logged any more. I had a system which did ~150 IOs when in simple recovery. When I moved it to full and it jumped to ~450 IOs. In my case there were a lot of bulk inserts, which would explain it as those would have been minimally logged under simple recovery. – mrdenny Mar 27 '11 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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