<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by David Bird
»
SQL Overview V - Monitoring Long Running Jobs...
13 posts, Page 1 of 2
1
2
»»
SQL Overview V - Monitoring Long Running Jobs
Rate Topic
Display Mode
Topic Options
Author
Message
David Bird
David Bird
Posted Tuesday, March 23, 2010 9:43 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 182,
Visits: 958
Comments posted to this topic are about the item
SQL Overview V - Monitoring Long Running Jobs
David Bird
My PC Quick Reference Guide
Post #888674
cclark-1005023
cclark-1005023
Posted Wednesday, March 24, 2010 7:31 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, January 02, 2013 11:45 PM
Points: 13,
Visits: 127
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.
Post #888954
SQLRNNR
SQLRNNR
Posted Wednesday, March 24, 2010 10:57 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858,
Visits: 12,443
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #889142
SQLRNNR
SQLRNNR
Posted Wednesday, March 24, 2010 10:58 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858,
Visits: 12,443
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #889144
Trey Staker
Trey Staker
Posted Wednesday, March 24, 2010 11:25 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 5:42 PM
Points: 1,380,
Visits: 2,637
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
Post #889176
arielmunafo2001
arielmunafo2001
Posted Thursday, March 25, 2010 6:31 AM
Forum Newbie
Group: General Forum Members
Last Login: Sunday, February 03, 2013 8:05 AM
Points: 4,
Visits: 40
Great Article
The limk to SQL Overview Part VI doesnt work....
http://www.sqlservercentral.com/articles/Monitoring/69650/
Post #889724
David Bird
David Bird
Posted Thursday, March 25, 2010 7:30 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 182,
Visits: 958
SQL Monitoring VI is coming out next week. Steve Jones must be planning ahead.
David Bird
My PC Quick Reference Guide
Post #889800
Stephen Dyckes
Stephen Dyckes
Posted Friday, July 29, 2011 4:54 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:38 PM
Points: 145,
Visits: 217
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
). 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
Post #1151354
adnan.korkmaz
adnan.korkmaz
Posted Friday, September 16, 2011 12:43 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, June 03, 2013 1:34 AM
Points: 19,
Visits: 145
This is an old topic, but nevertheless: There is a tiny typo in "last_exectued_step_date" column. Raises error if not corrected.
Post #1176212
aswini.gangaraju
aswini.gangaraju
Posted Wednesday, September 28, 2011 8:33 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 10:10 AM
Points: 4,
Visits: 545
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
Post #1182925
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
