Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use the long-running job alarm monitoring SQL agent, the article is very detailed, has a certain reference value, interested friends must read it!

problem

When you have to manage hundreds of scheduled jobs on a busy production server, it will inevitably take a long time for the job to complete, resulting in a lot of waiting or affecting the performance of other processes. Before delving into the causes of performance degradation, we want to know when a job starts to take too long. Not only do some tasks take a long time to complete, but they may take longer than they normally take to complete.

Which jobs are going backwards? Is slow work always a problem now, or does it happen all at once? When we need to investigate a job, usually within the time limit, the running time is the first indicator to be considered in our analysis.

To compare the current run time with the duration that was executed before the same job, we usually look at the history of the previous run time of the job and see that the current run time is longer than the previous several durations. However, if you manage 500 jobs as a DBA, developer, or DevOps engineer, the task of investigating one job at a time may take half a day, but it still doesn't bring you closer to the conclusion.

You may need to check 20 assignments, and you don't want to take care of each job all the time. In this case, automatic alerts sent to your mailbox will come in handy. The solution in this article is to create a stored procedure that will accept parameters. This parameter is used to calculate the average duration of a job in the past. This process produces a report showing that the performance of currently running jobs is degraded (degraded) and optional email alerts.

Some of the steps to understand this approach and the convenient stored procedures we have used in production are as follows (scheduling as separate jobs on the same server where the monitored jobs are running (see Appendix for scripts for such jobs) to help you receive email alerts about one or more jobs that are being degraded at the time of polling. We asp_long_running_Regressing_Jobs_Alerts execute Stored Proc in our environment every minute because the duration of our homework varies from a few seconds to a few hours. Some components of SP DDL,SP are broken down into prerequisites (small size code snippets) before giving them, which contributes to a comprehensive understanding of the alert SP.

List of required tools: SQL Server (the following code is tested on version 2012 and later)

The goal here is to compare the duration of each currently running job with the average duration of all elapsed times for the same job over a given period of time.

Note: before continuing to compile the stored procedures given below, make sure that you have high access in the environment in which you want to analyze the scheduled job. Ideally, the administrator. To check your access level (if you are not the administrator of the server, run this or similar T-SQL statement. If you do not have the results of this query, you do not have permission to select the MSDB table.

USE MSDBGOSELECT HAS_PERMS_BY_NAME (QUOTENAME (SCHEMA_NAME (schema_id)) +'.'+ QUOTENAME (name), 'OBJECT',' SELECT') AS have_select, * FROM sys.tablesGO solution

The T-SQL DDL code for the following stored procedure can be compiled in any database of your choice. We use a dedicated DBA_db for this type of management SP.

USE [DBA_db] GO-- CREATE PROCEDURE asp_long_running_Regressing_Jobs_Alerts @ history_days int = 7, @ avg_duration_multiplier float = 1.5, @ bEmail bit = 0, @ bSaveToTable bit = 0, @ RecipientsList Varchar (1000) = 'myName@myCoDomain.com' @ ignore_zero_durations bit = 0 AS/* example of usage: exec DBA_db..asp_long_running_Regressing_Jobs_Alerts @ history_days = 45, @ avg_duration_multiplier = 2, @ bEmail = 0, @ bSaveToTable = 0, @ RecipientsList = 'myName@myCoDomain.com ', @ ignore_zero_durations = 1 AUTHOR (s): Vladimir Isaev;-- + V.B.S.L -contact@sqlexperts.org*/ / * PARAMETERS:@history_days int (how many days back we use for AVF run duration) @ avg_duration_multiplier (how many times longer than AVG will qualify job for producing an alert) @ bEmail (send out Alert Email or just print the msg about Regressing jobs)-'REGRESSION 'is defined here by Duration only*/SET NOCOUNT ONBEGIN select sj.name Sja.start_execution_date, sja.stop_execution_date, ajt.min_run_duration, ajt.max_run_duration, ajt.avg_run_duration, datediff (ss, start_execution_date Getdate () as cur_run_duration into # Regressing_Jobs from msdb..sysjobactivity sja left join (select job_id, avg (dbo.udf_convert_int_time2ss (run_duration)) as avg_run_duration, min (dbo.udf_convert_int_time2ss (run_duration)) as min_run_duration Max (dbo.udf_convert_int_time2ss (run_duration)) as max_run_duration from msdb..sysjobhistory where step_id=0 and run_date > CONVERT (varchar (8), GETDATE ()-@ history_days And ((run_duration 0 or @ ignore_zero_durations = 0)) and run_duration

< 240000 group by job_id )ajt on sja.job_id=ajt.job_id join msdb..sysjobs sj on sj.job_id=sja.job_id where sja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC) AND start_execution_date is not null and stop_execution_date is null and datediff(ss, start_execution_date, getdate()) >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.