SQL Server Agent Job Running Slow

Question

I am executing a stored procedure using SQL Server Agent Job in SQL Server 2005.

This job was running fast until yesterday. Since yesterday this job is taking more than 1 hour instead of 2 mins.

I executed the stored procedure in SSMS, it just took less than 1 minute to execute.

I could not figure out why it is taking more than 1 hour when executed as a SQL Server Agent job?

Detachment

If you execute the same SP in SSMS and it works, maybe a setting of the Job changed, e.g. its executed on the wrong databse. If not, check Activity Monitor what the agent is doing or start a trace to get the executed commands. maybe there are locking problem. Does the problem still occur if you manually execute the job? – Shyster

@Bernhard, Thanks for your response. I checked the job and recreated it. Still the same problem. Yes if I manually exec the job it still takes a lot of time – Detachment

@Preteek can you post the job's settings? The only reason for this behaviour I can think of, is a different execution of the SP. Since parameters etc. are the same as when executed manually, the SQLServer should use the same execution plans etc. maybe you can take a look at the SQL Log - maybe this will give us a hint. – Shyster

@ Bernhard- In the job properties, Owner=sa; in Steps, Type=t-SQL; Command = Exec dbo.StoredProcedureName – Detachment

@Bernard - No reason to assume that they will be using the same plan. Maybe a parameter sniffing issue see Slow in the Application, Fast in SSMS? Understanding Performance Mysteries – Turkestan

@Martin Thanks for the link, but I thought executing the same statement from within SQLAgent should be the same as executing from SSMS, since it uses Ado.NET liek SSMS, the same query, etc. In this case I think I cannot provide any usefull help. Again I would try to check if all settings for the Job (check the settings of the single Job steps as well as the settings of the job). If this does not work, I would use Activity Monitor or start to trace the execution, to find out if there is a different executionplan. Good luck. – Shyster

Some one has any other comment or advice?? – Detachment

@Bernhard- Can you tell me how to create a trace to monitor that one particular job – Detachment

@Detachment I just added an answer, maybe it helps. If not, please follow the advices mentioned in my answer. googling for SQL Trace or SQL Profiler should help you to find information on how to perform a trace. I'm sorry I cannto give you something liek a 5 step solution to this. – Shyster

I have exactly the same problem. A script that takes 20 seconds in SSMS takes 8 minutes when running as a job. – Hallam

Answer 1

After some time commenting and assuming that the SP performs with the same input parameters and data well when executed in SSMS, I finnaly think I can give a last tip:

Depending on what actions are performed within the SP (e.g. inserting/updating/deleting a lot of data within a loop or cursor), you should set nocount on at the beginning of your code.

set nocount on

If this is not the case or does not help, please add more information, already mentioned in the comments (e.g. all settings of the Job and each Jobstep, what has been logged, what is in the Jobhistory, check SQLerrorlogs, eventlogs,....). Also take a look at the "SQL Server Logs" maybe you can gather some info here. Also a look into the Application/System eventlo of the Databaseserver is always a good idea. To get a basic overview you can use the Activitymonitor in SSMS, by selecting the Databaseserver and selecting "Activity monitor" from contextmenu and search for the sql agent.

My last try would be to try to run a sql trace for the agent. In this case you would start a trace and filter e.g. by the user that the SQLAgent Service runs. There are so many options you can set for traces, so I would recommend to google for it, search on MSDN or ask another question here on stackoverflow.

Shyster

I had the exact same issue. An SP was being executed by a job with a simple EXEC schema.sp_name GO, and running the SP as a job took forever. Adding SET NOCOUNT ON; GO to the T-SQL step ran the job in the expected 5 seconds. Must be an SQL Server software bug? – Bogoch

Answer 2

We have a large proc that runs in 88 seconds in SSMS and 30-45 minutes in SQL Server Agent. I added the dbo. prefix on all the table names and now it runs just as fast as SSMS.

Patronizing

Does anyone have any ideas or suggestions on why this makes a difference? It would seem that with or without the dbo. prefix it's finding the same table, so that shouldn't make a difference it would seem. Is it possible that it's related to making and installing some change, that resets something somewhere or causes some kind of recalculation of some statistic? – Byandby

Answer 3

I've noticed that SQL Agent jobs ignore the server's MAXDOP setting and run everything with a MAXDOP of 1. If I run a stored procedure in a query windows, it obeys the server settings and uses 4 processes. If I use SQL Agent, any stored procedure I run uses only one process.

Massasauga

Answer 4

I have a similar issue with a script that calls a number of UDFs that I created. The UDF's themselves normally run subsecond under SSMS. Likewise, running the reports I generate with them is bearable under SSMS (30d data in 8s, 365d data in 22s). I've always done NOCOUNT ON with my SQL Agent jobs as they normally generate text files out for pickin up by other processes or Excel and I do not want the extra data at the end, so it was not a solution for me.

In this case, when we run the exact same script under SQL Agent as a job, my times grow exponentially. My 8s script takes 2m30s and my 22s script takes 2h20m. This is the same whether I run it midday with other user activity and jobs or after hours with no user activity, nor jobs or backups running. Our server is idle and at best I get one of the 8 cores being utilized when run. DB is only about 10GB running on SSD with a cached RAID card and 16 of 32GB RAM is free. Since my SQL runs efficiently in SSMS, I am pretty well of the belief that I am hitting a threading limit of some sort. I have researched and tried adjusting MAXDOP just prior to the scripts in the SQL Agent with no luck.

Since this is an activity I want to schedule, it needs to be automated one way or another. I could let these scripts take the hours they need to run as SQL steps in SQL Agent jobs, but I decided to run from command line instead and I get the same performance I see in SSMS.

    sqlcmd -S SQLSRVRHost -i "C:\My Script Loc With Spaces.sql" -v MyVar="VarValue" >"C:\MyOutputFile.txt"

So I created a batch script with the SQL jobs run from sqlcmd. Then I run the batch script from a SQL Agent job, so I still have the same management and control in place. My 4 SQL jobs that collectively took over 3 hours to run complete in 1 min and a few seconds from a single batch script executed by SQL Agent.

I hope this helps...

Pedicle