September 3, 2013 at 5:05 pm
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?
September 4, 2013 at 1:23 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 4, 2013 at 5:27 pm
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!
September 5, 2013 at 1:20 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply