Устали от неожиданных задержек, вызванных долгими заданиями SQL Server? Эти задержки могут быть раздражающими для пользователей, разработчиков и администраторов баз данных, которые пытаются выполнить свою работу. К счастью, есть способы решить эти проблемы до того, как они станут более серьезными.

В предыдущей статье мы представили пакет SQL Overview SSIS, который может сообщать о долго выполняющихся заданиях SQL Agent после их завершения. Однако он не проверяет активно выполняющиеся задания, которые занимают больше времени, чем ожидалось. В этой статье мы покажем вам, как добавить контейнер в пакет SSIS, который будет отслеживать выполняющиеся задания SQL Agent для экземпляров SQL Server 2005 и 2008.

Для отслеживания выполняющихся заданий SQL Agent мы будем использовать новую хранимую процедуру msdb.dbo.sp_help_jobactivity, добавленную в SQL Server 2005. Эта хранимая процедура предоставляет больше информации, чем таблица msdb.dbo.sysjobactivity, что делает ее лучшим выбором для наших целей. Поскольку хранимая процедура была выпущена вместе с SQL Server 2005, ее можно выполнить на SQL Server 2005 или более поздних версиях.

В дополнение к новому контейнеру, мы также добавим несколько новых отчетов. Эти отчеты будут предоставлять информацию о долго выполняющихся завершенных заданиях и заданиях, которые в настоящее время выполняются дольше, чем ожидалось. Для проверки этих заданий мы будем сохранять их время выполнения в новой таблице. Эта таблица будет содержать системное значение по умолчанию и дополнительную настраиваемую настройку для каждого задания. Отчеты будут генерироваться ежедневно вместе с остальными отчетами SQL Overview. При необходимости отчеты также могут быть созданы повторно в течение дня с использованием отдельного пакета.

Обновление пакета

Перед обновлением пакета SSIS новым контейнером нам нужно создать несколько новых таблиц. Первая таблица – dbo.Job_Activity, которая будет хранить вывод из хранимой процедуры msdb.dbo.sp_help_jobactivity. Вот SQL-код для создания этой таблицы:

USE SQL_Overview
GO

CREATE TABLE [dbo].[Job_Activity](
    [Server] [nvarchar](128) NOT NULL,
    [session_id] [int] NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [job_name] [sysname] NOT NULL,
    [run_requested_date] [datetime] NULL,
    [run_requested_source] [sysname] NULL,
    [queued_date] [datetime] NULL,
    [start_execution_date] [datetime] NULL,
    [last_executed_step_id] [int] NULL,
    [last_exectued_step_date] [datetime] NULL,
    [stop_execution_date] [datetime] NULL,
    [next_scheduled_run_date] [datetime] NULL,
    [job_history_id] [int] NULL,
    [message] [nvarchar](1024) NULL,
    [run_status] [int] NULL,
    [operator_id_emailed] [int] NULL,
    [operator_id_netsent] [int] NULL,
    [operator_id_paged] [int] NULL,
    [Row_inserted_date] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Job_Activity] ADD CONSTRAINT [DF_Job_Activity_Server] DEFAULT (@@servername) FOR [Server]
GO

ALTER TABLE [dbo].[Job_Activity] ADD CONSTRAINT [DF_Job_Activity_Package_run_date] DEFAULT (GETDATE()) FOR [Row_inserted_date]
GO

Вторая таблица – rep.Long_Running_Jobs, которая содержит настройки долго выполняющихся заданий и любые задания, отмеченные как выполняющиеся долго. Вот SQL-код для создания этой таблицы:

USE SQL_Overview
GO

CREATE TABLE [rep].[Long_Running_Jobs](
    [Server] [nvarchar](128) NOT NULL,
    [Job_Name] [nvarchar](128) NOT NULL,
    [Alert_RunTime_Minutes] [bigint] NULL,
    [Last_Reported_Job_Duration] [nvarchar](50) NULL,
    [Last_Reported_Date] [datetime] NOT NULL,
    CONSTRAINT [PK_Long_Running_Jobs_Thresholds] PRIMARY KEY CLUSTERED (
        [Server] ASC,
        [Job_Name] ASC
    )
) ON [PRIMARY]
GO

ALTER TABLE [rep].[Long_Running_Jobs] ADD CONSTRAINT [DF_Long_Running_Jobs_Last_Report_date] DEFAULT (GETDATE()) FOR [Last_Reported_Date]
GO

Третья таблица – tempdb.dbo.Job_Activity, которая используется для хранения данных из хранимой процедуры msdb.dbo.sp_help_jobactivity и передачи их обратно в постоянную таблицу в базе данных SQL_Overview. Вот SQL-код для создания этой таблицы:

USE tempdb
GO

CREATE TABLE [tempdb].[dbo].[Job_Activity](
    [Server] [nvarchar](128) NOT NULL CONSTRAINT [DF_Job_Activity_Server] DEFAULT (@@servername),
    [session_id] [int] NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [job_name] [sysname] NOT NULL,
    [run_requested_date] [datetime] NULL,
    [run_requested_source] [sysname] NULL,
    [queued_date] [datetime] NULL,
    [start_execution_date] [datetime] NULL,
    [last_executed_step_id] [int] NULL,
    [last_executed_step_date] [datetime] NULL,
    [stop_execution_date] [datetime] NULL,
    [next_scheduled_run_date] [datetime] NULL,
    [job_history_id] [int] NULL,
    [message] [nvarchar](1024) NULL,
    [run_status] [int] NULL,
    [operator_id_emailed] [int] NULL,
    [operator_id_netsent] [int] NULL,
    [operator_id_paged] [int] NULL,
    [Row_inserted_date] [datetime] NOT NULL CONSTRAINT [DF_Row_inserted_date] DEFAULT (getdate())
) ON [PRIMARY]
GO

После создания этих таблиц вы можете обновить пакет SSIS с помощью SQL Server Business Intelligence Development Studio. Очистите таблицу Job_Activity и добавьте объект “Execute SQL Task” в контейнер “Truncate Tables”. Установите SQL-запрос в TRUNCATE TABLE Job_Activity.

Добавьте контейнер “Foreach Loop Container” между задачами “Collect Backup History” и “Collect Job History”. Установите перечислитель на Foreach ADO и выберите источник ADO объекта. Добавьте необходимые задачи для сбора активности задания и загрузки ее в локальный экземпляр.

Отчеты

Мы также создадим новые отчеты для монитор