SQL Overview V - Monitoring Long Running Jobs

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    Comments posted to this topic are about the item SQL Overview V - Monitoring Long Running Jobs

    David Bird

  • cclark-1005023

    Valued Member

    Points: 51

    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

    SSC Guru

    Points: 281205

    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[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281205

    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[/url]
    Learn Extended Events

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    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

    SSC Rookie

    Points: 30

    Great Article

    The limk to SQL Overview Part VI doesnt work....

    http://www.sqlservercentral.com/articles/Monitoring/69650/

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    SQL Monitoring VI is coming out next week. Steve Jones must be planning ahead.

    David Bird

  • Stephen Dyckes

    SSC Veteran

    Points: 244

    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:exclamationmark:). 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

    Old Hand

    Points: 388

    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

    SSC Enthusiast

    Points: 113

    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

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    I have upgraded the package to SQL 2008 and plan to publish it someday. We are skipping the SQL Server 2008 R2 in our shop because of Microsoft's requirement for a new license to use the R2 release.

    I did this upgrade a while ago and did not get any errors. I have a seperate package (never published) for pre SQl Server 200 instances that gave me warning and required a change to the multiserver database connection.

    Upgrade gave this warning:

    Warning 0x4020f42c: : The Maximum insert commit size property of the OLE DB destination "OLE DB Destination" is set to 0. This property setting can cause the running package to stop responding. For more information, see the F1 Help topic for OLE DB Destination Editor (Connection Manager Page).

    Not able to access SQL Server 7 through multiserver. Created new multiserver to use Microsoft OLE DB Provider for SQL Server.

    David Bird

  • SQLR45

    SSC Journeyman

    Points: 77

    Hello,

    I don't get how to make the MultiServer connection to work, all my server have instance.

    If I use the default LocalHost it's not working at all, gave me timeout.

    I tried to delete and recreate the connection, no luck.

    If I use a known Server\Instance it work, but as u probably guess it give me only data from that server.

    Any help will be appreciated,

    Thank you,

  • SQLR45

    SSC Journeyman

    Points: 77

    ok fixed it by myself reading this: http://www.sqlservercentral.com/Forums/Topic445734-283-3.aspx

    Do not forget that if you have an instance you need to manually change the default value of SRV_Conn to SERVER\INSTANCE using MVS.

    Also you need to change the LocalHost of ADONET.SQL_Overview and QASRV.SQL_Overview for SERVER\INSTANCE

    Thank you,

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply