Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

intermittment slow data modification on sql 2008 r2 Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 8:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
Hi Folks,

I am working with sql server 2008 r2 standard (build 10.50.4000)

Occasionally (not all the time) we get slow data modifications (e.g 16+ seconds) for the following updates and inserts - see attached file for slow updates and inserts.

I have also attached the schema of the tables involved.


Running a blocked process report in profiler (threshold 4 seconds) come up with nothing when the data mods come up with slow response times

Wait stats show mirroring waits - but looking at the mirror history the mirror has been synchronized during the time when data mods have been an issue.

Is anyone able to advise what else I should be looking for here?


  Post Attachments 
slowmods.txt (6 views, 2.38 KB)
T_VechileNotification.txt (5 views, 5.89 KB)
T_VN_Index.txt (4 views, 675 bytes)
Post #1489981
Posted Friday, August 30, 2013 4:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 801, Visits: 715
Are there any triggers on the table? The INSERT statements themselves are plain vanilla, but there could be issues with trigger code.

But since you say mirror waits, I guess you have set up mirroring to be synchronous, which means that if there are delays in connection to the mirror or the I/O subsystem on the mirror that you will see delays.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490326
Posted Monday, September 2, 2013 12:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
thanks for your help!

No triggers on the tables.

Yes it is using synchronous mirroring.
Post #1490519
Posted Monday, September 2, 2013 1:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 801, Visits: 715
OK, if there are no triggers no the tables, we can exclude problems with query plans as such, but problem is related to the mirroring setup.

One way to resolve it is to give up on synchronous mirroring and use asynchronous instead. But that requires that you fully understand the consequences for disaster recovery - asynchronous mirroring means that there is a risk for data loss in case of a failover.

Another angle is to look at the context of these INSERT statements. I guess since they are problematic, that you are not submitting these in singletons a few times a day, but maybe you are inserting many rows at a time? In such, there is plenty of oppurtunities for improvement!


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490529
Posted Monday, September 2, 2013 1:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
As I said - I'm using sql server standard .. asynchronous mirroring is not avialable in that edition I believe (correct me if I'm wrong).

Can you clarify

Another angle is to look at the context of these INSERT statements. I guess since they are problematic, that you are not submitting these in singletons a few times a day, but maybe you are inserting many rows at a time? In such, there is plenty of oppurtunities for improvement!


I think you may have missed a few words so I am unsure what you mean

thanks!
Post #1490530
Posted Monday, September 2, 2013 2:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 801, Visits: 715
Sorry, I overlooked the edition issue.

What I tried to say is that while you could investigate what is going on with your hardware, you could also consider these INSERT statements. They insert a single row, and if there is only a single row to insert, there is not much to do about it. But since they were problematic, my suspicion is that there are more than one row to insert, and the INSERT statements are performed as part of a loop. And in this case there is certainly room for improvements.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490545
Posted Monday, September 2, 2013 2:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
thanks,

if it was more than 1 statement I wouldn't expect to see it multiple times in profiler? (i.e because the statement was in a stored proc I actually used the Audit Schema Object event to monitor those statements.

I am only seeing the statement once before the procedure ends.

Currently I am monitoring in profiler looking for statements greater than 5 seconds to see when the procedure fires again and also monitoring for status changes in mirroring
Post #1490546
Posted Monday, September 2, 2013 3:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 801, Visits: 715
I don't know your application, so I can only make speculations. Nor do I know why think you actually think this is a problem. That is, my assumption is that someone has reported a performance problem.

Keep in mind that even if the stored procedure is a single INSERT statement, the procedure may be called repeatedly.

And please observe that I don't anything - it is just that I have seen bad application patterns before.

And, of course, if this a third-party application there is not much you can do but bug the vendor.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490557
Posted Monday, September 2, 2013 7:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
yes that right we are getting complaints of timeouts. The stored procedure doing these inserts contains most of the longest running queries.


I was just asking that question
if it was more than 1 statement I wouldn't expect to see it multiple times in profiler?
to make sure I was not missing anything - I'm sorry if it was interpreted the wrong way.

This stored procedure does get called repeatedly. But I am guessing you are wondering if there a content issue. The thing is I ran a blocked process report in profiler (4 second threshold) for a extended period of time (at least a day) and when that procedure ran slow no blocked process report occurred. I did block a process in a dummy database on the db instance to make sure it was working ok.

Thanks!
Post #1490725
Posted Tuesday, September 3, 2013 1:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 801, Visits: 715
Please forgive me. I like to help you with your performance problem, but I only have fragmentary information and it is difficult to piece things together.

It seems that you have a longer stored procedure, and occasionally it runs for such a long time that the clients time out. By using Profiler you have found that the slow statements are two INSERT statements to insert a single row. What else is going on in this stored procedure? Could you post the code?



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490793
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse