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 Tuesday, September 3, 2013 2:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
If you're seeing mirroring-related waits, check the latency of the line to the mirror, make sure that nothing's wrong, check write throughput on the mirror's log drive. The state will be synchronised (SQL's spending a long time on the inserts to ensure that), it'll only switch to synchronising if it falls way behind.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490799
Posted Tuesday, September 3, 2013 2:20 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
Its all good - I appreciate any advise you can give.

Anyway I sent you two procedures because I think our archiving process might be involved with causing the issues. Apparently it runs 24/7 so I spoke to the vendor about limiting its run time. I was concerned that the mirrored archive database was interfering with the day to day transactional stuff -but I'm not exactly sure if that is the issue because I would of expected something to show up with the blocked process report when I run it earlier.

So after discussions today with the vendor I have decided to remove mirroring from the archive database. It is a bit early to tell but it so far 1 have only got 1 query/stored proc in the last 8.5 hours that is running slow (over 5 seconds).

Anyway the procedures are attached
insertvehiclenotification.txt is the stored proc that contains those sql statement I originally sent.
movearchiverecordtotable.txt is the proc that move data out of the live table into the archive tables.

Let me know if I can help you/help me any other way


  Post Attachments 
insertintovehiclenotification.txt (4 views, 67.86 KB)
movearchiverecordtotable.txt (3 views, 101.65 KB)
Post #1490802
Posted Tuesday, September 3, 2013 2:36 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
I forgot to mention a couple of other things.

My infrastructure guys tell me they see no problem with the network link between principal and mirror.

A few times I continously pinged the mirror box (for about an hour at a time) from the principal most of the time the return trip time was less than 1ms but around the time my procedure ran slow I got a couple (no more than 5) return trips around the 25-35ms. Because there was only a couple of slow rtts infrastructure did not think it was an issue.

A few times last week our System Center Operations Manager system complained that databases on the suspect database instance had changed there mirroring state from synchronized to synchronizing. Gilamonster mentioned that the management studio will not show a synchrozing state unless the databases are a lot out of sync - would it show synchronizing if the database where out of sync for more than 10 seconds because sometimes my proc can run for more than 15 seconds.
Post #1490810
Posted Tuesday, September 3, 2013 2:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
djordan 4543 (9/3/2013)
Would it show synchronizing if the database where out of sync for more than 10 seconds because sometimes my proc can run for more than 15 seconds.


Unlikely. Synchronous mirroring means SQL tries to stay synchronous, that means waiting for log commits on the mirror. Falling back to synchronising means it's failing to do what it's required to do.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490812
Posted Tuesday, September 3, 2013 2:01 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 786, Visits: 690
The plot thickens...

Anyway, the stored procedure had quite a lot of INSERT statements, so why would only these two be slow? If it's a problem with mirroring, it could happen anywhere in the procedure one would think. Unless these tables are on a particular filegroup, but I doubt since they are on PRIMARY.

Anyway, your ping times indicates that there is a connection to the mirror. Maybe because the archival procedure helps to consume the bandwidith. The funny thing is that this one seemed to work one row at a time (but the code was about unreadable), which inefficient, but lean on resources.

As for the procedure InsertIntoVehicleNotification, permit to try to what explain what I was getting at earelier. Say that the client needs to insert 30 notifications. Apparently then it will call the procedure 30 times. It if it was rewritten to accept a table-valued parameter, all 30 rows could be inserted at once. Of course, if this is a vendor app, there is little you can do, but to bug the vendor.

I also note that there is no transaction in the procedure, but maybe the client takes care of that.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491081
Posted Tuesday, September 3, 2013 5:05 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
To be totally honest once I found those couple of insert statements being slow in the stored procedure I concentrated on why they are causing slowness. Perhaps if I watched profiler for a bit longer I would see other data mod t-sql in that proc being slow too.


We are using a vendor app.

You comment about using a table-valued parameter sounds good. But are table-valued parameter persistent across procedure calls - I thought no. If they aren't how would that work?
Post #1491116
Posted Wednesday, September 4, 2013 1:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 786, Visits: 690
Yes, the UPDATE statements could also be slow, but the INSERT statements may cause page splits etc, why they are more prone to stalls.

No, TVPs to not persist between calls, but nor is there any reason to. Anyway, that discussion is immaterial for you, since you cannot change the application. (But you could kick the vendor in the rear parts for not cleaning up his act.)

Anyway, it seems clear that there is an issue with your mirror or the connection to it. That is, either the network connection to the mirror is flaky or has too little bandwidth, or the mirror has problems with the I/O subsystems. The ping times you noted hints that the network is the problem. 25 ms is far too long for a LAN connection. But it seems you have a difficult battle with your infrastructure guys.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491158
Posted Wednesday, September 4, 2013 5:27 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
Since I removed mirroring from the archive database the queries are performing much better. Thanks heaps for helping me out with that!

I do have a question though (just to understand a point you made earlier)

you mentioned the problem stored proc could be rewritten to accept a table-valued parameter so that it could do bulk inserts rather than singleton ones.

If table valued parameters are not persistent from one sp call to another how could using them help with my issue (as there would be new data in the variable every time the proc was called?

Anyway thanks again!
Post #1491585
Posted Thursday, September 5, 2013 1:20 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 786, Visits: 690
djordan 4543 (9/4/2013)
Since I removed mirroring from the archive database the queries are performing much better. Thanks heaps for helping me out with that!


But the root problem appears to be that network or I/O subsystem is not able to sustain the load.

you mentioned the problem stored proc could be rewritten to accept a table-valued parameter so that it could do bulk inserts rather than singleton ones.

If table valued parameters are not persistent from one sp call to another how could using them help with my issue (as there would be new data in the variable every time the proc was called?


Not sure that I understand your concern. It is just like any other parameter. The scalar parameters you have to day to persist between calls, but the caller have to provide values every time.[/quote]

The point with using TVPs is that there would not be 30 calls or whatever a loop, but a single call to insert 30 rows. There would be fewer write operations. Even if each write operation would include more data, there would be less overhead per row.



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

Add to briefcase ««12

Permissions Expand / Collapse