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]