Slow Sql Agent Job

Question

Slow Sql Agent Job

Jayme MacDonald

SSC Enthusiast

Points: 139
More actions
March 14, 2007 at 4:54 pm

#177130

Hi,

I have a sql agent job that is running a stored procedure. It takes about 50 minutes to run. In query analyzer, it takes about 2 minutes.
I found reference to ensuring that the 'set nocount on' statement is set in the stored procedure. In my case it wasn't. I added in but I find it did help as it only runs for 15 minutes now.
I'm wondering what else is there to check for so it runs for 2 minutes?
Regards,
Jayme

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Check the timing. When do you have it scheduled to run? Try running it manually at that time and see if it is just as slow. When you run it in QA, it's probably not at the same time the job itself runs, is it?

Check login. When you run the script via Query Analyzer, you run it as you. When the job runs, it is running as the SQLServerAgent service login. We don't ever use the 'local admin' login for our services. We created a different login. So when we need to troubleshoot possible login issues, we can log into Query Analyzer with the same login/password our SQL Server services run under.

-SQLBill

Jayme MacDonald SSC Enthusiast Points: 139 More actions · Answer 2

Thank you for the response.

Yes, I have been running query analyzer and then the sql agent job right afterwards.

Yes, I run the Query Analyzer as a windows userid id we set up for the application.

In the sql server agent job, it runs as [self]. I think its sqlexec that its running under when its this as the job step indicates that.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Go to Services on the server. Find the SQLServerAgent service. Look for the column 'Log on as'. What does the agent use for a login? THAT is what the job runs as. When you run the job manually (right click on the job and select start), it is running with the login you are using.

Log into Query Analyzer using the same login the SQLServerAgent service uses and try to run the script.

-SQLBill

Jayme MacDonald SSC Enthusiast Points: 139 More actions · Answer 4

I don't have direct access to our server but my DBA confirms that its sqlexec.

I'll get my DBA to try the query using sqlexec.

One other thing about the script that I'm going to change is that the program is using external cursors. I'm not sure if that causes record retrieval to extremly slow down but I'll see.