July 19, 2013 at 9:09 am
I am having some performance issues when a stored procedure is executed going through a broker-based request.
The SP is doing a simple join-based update.
Sometimes it will update 1M rows in under 5 minutes.
When it is bad the same update for 300K rows will run for 6 hours.
There appear to be no other processes competing for resources on the server.
CPU usage remains constant during this time at about 30-35 %.
SQL Wait Stats during the execution time are high for OLEDB, BROKER_EVENTHANDLER & BROKER_RECEIVE_WAITFOR.
Any ideas greatly appreciated.
July 19, 2013 at 4:12 pm
To little information given to say anything particularly useful. The fact that Service Broker is involved is not a very important piece of information. The BROKER wait types you see are typical wait types for Service Broker application. OLEDB is more interesting, as it indicates that a linked server is involved.
As a start, you could post the procedure.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply