Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Overview V - Monitoring Long Running Jobs


SQL Overview V - Monitoring Long Running Jobs

Author
Message
David Bird
David Bird
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 1202
Comments posted to this topic are about the item SQL Overview V - Monitoring Long Running Jobs

David Bird

My PC Quick Reference Guide
cclark-1005023
cclark-1005023
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 136
I like the thoughts in this, but we use a more general solution where we track similar information for all production jobs. Instead of using a manually configured time period for each job we calculate the standard deviation based on the historical job durations and report only when the current run time falls outside of 3 standard deviations. This alerts us to both jobs that run too long or too short, etc.
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27682 Visits: 18368
cclark-1005023 (3/24/2010)
I like the thoughts in this, but we use a more general solution where we track similar information for all production jobs. Instead of using a manually configured time period for each job we calculate the standard deviation based on the historical job durations and report only when the current run time falls outside of 3 standard deviations. This alerts us to both jobs that run too long or too short, etc.


Nice Idea. Since, statistically, 99% should fall within that range of deviations - it should be more accurate to find when a job is behaving abnormally and avoid false positives.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27682 Visits: 18368
Nice article. Thanks for the other articles in the series as well.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 2788
cclark-1005023 (3/24/2010)
I like the thoughts in this, but we use a more general solution where we track similar information for all production jobs. Instead of using a manually configured time period for each job we calculate the standard deviation based on the historical job durations and report only when the current run time falls outside of 3 standard deviations. This alerts us to both jobs that run too long or too short, etc.


@David Bird -- Nice article. Thanks. I haven't read the others in the series but definately will.

@cclark -- Great idea, I hadn't thought of that method.

This is why I love the discussions on articles. The discussions to me are like icing on the cake except sometimes the icing is vitimin packed.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
arielmunafo2001
arielmunafo2001
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 45
Great Article

The limk to SQL Overview Part VI doesnt work....
http://www.sqlservercentral.com/articles/Monitoring/69650/
David Bird
David Bird
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 1202
SQL Monitoring VI is coming out next week. Steve Jones must be planning ahead.

David Bird

My PC Quick Reference Guide
Stephen Dyckes
Stephen Dyckes
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 288
I think the idea of monitoring on the deviation from an average runtime is spot on. I have used this methodology extensively to always be one step ahead of the users and bosses, and to stop problems before they become BIG problems. I have used the automated email to call a user running the query and have some fun (sometimes I love my job:exclamationmarkSmile. I have saved countless hours by eliminating problems as they occur or quickly finding the root cause of a problem that occurred in the middle of the night or weekend, which has more than paid for itself multiple times over with just my sanity alone!
Since I am using a supported tool, SQL Sentry's Event Manager, I also have a graphical display showing me the interaction of all the events fired off in SQL or via the Task Scheduler in a cool calendar format, allowing me to drill in and see what was affected and/or caused the problem.

Stephen
adnan.korkmaz
adnan.korkmaz
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 167
This is an old topic, but nevertheless: There is a tiny typo in "last_exectued_step_date" column. Raises error if not corrected.
aswini.gangaraju
aswini.gangaraju
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 960
Hi David,

I am trying to work with this package on SQL version 2008R2. I was able to to execute this on SQL 2005 version successfully by having some modification. I would like to know from you if i need to make any modifications need to make while working on SQL 2008 version.

Thanks in advance!

Ashwin
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search