SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


intermittment slow data modification on sql 2008 r2


intermittment slow data modification on sql 2008 r2

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221027 Visits: 46279
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, MVP, M.Sc (Comp Sci)
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


djordan 4543
djordan 4543
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 251
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:-)
Attachments
insertintovehiclenotification.txt (8 views, 67.00 KB)
movearchiverecordtotable.txt (10 views, 101.00 KB)
djordan 4543
djordan 4543
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 251
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221027 Visits: 46279
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, MVP, M.Sc (Comp Sci)
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


Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5138 Visits: 875
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
djordan 4543
djordan 4543
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 251
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?
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5138 Visits: 875
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
djordan 4543
djordan 4543
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 251
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!
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5138 Visits: 875
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search