March 6, 2013 at 12:01 am
Hi,
I am just starting to learn about these things, and i was hoping someone could give me some pointers to what i should be looking at. Im running SQL Server 2005, have a nightly download from a system that takes several hours, after that is done there is a SQL job with several steps that run. Normally, this process finishes before 7AM, but lately its been taking really long, and i am lucky if it finishes before noon. Also, during the day there are often timeouts, and i noticed that it has problems when multiple sql jobs are running at a time.
Can anyone give me some suggestions to what could possibly be wrong, or where i can start looking?
On a sidenote, i had the same issue last year, but we moved our sql server to a new server and it has been running fine until now. I also restarted the server just in case.
March 6, 2013 at 9:42 am
Could be any number of things. Fragmented indexes, blocking/locking, missing indexes, other applications grabbing CPU, any server changes recently ? code changes ? major data changes ? Bigger download & data needing processing ?
Read up on performance tuning.
Look at the job and try to determine which steps inside the job are taking longer than normal.
March 6, 2013 at 1:07 pm
Have you looked at the Job History in the Activity Monitor? This should show how long each step (you stated your job had multiple steps) is taking as compared to say last month when the job was running on time.
What has changed lately with the download?
What has changed on the physical server itself?
Do you have any baseline numbers you can compare too from the past?
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
March 7, 2013 at 12:58 am
look at the failed jobs and look at the other jobs which runs around the same time. take running time also into consideration.
can you post more details like what is the job about and time taken, server configuration details.
Regards
Durai Nagarajan
March 7, 2013 at 2:47 am
I had the similar issue job which was taking under three hours to complete started taking 48 hours or more ultimately I had to terminate(kill) the job as it was impacting server performance . After lot of analysis I found that particular job step was processing huge amount of data which caused job to overrun. In my case rewriting the query and introducing new indexes resolved it.
Talk to your Development team they would be able to test the particular query in CTC/UAT also as others suggested check for the indexes and tune them if need be. SQL server allows you to run jobs simultaneously as long jobs do not point to the same database/query it should be fine.
I would monitor the jobs in activity monitor or do sp_who2 to check for any block /locks while job is still running
March 7, 2013 at 9:50 am
Here is a script that will show you the duration of each step for your jobs. this will give you a clue as to which step might be giving you the problem.
--shows duration by step for long running multi-step jobs
select
j.name as 'JobName',
s.step_id as 'Step',
s.step_name as 'StepName',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as 'RunDurationMinutes'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h
ON s.job_id = h.job_id
AND s.step_id = h.step_id
AND h.step_id <> 0
where j.enabled = 1 --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN '12/08/2012' and '12/10/2012' --Uncomment for date range queries
*/
order by JobName, RunDateTime desc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply