SQL Server Performance Forum – Threads Archive

Help troubleshoot why running stored procedure fro

Hi Folks, I’m looking for help to troubleshoot why running stored procedure from SQLAgent takes MUCH longer than from Query Analyzer. The stored procedure I’m running usp_FinanceBuildLocalTables, takes about 9 hours when run as a sqlagent job, but about 25 minutes when run from Query analyzer. The stored procedure usp_FinanceRefreshPortfolio uses a CURSOR to fetch all records in a table, and calls another stored procedure using cursors to iterate through and manipulate the data for that record (using more stored procedures). QUESTIONS: * Why would the SQLAgent job run so very much longer and what can I do to shorten it?

First step to check recompile all the stored procedure & triggers that are involved in this usp.
Check the exectuion plan on query analyzer for that SP and see whether (suitable) indexes are used.
WHen you’ve scheduled this job make sure what kind of other processess are running/scheduled on SQL Server.
Refer to the tips section about fine tuning cursors & Stored procedures in the database. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>