Agent makes SP slow
-
On my Win2003 / SQL2K Server, a stored proc runs some accounting calculations for various accounts. Depending of the size of the account and the numer of financial transactions, each account takes between 0.5 and 5 seconds to complete.
I scheduled the SP as an overnight job for SQL agent. Now each account takes between 0.07 and 23 seconds. It's amazing, relatively small accounts go much faster than in Query Analyzer, but large accounts take much more time. I can reproduce the effect.
I suspect it's some recompilation issue with SQL Agent. Any ideas ?
-
Are there other jobs running at the same time as this one?
If you suspect recompilation, you can use Profiler to trace if your stored proc is recompiling
Francis
-
Please check your showplan. I had similar problem due to the table scan.
-
See this. Sorry it's a bit late
-
Hmm, I have "SET NOCOUNT ON" but the stored procedure if is running from server agent like scheduled job, is more than 10x slower than if I run this from query analyser. Do you have any idea? Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply