x

Mirroring significantly degrades ETL process performance

I have an accounting system which we recently mirrored to our DR centre. Before this the users had an ETL process that extracts key data for analysis and takes about 3 or 4 minutes to complete. Since mirroring has been implemented this has increased to more than 150 minutes.

Nothing seems to be stressed - the network is fine, the SQL Servers are fine, CPU doesnt peak, disk access (read/write/transfers per sec) on both servers is active but not at all high.

I know the ETL process uses cursors.

When running there is a peak of activity (Send Rate 1800KB/s New Trans 1800KB/s)) on the Primary followed immediately by the corresponding activity on the mirror (Restore Rate 1800KB/s as the process starts and then the send rate drops to ~ 50KB/s and New Trans rate of ~ 50KB/s and the mirror has a Restore Rate of the same ~50KB/s.

If I suspend mirroring before the process starts the process takes the normal 3 or 4 minutes and then I resume mirroring and it catches up in about 6 minutes. The Send Rate rises to 7-800KB/s and the Restore Rate matches is at 800KB/s.

Mirroring is Sync as it is on SQL Server 2005 Std Edition.

I have noticed that Log Flushes and Log Waits rise when mirroring is active and the ETL process is executing. No other metrics seem to rise noticeably.

Anyone have any ideas why and how I might be able to resolve this please?

I am almost at the point of creating a method for the users to suspend and then resume the mirroring themselves.
more ▼

asked Sep 11 '12 at 02:30 PM in Default

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

suspending the mirror in live seems dangerous - you are losing your resilience whilst the ETL runs
Sep 11 '12 at 07:47 PM Kev Riley ♦♦
yes but needing to refresh 6 times a day is only possible when it doesnt take over 2 hours. The vulnerability is for less than 10 minutes and the risk is in the ETL target which is wipe + write as far as I understand it so a failure just means we re-execute the job. After we get over the disaster.
Sep 11 '12 at 09:43 PM Fatherjack ♦♦
Is the ETL Process also 3rd party? Sounds like that could get pushed off onto another box or re-written into a proper ETL process.
Sep 12 '12 at 10:58 AM WilliamD
yes, it's all 3rd party code baked into the application. I am describing it as ETL as that is how I believe it works, actual purists may have alternative term for it.
Sep 12 '12 at 11:00 AM Fatherjack ♦♦
Is the ETL the only thing that runs on this db? If so, it doesn't seem that mirroring is the right approach for DR. If not, are you going to prevent 'other' transactions from running whilst the ETL takes place?
Sep 13 '12 at 07:28 AM Kev Riley ♦♦
show all comments (comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Where I generally see this scenario is when the transactions do processing in the same database; work tables and the like.

If this can be offloaded to another dB and only the final results applied you will enhance performance. The issue is 2PC and that is why the throughput is reduced... the primary is waiting on acknowledgements of many small transactions and this time adds up.

Check for ETL prep work going on in the Primary.

EDIT

---------- Your other option is to move to Log Shipping rather than the suspend/resume route. It requires manual failover but the contention is probably removed and you don't have to worry about others forgetting steps that could compromise your recovery objectives.

Either way you are in a tricky spot.
more ▼

answered Sep 11 '12 at 03:19 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

bah, 3rd party app - I get no access to configure internals. I figured it was lots of chatter but was hoping for confirmation so thanks for that.

off to build some sort of UI to allow users to suspend/resume mirror - they want to do the ETL 5 or 6 times a day.
Sep 11 '12 at 03:29 PM Fatherjack ♦♦
+1. This kind of technique is used by us anyways especially with either BULK-LOGGED OR SIMPLE recovery model staging databases.
Sep 11 '12 at 03:31 PM Usman Butt
Have you checked the perfmon counters for mirroring?
Sep 11 '12 at 04:01 PM sp_lock
@sp_lock - not actually looked at the perfmon ones, will take a look next time it runs. Thanks
Sep 12 '12 at 10:45 AM Fatherjack ♦♦
Log shipping is a possibility but wouldn't provide any better DR protection than a suspend/resume of the mirroring! Yes, it's a PITA!
Sep 12 '12 at 04:53 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Too bad it is the 3rd party process. But as Kev Riley rightly said, this could be dangerous. The matter could get more serious, if a GUI is to be made to suspend and resume (could pile a heck of log records/ bigger send queue with the possibilities like if someone forgot to resume it in time). So to me, the suspend and resume should be starting and ending points of the ETL process respectively.

Moreover, one cannot be sure that if this process is taking 3/4 minutes now, then the execution time will remain the same especially when CURSORS are used. Believe me, I had a terrible time while dealing with a job having multiple cursors. So the ten minutes cushion may rise to 30 minutes in a bad case and the data loss could be bigger in magnitude. Which may not be allowed according to the DR policy.

I do not know whether it is possible or not, but the best would be to involve all the stakeholders and if a change in 3rd party code can do a better job, then it should be the course of action.

I know this is an awkward situation, but suspending/resuming process should be the last resort IMHO. And if is implemented, it should be monitored carefully and documented fully in DR policy to inform all the stakeholder about the risks involved.

BTW, I would be very interested to know how it is going to be documented. If you can please share some basic info here OR on my email, I would be very thankful to you.
more ▼

answered Sep 12 '12 at 01:13 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Use High Performance Mirroring, i.e switch the Db to Async Mirroring mode.
more ▼

answered Apr 30 '13 at 09:15 AM

geoched gravatar image

geoched
0

OP is using standard edition - you need Enterprise for Async
Apr 30 '13 at 09:25 AM Kev Riley ♦♦
Yeah, switching to Async involves switching to license from Standard to Enterprise
Apr 30 '13 at 09:40 AM Fatherjack ♦♦

Then try code wrapped in Begin.Trans Commit.

This should stop having to go across network for each commit .
Apr 30 '13 at 10:07 AM geoched
Its a 3rd party app. I have no access to the code. the question is moot now anyway, I solved the issue months ago. Thanks for your input though
Apr 30 '13 at 11:36 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x242
x57
x42

asked: Sep 11 '12 at 02:30 PM

Seen: 1139 times

Last Updated: Apr 30 '13 at 11:36 AM