Why some time My Job running longer than usual?
-
I have a job that usual completes in 4 to 5 mins. But sometimes its running more than 4.5 hours. I don't understand why this is happening. So simply I am killing my job and restarting job then it will run for 4 to 5 mins.
How to trace this? If I kill job in the middle, will temp space truncated automatically? or do I need to do manually truncate?
-
Could you please provide more info. If you want to see what is going on your Server you can use the dynamic management views.
-
What do the wait stats on the server show at the times when the job is running slow? Are you having blocking issues maybe?
-
My job executes a store proc that has select statements and insert into temp table. After temp table is ready we are updating other tables based on the temp table data. Some time job working nice and completes in 4 to 5 mins. But some time server takes to execute upto 5 hours. Server looks normal not having any cpu and bandwidth usage. Still executes upto 5 hours.
So now I stop sign and trying to figured out causing the issue.
-
You're far better off triaging the issue while the job is running slow. Look into issues around blocking, disk contention, page allocation problems in Tempdb.
-
There are a lot things it could be. Parameter sniffing, stale statistics, blocking, poor indexing...
Take a look at this article about how to post performance issues.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
It's a shot in dark .. but I had a problem once with long running jobs that would not complete sometimes. It utilized TEMPDB heavily to create temptables, did a lot of insertions.
That particular server was configured with a single TEMPDB.MDF file. I added additional Tempdb file, and the problem has not occurred since. (my result could be different than yours though)
On most servers depending on the need/contention. It's been recommended as a practice to have Multiple TEMPDB files (of Equal Size) ... but everything depends. And too many tempdb files could be bad too.
You can google articles on "adding multiple temp db files" to help you.
It's not a big challenge to test this, that could be something you can try?
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply