September 4, 2013 at 3:01 pm
I am new to service broker. One of our databases uses service broker on SQL Server 2008 R2. A Stored Procedure makes use of service broker. And the procedure is called nearly 100 times.
Now the problem is that the procedure execution completes very quickly in 13 seconds but I am able to see the inserts to the main table occurring for a very long time. For nearly 6 hrs after the procedure execution completes.
Can someone shed some light on this ? Do I tune the query/change the send or receive queue number/tune the table ?
September 4, 2013 at 4:09 pm
Lots of pieces missing here. You say that the procedure completes in 13 seconds, and then there are INSERTs going on six hours later.
Should I guess that the procedure you talk about post messages on Service Broker queues, and then there is an activation procedure which processes the messages?
I would guess then that the activation procedure needs tuning.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 5, 2013 at 11:48 am
Yes the inserts takes 6 hours to complete.
Basically I want to know how to use more than 1 dialog to make use of parallel processing.
September 5, 2013 at 1:18 pm
You change a MAX_QUEUE_READERS parameter to start more activation procedures.
http://technet.microsoft.com/en-us/library/ms189529.aspx
But are you sure that it will speed up and actication proc don't need a tuning?
September 5, 2013 at 1:33 pm
We have tuned the procedure now. It is being serially processed now. We need parallelism, and for that I believe we need more than one dialog. Not sure how to create more dialog.
September 5, 2013 at 4:01 pm
Sanz (9/5/2013)
We have tuned the procedure now. It is being serially processed now. We need parallelism, and for that I believe we need more than one dialog. Not sure how to create more dialog.
Since I know next to nothing about your system, I can't how you can get more dialogs. However, as e4d4 said, you can change to the queue to have more readers. Be aware, though, that having multiple queue readers means that you cannot save data between messages, particularly not if you roll back and make a new attempt, because another queue reader may get the message.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply