0

I've written a stored procedure that takes 15 min when executed from Management Studio. When it's activated from Service Broker, however, after 4 hours it hasn't done even half of its work.

Are there known performance issues when running SPs from Service Broker? (Perhaps Service Broker runs the SP inside a transaction and Management Studio doesn't?)

I'm using SQL Server 2005.

Update:

It appears the problem was executing a stored procedure from another stored procedure. More specifically, I have a stored procedure which receives an operation (export or delete). This SP then calls the respective SP based on the operation (one has an ETL process, the other deletes data). Forcing recompile on these SPs seems to have fixed the problem. I wonder if SQL Server should make an action plan for each sub-SP though, independent of the SP that's calling them. In that case, no recompile would be needed.

noup
  • 738
  • 2
  • 11
  • 19

1 Answers1

1

I don't know about Service Broker, but for general stored procedure troubleshooting check out the suggestions given at this question. They helped me a lot to figure out some problems with my stored procedures.

You can take a look at what the stored procedure is doing with the WhoIsActive routine, you can acquire the query plan and study if there is any difference with the query plan when executed in Management Studio, you can experiment with the OPTIMIZE FOR hint to eradicate parameter sniffing...

(Parameter sniffing is that the query plan is generated differently when other parameters are supplied. Is Service Broker passing the same parameters to your SP as the ones you pass in Management Studio?)

Good luck and please post your findings here if you are unsuccessful.

Community
  • 1
  • 1
littlegreen
  • 7,290
  • 9
  • 45
  • 51
  • Out of nothing, the procedure started running fast from Service Broker too. I don't know what (automatic) change caused this. The parameters in Management Studio are the same. Couldn't make WhoIsActive return anything, although Who and Who2 do return results. – noup Nov 12 '10 at 15:34
  • Yeah, my SP's also slow down and then go faster 'out of nothing'. And then in a month, the same slow behaviour is there again. It is annoying, and I still haven't pinned down all the causes, but at least now I now where to start looking - and you too. I'd like to hear about your experiences when you have such a problem in the future, we can learn from eachother. Just drop a line here. – littlegreen Nov 23 '10 at 10:46
  • Happened again, this time with a query that takes 9 seconds when ran manually. Some notes: I'm not using transactions; the process has "Background" status in sp_who2; as mentioned on my update, I have an SP1 which calls an SP2 multiple times (on for each export that needs to be done) - slowness came on third call to SP2 this time. – noup Nov 23 '10 at 18:20
  • Can you try to use OPTIMIZE FOR after each query in SP2 to make sure the SP compilation is optimized for the parameters for the first call, and not the third call? – littlegreen Nov 24 '10 at 10:17