Solved

SS 2012 Synchronous Mirroring Performance Issue

Posted on 2013-06-06
23
373 Views
Last Modified: 2013-06-24
Hi

We have a SQL Server 2012 SP1 instance running 1 database (plus the system ones of course).

This database is mirrored to a DR server that has the exact same configuration, layout etc as the production one. It is also log shipping to another location.

Our users have reported a performance issue to us that became apparent when mirroring was turned on. They have provided the following SQL for testing purposes;

SET NOCOUNT ON

declare @SequenceTx int
declare @SequenceRx int
 
DECLARE @count INT
USE Risk

SET @count = 0
 
CREATE TABLE tempConnect
(
       ConnectionId int primary key,
       SequenceTx int,
       SequenceRx int
)
INSERT tempConnect VALUES(1, 1, 1)
SET @SequenceRx = 0
 
WHILE (@count < 12000)
BEGIN
       SET @SequenceRx = @SequenceRx + 1
       
       UPDATE tempConnect
              SET
                     SequenceTx = @SequenceTx,
                     SequenceRx = @SequenceRx
              WHERE ConnectionId = 1
 
       SET @count = (@count + 1)
END
 
DROP TABLE tempConnect

Now - when mirroring was disabled, the above would take between 2 and 4 seconds to run. Now that it is enabled, it is taking anything up to 11 minutes to run.

I have run a trace in SQL Profiler which is reporting the following;
CPU - 1342
Reads - 24341
Writes - 5
Duration - 647201

I then paused mirroring and re-ran the SQL with the following reported;
CPU - 656
Reads - 24352
Writes - 28
Duration - 5657

Please can anyone shed any light as to what is going on? I cannot see anything that would explain this behaviour.

Many thanks in advance
Sue
0
Comment
Question by:rdbconcepts
  • 12
  • 7
  • 2
23 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
When synchronous mirroring is activated, the transaction on the source can not be committed until it is created committed on the mirror.  Many things may slow down the process:
> The network
> The disk configuration on the destination
> The CPU consumption on the destination

You may want to check these out for a start...Yes an please make sure your stats are up to date on the source.
0
 

Author Comment

by:rdbconcepts
Comment Utility
Hi
Everything is appearing normal - the network is good, disk config is the same and the cpu is around 5% on the source and 1% on the destination.

Stats are updated weekly, so that wont be an issue here - and given it takes seconds when mirroring is off, it wouldn't make sense for that to be the issue.

What we need to understand is how the mirroring is treating the SQL being run and if there is anyway that we can change it?
thanks
sue
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<What we need to understand is how the mirroring is treating the SQL being run and if there is anyway that we can change it?>>
It is hard not knowing what the SQL looks like.  Perhaps you could do a separate testing by creating  separate table and seeing the difference on a simple operation such as creating a table, inserting rows and querying something out of it.  Also check whether everything works well on the the streaming part: double check IP addresses: try changing listener port...What kind of AG are we talking about ? Do we have single storage or separate storage ?
0
 

Author Comment

by:rdbconcepts
Comment Utility
The SQL is in my original post...

what is AG??

Everything is working fine with the mirroring - except it has introduced a very major performance hit.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
AG:Availability group
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
Comment Utility
Since you are implicitly creating 12000 transactions you have more than 24000 transactions to commit and 12000 round trips between the source and destination.  Try testing using one global transaction with one single commit (2 commits in total) and one single round trip to confirm.
0
 

Author Comment

by:rdbconcepts
Comment Utility
I don't think we have an AG - unless there is a default one?

We are running mirroring (rather than always on functionality, though I appreciate in 2012 they are linked) without a witness server.

I wondered if that was the case - but couldn't tell from the output on the profiler or explain plan if it was doing them individually or in one lump.

Could I ask you how I would do this as one commit? Im not normally involved in SQL writing so am not sure how to change it?

Many thanks
Sue
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
I don't think we have an AG - unless there is a default one?
You can not have a mirroring without at least one availability group.  Somebody necessarily created one.

Could I ask you how I would do this as one commit? Im not normally involved in SQL writing so am not sure how to change it?
you can create a table a fill that table based on the content of a table or a view.  For instance:

select into test_table (test table)
from sometable (existing table in the schema)
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
I wondered if that was the case - but couldn't tell from the output on the profiler or explain plan if it was doing them individually or in one lump.
Profiler outputs are session based.  They won't show transaction level operations.
0
 

Author Comment

by:rdbconcepts
Comment Utility
Is there a way to see transaction level operations?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Is there a way to see transaction level operations?
Yes but complicated and difficult to describe online.  That is why I suggested you take a simpler approach by running a single global commit and use profiler to cheek the differences as you are now.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<You can not have a mirroring without at least one availability group.  Somebody necessarily created one.>>
Sorry I was wrong on this.  It is actually is still possible to set up mirroring without AG but it will probably be deprecated in SQL 2014.  What is your mirroring topology ?  The probable reason of the slowdown is the huge number of commits made on the two nodes.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
How big are your transaction logs on both sides? How many VLFs do they have?

As a recommendation, if you have SQL 2012 Enterprise edition you should use Always On. It is not that will fix your sync lag, but for sure is a better solution.

One thing that is AO does is that allows 4 replicas of the same principal and they can even be used as read only. Classic mirroring doesn't allow more than 1 replica and that is not available being in sync(restore) mode all the time. This way you would not need to use the log shipping at the same time unless you need more than 4 replicas.

Obviously something delays the commit on the mirror and the response back to the Principal. If you want to monitor something you should do it on the mirror. Also the fact that you use log shipping at the same time might be an issue. It could be the shared location of the log files or maybe they are big at times, who knows.

How is your fragmentation on indexes? Are you running regular optimizations? If not you should.
0
 

Author Comment

by:rdbconcepts
Comment Utility
Hi
The transaction log was quite big, so we have shrunk that down to 500m and will keep an eye on its growth.

They have 1 TL file on each side. But we are looking to see if we can add another - will the log shipping be able to cope if we do?

They unfortunately have SQL Server 2012 Standard edition, so we cant use asynchronous or the Always On features.

Indexes are reorganised every week - but we can increase that if needed.

Racimo - we put a begin transaction/commit transaction around the SQL and the job ran in seconds - but I am not sure the client is able to change the code (as it may be a 3rd party app), so I am waiting to hear back from them.

Thank you both for your assistance - I will update with progress when we have some.
Sue
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
You don't need to shrink the transaction log, unless really is too big and free space is too low, just make sure that the VLF are kept under control. What I meant was how big are the transaction log backup files.

In regards to the index optimizing jobs i recommend you to look for fragmentation and only apply rebuild and reorganize based on that. If you move the frequency to daily then the indexes won't get the time to become too fragmented. A full rebuild every time is not needed if an index is fragmentation is not high enough.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
- we put a begin transaction/commit transaction around the SQL and the job ran in seconds
As I mentioned earlier, the less commits you will have in a statement, the less round trips will occur between the servers by synchronous mirroring design.  If the reduction in time is confirmed, it would then seem the transactional overhead of the mirroring might explain the difference in time.

but I am not sure the client is able to change the code (as it may be a 3rd party app), so I am waiting to hear back from them.
Good luck with that.  

Hope this helped.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Obviously something delays the commit on the mirror and the response back to the Principal.
Not as obvious as meets the eye.  The statement ran shows there is very important number of commits occurring in a very short period of time in auto commit where each UPDATE statement generates a transaction even though they are on the same session.  Since mirroring is transaction-based, the overhead of synchronously mirroring all these transactions may become cumbersome.  If one session triggers 12000 commits (hence 12000 round trips between the servers and 24000 commits in total) the time shown for the entire session should be no surprise.

@Sue
Appart from declaring explicitly the transaction boudaries, you can also ask your vendor if he can activate the MARS feature in the connection chain by forcing batch commits.  

For more info please read:

Batch Scope Commits

Enabling MARS.

Hope this helps.
0
 

Author Comment

by:rdbconcepts
Comment Utility
I have accepted this solution as it was proven to work - however, we have not been able to implement it as there are restrictions on what can be done with the code.
I am told however the application has a method of controlling mirroring performance (presumably with a similar wrapper but I haven't had any information on this) so they are going to continue with that.

Thank you all for your help and advice.
Sue
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
I have accepted this solution as it was proven to work - however, we have not been able to implement it as there are restrictions on what can be done with the code.
So I get a B for answering exactly the question asked and proposing a solution but you can't implement it because your application vendor does not accept it.  Perhaps, mentioning that restriction in the question by specifying the kind of solution that your application vendor would accept would have helped me get an A.

In invite you to read the following link about grading.

http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-

it says

A should be the default grade awarded unless the answer is deficient. An A grade means the solution provided is thorough and informative or is a link to information that answered the question. Any links that are posted will be accompanied by a summary of what can be found there and how it helps solve the problem.

The answer was not deficient and solved the issue as asked.

I am told however the application has a method of controlling mirroring performance (presumably with a similar wrapper but I haven't had any information on this) so they are going to continue with that.
Right.  I would be curious to know which one.

Thank you all for your help and advice.
Glad we could help.  Good luck with this issue.
0
 

Author Comment

by:rdbconcepts
Comment Utility
Sorry - I was unaware of this. based on those definitions please change it to an A.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now