July 6, 2004 at 3:28 am
I am working with SQL 7 service pack 4.0. and are running several stored procedures through Sql Server Agent Jobs to run at the weekend. Several of the stored procedures are running approximately 100 times slower when run through SQL Server Agents Jobs. Each stored procedure that has a problem uses cursors.
For example one of my stored procedures takes 3 minutes when run on SQL Query Analyzer and six hours when run through SQL Server Agents Jobs. I have run it through Query Analyzer on my own pc as well as through Query Analyzer on the server box.
Does anyone have any idea why this might be happening and any potential solutions? I know that cursors are slow but I don't understand why there should be a vast difference in speed between Query Analyzer and SQL Server Agent Jobs. To perform the data manipulation in SQL I need to use cursors.
Any suggestions would be appreciated.
Lee
July 6, 2004 at 5:43 am
Are they exactly the same stored procedures that you run in QA and via jobs?
If they are that rules out old query plans and a need for recompiling your procs.
My guess would be that the scheduled jobs overlap and the cursors are locking records.
I have one or two jobs that require record by record processing, but rather than use cursors I have a client machine in the server room that performs the manipulation off-line.
July 6, 2004 at 9:17 am
They are the same stored procedure that runs in QA and via jobs.
I have added 'with recompile' to the stored procedures but that has made no difference to the speed.
There are scheduled jobs that overlap but only due to the time they take to run via jobs. They are scheduled two hours apart and when run through QA they would finish in plenty of time.
Also the scheduled jobs are on different databases so I assume this would not affect them in this way?
I have tested a stored procedure by running it via jobs when nothing else has been running on the server yet it makes no difference to the speed.
If I cannot speed up the job in SQL then I will look into manipulating the data outside of SQL then loading the data in.
July 6, 2004 at 9:46 am
I suggest you run a Profiler trace to find out what is really going on. I have scheduled very complex stored procedures with no problems at all. The only difference though is that I am using 2000 and not 7 but I believe it shouldn't matter.
HTH
* Noel
July 16, 2004 at 9:04 am
I had the same problem with sql server 2000.
I solved it with SET NOCOUNT ON.
If NOCOUNT is OFF, every time sql server executes a statement it returns the number of rows "touched". If the cursor fetch thousands of times, the messages could be a huge quantity.
The job receives the messages and does'nt stop, but it slows down...my job is three time faster with SET NOCOUNT ON
I hope it will work.
stefano
July 20, 2004 at 5:08 am
I have been running some tests with SQL Server 2000 on a test server which isn't as powerful as our server boxes with SQL Server 7.0 and while the stored procedures take twice as long with SQL 2000 in QA they are running at ten times quicker in jobs with SQL 2000.
Now I set NOCOUNT to ON the jobs are running three to four times quicker in general then with NOCOUNT set to OFF. One stored procedure is running nine quicker with NOCOUNT on rather then off. This works for both SQL 2000 and SQL 7.0.
Thanks for all your help, especially Stefano with the NOCOUNT advice as it has worked a treat.
Lee
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply