June 20, 2012 at 5:06 am
Hi There,
I've couple of SQL jobs which generally take under an hour to complete but sometimes they take 6-7 hours to complete. The output job logs doesn't really give any input. I've looked at the job activity monitor just to see if any other job is running simultaneously but that’s not the case here.
Is there way to find out why jobs take such a long time to finish?
Thanks
June 20, 2012 at 7:21 am
what do the jobs actually do??
Try setting the log output on the job steps to get more info too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 20, 2012 at 7:32 am
Do these jobs call stored procs? It could possibly be parameter sniffing.
_______________________________________________________________
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/
July 4, 2012 at 7:37 am
sorry for the delayed response was off sick for sometime.
Yes, job steps include SP's with droping and recreating indexes. But same job takes just under an hour
to complete sometime and six to seven hours at times..
Log doesn't give enough informtion it just gives output for each and every step.
July 4, 2012 at 8:35 am
I assume that these SP's are running only during the batch and are not used during say daytime or like that as these drop and recreate indexes.
Try to capture the plan when it is performing bad and when it is performaing good and see what is the difference.
How much data it processes when it runs fast and how much data it process when it is performing bad?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 8:39 am
Thanks
jobs run overninght everyday.
I tried scheduling traces but impacted performace as its 24/7 server had a P1 raised for it.
July 4, 2012 at 8:57 am
Try DMV's to see the plan and stats of these procedures,if it is still in cache.. Then that might give you idea on how to proceed. Trace will collect too much data if it ran for overnight... Try to run the extended events with minimal events..If you can change the code of the proc try to start the extended event at start and stop it once it is executed..Collect the events data based on spid..Thus you will have minimal over head and cpature the wiat events, normal read,write,cpu ,duration,sqltext and plan only...
This should be very light and will run only for duration of your proc exec and will run for that spid which is executing the proc and thus minimal impcat on server perf.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 7:20 am
You have procs running sporadically slow sometimes the two most likely candidates are blocking and parameter sniffing. Take a look at Gail's article about parameter sniffing.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
Make sure you read all 3 parts.
You may need to add some additional logging into your job and/or procs to help identify which step(s) or whatever is slow.
_______________________________________________________________
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply