USE [DBA];
GO

/*-----------------------------------------------------------
	Удаляем таблицы и процедуры, если они есть
------------------------------------------------------------*/

DROP TABLE IF EXISTS dbo.MaintenanceLog;
GO

DROP TABLE IF EXISTS dbo.IndexMaintenanceTask;
GO

DROP TABLE IF EXISTS dbo.StatsMaintenanceTask;
GO

DROP TABLE IF EXISTS dbo.MaintenanceConfig;
GO

DROP TABLE IF EXISTS dbo.MaintenanceRun;
GO

DROP PROCEDURE IF EXISTS dbo.usp_IndexCandidateSelect;
GO

DROP PROCEDURE IF EXISTS dbo.usp_IndexDefrag_Process;
GO

DROP PROCEDURE IF EXISTS dbo.usp_StatsCandidateSelect;
GO

DROP PROCEDURE IF EXISTS dbo.usp_StatsUpdate_Process;
GO

DROP PROCEDURE IF EXISTS dbo.usp_RunMaintenance;
GO

/*-----------------------------------------------------------
	Создаем таблицы
------------------------------------------------------------*/

CREATE TABLE dbo.MaintenanceRun
(
    RunId bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_MaintenanceRun PRIMARY KEY,
    ServerName sysname NOT NULL CONSTRAINT DF_MaintenanceRun_ServerName DEFAULT @@SERVERNAME,
    DatabaseName sysname NOT NULL,
    JobName sysname NULL,
    Initiator varchar(20) NOT NULL CONSTRAINT DF_MaintenanceRun_Initiator DEFAULT ('SQLAGENT'),
    SessionId int NULL CONSTRAINT DF_MaintenanceRun_SessionId DEFAULT (@@SPID),
    StartTime datetime2(0) NOT NULL CONSTRAINT DF_MaintenanceRun_StartTime DEFAULT (SYSDATETIME()),
    EndTime datetime2(0) NULL,
    Status varchar(20) NOT NULL CONSTRAINT DF_MaintenanceRun_Status DEFAULT ('RUNNING'),
    MinPages int NULL,
    MinFrag decimal(10,2) NULL,
    MaxDop int NULL,
    MinRows bigint NULL,
    MinModPct decimal(10,2) NULL,
    MinModAbs bigint NULL,
    SamplePercent int NULL,
    LockTimeoutMs int NULL,
    CutoffTime datetime2(0) NULL,
    IndexCandidates int NULL,
    IndexProcessed int NULL,
    IndexErrors int NULL,
    IndexLockTimeouts int NULL,
    IndexDeadlocks int NULL,
    IndexTimeLimitSkipped int NULL,
    StatsCandidates int NULL,
    StatsProcessed int NULL,
    StatsErrors int NULL,
    ErrorMessage nvarchar(max) NULL
);
GO

CREATE INDEX IX_MaintenanceRun_DatabaseName_StartTime ON dbo.MaintenanceRun(DatabaseName, StartTime DESC);
GO

CREATE INDEX IX_MaintenanceRun_Status_StartTime ON dbo.MaintenanceRun(Status, StartTime DESC);
GO

CREATE TABLE dbo.IndexMaintenanceTask
(
    TaskId bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_IndexMaintenanceTask PRIMARY KEY,
    RunId bigint NOT NULL,
    ServerName sysname NOT NULL CONSTRAINT DF_IndexMaintenanceTask_ServerName DEFAULT @@SERVERNAME,
    DatabaseName sysname NOT NULL,
    SchemaName sysname NOT NULL,
    TableName sysname NOT NULL,
    IndexName sysname NOT NULL,
    ObjectId int NOT NULL,
    IndexId int NOT NULL,
    IndexType nvarchar(60) NULL,
    PageCount bigint NULL,
    FragPercent decimal(12,3) NULL,
    ScanMode varchar(20) NOT NULL,
    OperationType varchar(20) NOT NULL CONSTRAINT DF_IndexMaintenanceTask_OperationType DEFAULT ('REBUILD'),
    AttemptNo int NOT NULL CONSTRAINT DF_IndexMaintenanceTask_AttemptNo DEFAULT (1),
    CommandText nvarchar(max) NULL,
    Status varchar(20) NOT NULL CONSTRAINT DF_IndexMaintenanceTask_Status DEFAULT ('PENDING'),
    SkipReason varchar(30) NULL,
    QueueTime datetime2(0) NOT NULL CONSTRAINT DF_IndexMaintenanceTask_QueueTime DEFAULT (SYSDATETIME()),
    StartTime datetime2(0) NULL,
    EndTime datetime2(0) NULL,
    DurationMin decimal(10,2) NULL,
    ErrorNumber int NULL,
    ErrorMessage nvarchar(max) NULL
);
GO

ALTER TABLE dbo.IndexMaintenanceTask
ADD CONSTRAINT FK_IndexMaintenanceTask_MaintenanceRun
    FOREIGN KEY (RunId)
    REFERENCES dbo.MaintenanceRun(RunId);
GO

CREATE INDEX IX_IndexMaintenanceTask_RunId_Status ON dbo.IndexMaintenanceTask(RunId, Status);
GO

CREATE INDEX IX_IndexMaintenanceTask_Db_Object ON dbo.IndexMaintenanceTask(DatabaseName, ObjectId, IndexId);
GO

CREATE INDEX IX_IndexMaintenanceTask_Status_QueueTime ON dbo.IndexMaintenanceTask(Status, QueueTime);
GO

CREATE TABLE dbo.StatsMaintenanceTask
(
    TaskId bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_StatsMaintenanceTask PRIMARY KEY,
    RunId bigint NOT NULL,
    ServerName sysname NOT NULL CONSTRAINT DF_StatsMaintenanceTask_ServerName DEFAULT @@SERVERNAME,
    DatabaseName sysname NOT NULL,
    SchemaName sysname NOT NULL,
    TableName sysname NOT NULL,
    ObjectId int NULL,
    StatsName sysname NULL,
    RowsCount bigint NULL,
    ModificationCounter bigint NULL,
    ModPct decimal(8,3) NULL,
    UpdateMode varchar(20) NOT NULL,
    SamplePercent int NULL,
    CommandText nvarchar(max) NULL,
    Status varchar(20) NOT NULL CONSTRAINT DF_StatsMaintenanceTask_Status DEFAULT ('PENDING'),
    QueueTime datetime2(0) NOT NULL CONSTRAINT DF_StatsMaintenanceTask_QueueTime DEFAULT (SYSDATETIME()),
    StartTime datetime2(0) NULL,
    EndTime datetime2(0) NULL,
    DurationMin decimal(10,2) NULL,
    ErrorNumber int NULL,
    ErrorMessage nvarchar(max) NULL
);
GO

ALTER TABLE dbo.StatsMaintenanceTask
ADD CONSTRAINT FK_StatsMaintenanceTask_MaintenanceRun
    FOREIGN KEY (RunId)
    REFERENCES dbo.MaintenanceRun(RunId);
GO

CREATE INDEX IX_StatsMaintenanceTask_RunId_Status ON dbo.StatsMaintenanceTask(RunId, Status);
GO

CREATE INDEX IX_StatsMaintenanceTask_Db_Object ON dbo.StatsMaintenanceTask(DatabaseName, ObjectId);
GO

CREATE INDEX IX_StatsMaintenanceTask_Status_QueueTime ON dbo.StatsMaintenanceTask(Status, QueueTime);
GO

CREATE TABLE dbo.MaintenanceLog
(
    LogId bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_MaintenanceLog PRIMARY KEY,
    RunId bigint NOT NULL,
    TaskType varchar(20) NULL,
    TaskId bigint NULL,
    LogTime datetime2(0) NOT NULL CONSTRAINT DF_MaintenanceLog_LogTime DEFAULT (SYSDATETIME()),
    LogLevel varchar(10) NOT NULL,
    StepName varchar(100) NULL,
    Message nvarchar(max) NOT NULL
);
GO

ALTER TABLE dbo.MaintenanceLog
ADD CONSTRAINT FK_MaintenanceLog_MaintenanceRun
    FOREIGN KEY (RunId)
    REFERENCES dbo.MaintenanceRun(RunId);
GO

CREATE INDEX IX_MaintenanceLog_RunId_LogTime ON dbo.MaintenanceLog(RunId, LogTime);
GO

CREATE INDEX IX_MaintenanceLog_TaskType_TaskId ON dbo.MaintenanceLog(TaskType, TaskId);
GO

CREATE INDEX IX_MaintenanceLog_LogLevel_LogTime ON dbo.MaintenanceLog(LogLevel, LogTime DESC);
GO

CREATE TABLE dbo.MaintenanceConfig
(
    ConfigId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_MaintenanceConfig PRIMARY KEY,
    ServerName sysname NOT NULL CONSTRAINT DF_MaintenanceConfig_ServerName DEFAULT @@SERVERNAME,
    DatabaseName sysname NOT NULL,
    IsEnabled bit NOT NULL CONSTRAINT DF_MaintenanceConfig_IsEnabled DEFAULT (1),
    MinPages int NOT NULL CONSTRAINT DF_MaintenanceConfig_MinPages DEFAULT (1000),
    MinFrag decimal(5,2) NOT NULL CONSTRAINT DF_MaintenanceConfig_MinFrag DEFAULT (5.00),
    MaxDop int NOT NULL CONSTRAINT DF_MaintenanceConfig_MaxDop DEFAULT (4),
    MinRows bigint NOT NULL CONSTRAINT DF_MaintenanceConfig_MinRows DEFAULT (5000),
    MinModPct decimal(5,2) NOT NULL CONSTRAINT DF_MaintenanceConfig_MinModPct DEFAULT (10.00),
    MinModAbs bigint NOT NULL CONSTRAINT DF_MaintenanceConfig_MinModAbs DEFAULT (5000),
    LockTimeoutMs int NOT NULL CONSTRAINT DF_MaintenanceConfig_LockTimeoutMs DEFAULT (5000),
    RebuildCutoffTime time(0) NULL,
    ExcludeSchemas nvarchar(max) NULL,
    ExcludeTables nvarchar(max) NULL,
    Notes nvarchar(1000) NULL,
    ModifiedAt datetime2(0) NOT NULL CONSTRAINT DF_MaintenanceConfig_ModifiedAt DEFAULT (SYSDATETIME())
);
GO

CREATE UNIQUE INDEX UX_MaintenanceConfig_DatabaseName ON dbo.MaintenanceConfig(DatabaseName);
GO

/*-----------------------------------------------------------
	Записываем начальные конфиги для всех баз
------------------------------------------------------------*/

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

DECLARE @IsEnabled bit		= 1;
DECLARE @MinPages int		= 1000;
DECLARE @MinFrag decimal(5,2) = 5.00;
DECLARE @MaxDop int = 4;
DECLARE @MinRows bigint = 5000;
DECLARE @MinModPct decimal(5,2) = 5.00;
DECLARE @MinModAbs bigint = 40000;
DECLARE @LockTimeoutMs int = 5000;
DECLARE @RebuildCutoffTime time(0) = '06:00:00';
DECLARE @Notes nvarchar(1000) = N'Инициализация';

INSERT INTO dbo.MaintenanceConfig
(
    DatabaseName,
    IsEnabled,
    MinPages,
    MinFrag,
    MaxDop,
    MinRows,
    MinModPct,
    MinModAbs,
    LockTimeoutMs,
    RebuildCutoffTime,
    Notes,
    ModifiedAt
)
SELECT
    d.name,
    @IsEnabled,
    @MinPages,
    @MinFrag,
    @MaxDop,
    @MinRows,
    @MinModPct,
    @MinModAbs,
    @LockTimeoutMs,
    @RebuildCutoffTime,
    @Notes,
    SYSDATETIME()
FROM sys.databases AS d
WHERE d.database_id > 4
  AND d.name != N'DBA'
  AND d.state_desc = 'ONLINE'
  AND d.source_database_id IS NULL
  AND NOT EXISTS
  (
      SELECT 1
      FROM dbo.MaintenanceConfig AS c
      WHERE c.DatabaseName = d.name
  );
GO

/*-----------------------------------------------------------
	Определяем хранимые процедуры
------------------------------------------------------------*/

CREATE OR ALTER PROCEDURE dbo.usp_Index_SelectCandidates
(
    @DatabaseName sysname,
    @RunId bigint,
	@PhaseName varchar(20)
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE
        @MinPages int,
        @MinFrag decimal(5,2),
        @IsEnabled bit,
        @Cmd nvarchar(max),
        @Inserted int = 0,
        @AdminDb sysname = DB_NAME();

	SET @PhaseName = UPPER(@PhaseName);

	IF @PhaseName NOT IN ('LIMITED', 'SAMPLED')
		THROW 50015, N'@PhaseName должен быть LIMITED или SAMPLED', 1;

    IF @DatabaseName IS NULL OR LTRIM(RTRIM(@DatabaseName)) = N''
        THROW 50001, N'usp_Index_SelectCandidates: не задан параметр @DatabaseName', 1;

    IF @RunId IS NULL
        THROW 50002, N'usp_Index_SelectCandidates: не задан параметр @RunId', 1;

    IF DB_ID(@DatabaseName) IS NULL
        THROW 50003, N'usp_Index_SelectCandidates: указанная база не существует', 1;

    SELECT
        @IsEnabled = IsEnabled,
        @MinPages  = MinPages,
        @MinFrag   = MinFrag
    FROM dbo.MaintenanceConfig
    WHERE DatabaseName = @DatabaseName;

    IF @IsEnabled IS NULL
        THROW 50004, N'usp_Index_SelectCandidates: для базы отсутствует запись в dbo.MaintenanceConfig', 1;

    IF @IsEnabled = 0
    BEGIN
        INSERT INTO dbo.MaintenanceLog
        (
            RunId, 
			TaskType, 
			TaskId, 
			LogLevel, 
			StepName, 
			Message
        )
        VALUES
        (
            @RunId, 
			'RUN', 
			NULL, 
			'WARN', 
			'INDEX_SELECT_' + @PhaseName,
            N'Этап пропущен: обслуживание для базы отключено в MaintenanceConfig.'
        );

        RETURN;
    END;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel, 
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN', 
		NULL, 
		'INFO', 
		'INDEX_SELECT_' + @PhaseName,
        N'Начат отбор кандидатов в режиме ' + @PhaseName
    );

    SET @Cmd = N'
		INSERT INTO ' + QUOTENAME(@AdminDb) + N'.dbo.IndexMaintenanceTask
		(
			RunId,
			DatabaseName,
			SchemaName,
			TableName,
			IndexName,
			ObjectId,
			IndexId,
			IndexType,
			PageCount,
			FragPercent,
			ScanMode,
			OperationType,
			Status,
			CommandText
		)
		SELECT
			@p_RunId,
			@p_DatabaseName,
			s.name,
			o.name,
			i.name,
			i.object_id,
			i.index_id,
			i.type_desc,
			ips.page_count,
			ips.avg_fragmentation_in_percent,
			@p_ScanMode,
			''REBUILD'',
			''PENDING'',
			NULL
		FROM sys.dm_db_index_physical_stats(DB_ID(@p_DatabaseName), NULL, NULL, NULL, @p_ScanMode) AS ips
		JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.indexes AS i
			ON i.object_id = ips.object_id
		   AND i.index_id  = ips.index_id
		JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS o
			ON o.object_id = i.object_id
		JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
			ON s.schema_id = o.schema_id
		WHERE
			o.type = ''U''
			AND ips.alloc_unit_type_desc = ''IN_ROW_DATA''
			AND ips.index_id > 0
			AND ips.page_count >= @p_MinPages
			AND ips.avg_fragmentation_in_percent >= @p_MinFrag
			AND i.is_disabled = 0
			AND i.is_hypothetical = 0
			AND i.type_desc IN (''CLUSTERED'', ''NONCLUSTERED'')
			AND NOT EXISTS
			(
				SELECT 1
				FROM ' + QUOTENAME(@AdminDb) + N'.dbo.IndexMaintenanceTask AS t
				WHERE t.RunId = @p_RunId
				  AND t.DatabaseName = @p_DatabaseName
				  AND t.ObjectId = i.object_id
				  AND t.IndexId = i.index_id
			);

		SET @p_Inserted = @@ROWCOUNT;
		';

    EXEC sys.sp_executesql
        @Cmd,
        N'@p_RunId bigint,
          @p_DatabaseName sysname,
		  @p_ScanMode varchar(20),
          @p_MinPages int,
          @p_MinFrag decimal(5,2),
          @p_Inserted int OUTPUT',
        @p_RunId = @RunId,
        @p_DatabaseName = @DatabaseName,
		@p_ScanMode = @PhaseName, 
        @p_MinPages = @MinPages,
        @p_MinFrag = @MinFrag,
        @p_Inserted = @Inserted OUTPUT;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel, 
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN', 
		NULL, 
		'INFO', 
		'INDEX_SELECT_' + @PhaseName,
        N'Отбор завершен. Добавлено кандидатов: ' + CAST(@Inserted AS nvarchar(20))
    );
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_UpdateStatistics_SelectCandidates
(
    @DatabaseName sysname,
    @RunId bigint
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE
        @IsEnabled bit,
        @MinRows bigint,
        @MinModPct decimal(5,2),
        @MinModAbs bigint,
        @Cmd nvarchar(max),
        @Inserted int = 0,
        @AdminDb sysname = DB_NAME();

    IF @DatabaseName IS NULL OR LTRIM(RTRIM(@DatabaseName)) = N''
        THROW 50031, N'usp_UpdateStatistics_SelectCandidates: не задан параметр @DatabaseName.', 1;

    IF @RunId IS NULL
        THROW 50032, N'usp_UpdateStatistics_SelectCandidates: не задан параметр @RunId.', 1;

    IF DB_ID(@DatabaseName) IS NULL
        THROW 50033, N'usp_UpdateStatistics_SelectCandidates: указанная база не существует.', 1;

    SELECT
        @IsEnabled = IsEnabled,
        @MinRows = MinRows,
        @MinModPct = MinModPct,
        @MinModAbs = MinModAbs
    FROM dbo.MaintenanceConfig
    WHERE DatabaseName = @DatabaseName;

    IF @IsEnabled IS NULL
        THROW 50034, N'usp_UpdateStatistics_SelectCandidates: для базы отсутствует запись в dbo.MaintenanceConfig.', 1;

    IF @IsEnabled = 0
    BEGIN
        INSERT INTO dbo.MaintenanceLog
        (
            RunId, 
			TaskType, 
			TaskId, 
			LogLevel, 
			StepName, 
			Message
        )
        VALUES
        (
            @RunId, 
			'RUN', 
			NULL, 
			'WARN', 
			'STATS_SELECT',
            N'Этап пропущен: обслуживание для базы отключено в dbo.MaintenanceConfig.'
        );

        RETURN;
    END;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel, 
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN', 
		NULL, 
		'INFO', 
		'STATS_SELECT',
        N'Начат отбор кандидатов на обновление статистики для базы ' + QUOTENAME(@DatabaseName)
    );

    SET @Cmd = N'
		USE ' + QUOTENAME(@DatabaseName) + N';

		;WITH StatsData AS
		(
			SELECT
				o.object_id,
				s.name AS SchemaName,
				o.name AS TableName,
				CASE
					WHEN o.name LIKE ''[_]AccRg%''
					  OR o.name LIKE ''[_]AccumRg%''
					  OR o.name LIKE ''[_]InfoRg%''
					  OR o.name LIKE ''[_]CRg%''
						THEN 1
					ELSE 0
				END AS Is1CRegister,
				ISNULL(sp.[rows], 0) AS [rows],
				ISNULL(sp.modification_counter, 0) AS modification_counter,
				CASE
					WHEN ISNULL(sp.[rows], 0) = 0 THEN 0
					ELSE
						CASE
							WHEN (100.0 * ISNULL(sp.modification_counter, 0) / sp.[rows]) > 99999
								THEN 99999
							ELSE (100.0 * ISNULL(sp.modification_counter, 0) / sp.[rows])
						END
				END AS ModPct,
				sp.last_updated
			FROM sys.objects AS o
			JOIN sys.schemas AS s
				ON s.schema_id = o.schema_id
			JOIN sys.stats AS st
				ON st.object_id = o.object_id
			OUTER APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
			WHERE
				o.type = ''U''
				AND o.is_ms_shipped = 0
		),
		Agg AS
		(
			SELECT
				sd.object_id,
				sd.SchemaName,
				sd.TableName,
				MAX(sd.Is1CRegister) AS Is1CRegister,
				MAX(sd.[rows]) AS RowsCount,
				MAX(sd.modification_counter) AS ModificationCounter,
				MAX(sd.ModPct) AS ModPct,
				MAX(CASE WHEN sd.last_updated IS NULL THEN 1 ELSE 0 END) AS HasNeverUpdatedStats,
				SUM(CASE WHEN sd.modification_counter >= @p_MinModAbs THEN 1 ELSE 0 END) AS StatsWithHighModCounter
			FROM StatsData AS sd
			WHERE sd.[rows] >= @p_MinRows
			GROUP BY
				sd.object_id,
				sd.SchemaName,
				sd.TableName
		),
		Prepared AS
		(
			SELECT
				a.object_id,
				a.SchemaName,
				a.TableName,
				a.Is1CRegister,
				a.RowsCount,
				a.ModificationCounter,
				a.ModPct,
				CASE
					WHEN a.Is1CRegister = 1 THEN ''FULLSCAN''
					WHEN a.RowsCount < 5000000 THEN ''FULLSCAN''
					ELSE ''SAMPLE''
				END AS UpdateMode,
				CASE
					WHEN a.Is1CRegister = 1 THEN NULL
					WHEN a.RowsCount < 5000000 THEN NULL
					WHEN a.RowsCount < 10000000 THEN 30
					WHEN a.RowsCount < 30000000 THEN 20
					WHEN a.RowsCount < 100000000 THEN 10
					WHEN a.RowsCount < 300000000 THEN 7
					WHEN a.RowsCount < 1000000000 THEN 5
					WHEN a.RowsCount < 3000000000 THEN 4
					ELSE 3
				END AS SamplePercent
			FROM Agg AS a
			WHERE
				a.HasNeverUpdatedStats = 1
				OR a.ModPct >= @p_MinModPct
				OR a.StatsWithHighModCounter >= 1
		)
		INSERT INTO ' + QUOTENAME(@AdminDb) + N'.dbo.StatsMaintenanceTask
		(
			RunId,
			DatabaseName,
			SchemaName,
			TableName,
			ObjectId,
			StatsName,
			RowsCount,
			ModificationCounter,
			ModPct,
			UpdateMode,
			SamplePercent,
			Status,
			CommandText
		)
		SELECT
			@p_RunId,
			@p_DatabaseName,
			p.SchemaName,
			p.TableName,
			p.object_id,
			NULL,
			p.RowsCount,
			p.ModificationCounter,
			p.ModPct,
			p.UpdateMode,
			p.SamplePercent,
			''PENDING'',
			NULL
		FROM Prepared AS p
		WHERE NOT EXISTS
		(
			SELECT 1
			FROM ' + QUOTENAME(@AdminDb) + N'.dbo.StatsMaintenanceTask AS t
			WHERE t.RunId = @p_RunId
			  AND t.DatabaseName = @p_DatabaseName
			  AND t.ObjectId = p.object_id
		);

		SET @p_Inserted = @@ROWCOUNT;
		';

    EXEC sys.sp_executesql
        @Cmd,
        N'@p_RunId bigint,
          @p_DatabaseName sysname,
          @p_MinRows bigint,
          @p_MinModPct decimal(5,2),
          @p_MinModAbs bigint,
          @p_Inserted int OUTPUT',
        @p_RunId = @RunId,
        @p_DatabaseName = @DatabaseName,
        @p_MinRows = @MinRows,
        @p_MinModPct = @MinModPct,
        @p_MinModAbs = @MinModAbs,
        @p_Inserted = @Inserted OUTPUT;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, TaskType, TaskId, LogLevel, StepName, Message
    )
    VALUES
    (
        @RunId, 'RUN', NULL, 'INFO', 'STATS_SELECT',
        N'Отбор кандидатов на обновление статистики завершен. Добавлено задач: ' + CAST(@Inserted AS nvarchar(20)) + N'.'
    );
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_Index_RebuildProcess
(
    @DatabaseName sysname,
    @RunId bigint,
    @PhaseName varchar(20)
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    DECLARE
        @MaxDop int,
        @LockTimeoutMs int,
        @RebuildCutoffTime time(0),
        @IsEnabled bit,
        @TaskId bigint,
        @SchemaName sysname,
        @TableName sysname,
        @IndexName sysname,
        @ObjectId int,
        @IndexId int,
        @PageCount bigint,
        @FragPercent decimal(8,3),
        @Cmd nvarchar(max),
        @StartTime datetime2(0),
        @EndTime datetime2(0),
        @DurationMin decimal(10,2),
        @CutoffDateTime datetime2(0),
        @SkippedByCutoff int = 0;

    IF @DatabaseName IS NULL OR LTRIM(RTRIM(@DatabaseName)) = N''
        THROW 50011, N'usp_Index_RebuildProcess: не задан параметр @DatabaseName.', 1;

    IF @RunId IS NULL
        THROW 50012, N'usp_Index_RebuildProcess: не задан параметр @RunId.', 1;

    IF DB_ID(@DatabaseName) IS NULL
        THROW 50013, N'usp_Index_RebuildProcess: указанная база не существует.', 1;

    IF @PhaseName IS NULL OR UPPER(@PhaseName) NOT IN ('LIMITED', 'SAMPLED')
        THROW 50014, N'usp_Index_RebuildProcess: @PhaseName должен быть LIMITED или SAMPLED.', 1;

    SET @PhaseName = UPPER(@PhaseName);

    SELECT
        @IsEnabled = IsEnabled,
        @MaxDop = MaxDop,
        @LockTimeoutMs = LockTimeoutMs,
        @RebuildCutoffTime = RebuildCutoffTime
    FROM dbo.MaintenanceConfig
    WHERE DatabaseName = @DatabaseName;

    IF @IsEnabled IS NULL
        THROW 50015, N'usp_Index_RebuildProcess: для базы отсутствует запись в dbo.MaintenanceConfig.', 1;

    IF @IsEnabled = 0
    BEGIN
        INSERT INTO dbo.MaintenanceLog
        (
            RunId, TaskType, TaskId, LogLevel, StepName, Message
        )
        VALUES
        (
            @RunId, 'RUN', NULL, 'WARN',
            'INDEX_REBUILD_' + @PhaseName,
            N'Этап пропущен: обслуживание для базы отключено в dbo.MaintenanceConfig.'
        );

        RETURN;
    END;

    IF @RebuildCutoffTime IS NOT NULL
    BEGIN
        SET @CutoffDateTime =
            DATEADD
            (
                SECOND,
                DATEDIFF(SECOND, CONVERT(time(0), '00:00:00'), @RebuildCutoffTime),
                CONVERT(datetime2(0), CONVERT(date, SYSDATETIME()))
            );
    END;

    DECLARE @SqlLockTimeout nvarchar(100);
    SET @SqlLockTimeout = N'SET LOCK_TIMEOUT ' + CONVERT(nvarchar(20), @LockTimeoutMs) + N';';
    EXEC sys.sp_executesql @SqlLockTimeout;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel, 
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN',
		NULL, 
		'INFO',
        'INDEX_REBUILD_' + @PhaseName,
        N'Начата обработка очереди индексов для фазы ' + @PhaseName + N' в базе ' + QUOTENAME(@DatabaseName)
    );

    WHILE EXISTS
    (
        SELECT 1
        FROM dbo.IndexMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName
          AND ScanMode = @PhaseName
          AND Status = 'PENDING'
    )
    BEGIN
        IF @CutoffDateTime IS NOT NULL AND SYSDATETIME() >= @CutoffDateTime
        BEGIN
            UPDATE dbo.IndexMaintenanceTask
            SET
                Status = 'TIME_LIMIT',
                SkipReason = 'TIME_LIMIT',
                EndTime = SYSDATETIME(),
                ErrorMessage = N'Операция не начата: достигнуто ограничение RebuildCutoffTime.'
            WHERE RunId = @RunId
              AND DatabaseName = @DatabaseName
              AND ScanMode = @PhaseName
              AND Status = 'PENDING';

            SET @SkippedByCutoff = @@ROWCOUNT;

            INSERT INTO dbo.MaintenanceLog
            (
                RunId, 
				TaskType, 
				TaskId, 
				LogLevel, 
				StepName, 
				Message
            )
            VALUES
            (
                @RunId, 
				'RUN', 
				NULL, 
				'WARN',
                'INDEX_REBUILD_' + @PhaseName,
                N'Обработка очереди остановлена по RebuildCutoffTime. Пропущено задач: ' + CAST(@SkippedByCutoff AS nvarchar(20))
            );

            BREAK;
        END;

        SELECT TOP (1)
            @TaskId      = TaskId,
            @SchemaName  = SchemaName,
            @TableName   = TableName,
            @IndexName   = IndexName,
            @ObjectId    = ObjectId,
            @IndexId     = IndexId,
            @PageCount   = PageCount,
            @FragPercent = FragPercent
        FROM dbo.IndexMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName
          AND ScanMode = @PhaseName
          AND Status = 'PENDING'
        ORDER BY FragPercent DESC, PageCount DESC, TaskId ASC;

        SET @StartTime = SYSDATETIME();

        UPDATE dbo.IndexMaintenanceTask
        SET
            Status = 'RUNNING',
            StartTime = @StartTime,
            CommandText =
                N'USE ' + QUOTENAME(@DatabaseName) + N'; ' +
                N'ALTER INDEX ' + QUOTENAME(@IndexName) +
                N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
                N' REBUILD WITH (SORT_IN_TEMPDB = OFF, MAXDOP = ' + CAST(@MaxDop AS nvarchar(10)) + N', ONLINE = OFF);'
        WHERE TaskId = @TaskId;

        SELECT @Cmd = CommandText
        FROM dbo.IndexMaintenanceTask
        WHERE TaskId = @TaskId;

        BEGIN TRY
            EXEC sys.sp_executesql @Cmd;

            SET @EndTime = SYSDATETIME();
            SET @DurationMin = CAST(DATEDIFF(MILLISECOND, @StartTime, @EndTime) / 60000.0 AS decimal(10,2));

            UPDATE dbo.IndexMaintenanceTask
            SET
                Status = 'DONE',
                SkipReason = NULL,
                EndTime = @EndTime,
                DurationMin = @DurationMin,
                ErrorNumber = NULL,
                ErrorMessage = NULL
            WHERE TaskId = @TaskId;
        END TRY
        BEGIN CATCH
            SET @EndTime = SYSDATETIME();
            SET @DurationMin = CAST(DATEDIFF(MILLISECOND, @StartTime, @EndTime) / 60000.0 AS decimal(10,2));

            IF ERROR_NUMBER() = 1222
            BEGIN
                UPDATE dbo.IndexMaintenanceTask
                SET
                    Status = 'LOCK_TIMEOUT',
                    SkipReason = 'LOCK_TIMEOUT',
                    EndTime = @EndTime,
                    DurationMin = @DurationMin,
                    ErrorNumber = ERROR_NUMBER(),
                    ErrorMessage = ERROR_MESSAGE()
                WHERE TaskId = @TaskId;

                INSERT INTO dbo.MaintenanceLog
                (
                    RunId, 
					TaskType, 
					TaskId, 
					LogLevel, 
					StepName,
					Message
                )
                VALUES
                (
                    @RunId, 
					'INDEX', 
					@TaskId, 
					'WARN', 
					'INDEX_REBUILD_' + @PhaseName, 
					N'LOCK_TIMEOUT при REBUILD индекса ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' / ' + QUOTENAME(@IndexName)
                );
            END
            ELSE IF ERROR_NUMBER() = 1205
            BEGIN
                UPDATE dbo.IndexMaintenanceTask
                SET
                    Status = 'DEADLOCK',
                    SkipReason = 'DEADLOCK',
                    EndTime = @EndTime,
                    DurationMin = @DurationMin,
                    ErrorNumber = ERROR_NUMBER(),
                    ErrorMessage = ERROR_MESSAGE()
                WHERE TaskId = @TaskId;

                INSERT INTO dbo.MaintenanceLog
                (
                    RunId, 
					TaskType, 
					TaskId, 
					LogLevel, 
					StepName, 
					Message
                )
				VALUES
                (
                    @RunId, 
					'INDEX', 
					@TaskId, 
					'WARN', 
					'INDEX_REBUILD_' + @PhaseName, N'DEADLOCK при REBUILD индекса ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' / ' + QUOTENAME(@IndexName)
                );

            END
            ELSE
            BEGIN
                UPDATE dbo.IndexMaintenanceTask
                SET
                    Status = 'ERROR',
                    SkipReason = NULL,
                    EndTime = @EndTime,
                    DurationMin = @DurationMin,
                    ErrorNumber = ERROR_NUMBER(),
                    ErrorMessage = ERROR_MESSAGE()
                WHERE TaskId = @TaskId;

                INSERT INTO dbo.MaintenanceLog
                (
                    RunId, 
					TaskType, 
					TaskId, 
					LogLevel, 
					StepName, 
					Message
                )
                VALUES
                (
                    @RunId, 
					'INDEX', 
					@TaskId, 
					'ERROR', 
					'INDEX_REBUILD_' + @PhaseName, 
					N'Ошибка при REBUILD индекса ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' / ' + QUOTENAME(@IndexName) + N'. Ошибка: ' + ERROR_MESSAGE()
                );
            END
        END CATCH;
    END;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel, 
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN', 
		NULL, 
		'INFO',
        'INDEX_REBUILD_' + @PhaseName,
        N'Обработка очереди индексов для фазы ' + @PhaseName + N' завершена.'
    );
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_UpdateStatistics_Process
(
    @DatabaseName sysname,
    @RunId bigint
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    DECLARE
        @IsEnabled bit,
        @TaskId bigint,
        @SchemaName sysname,
        @TableName sysname,
        @StatsName sysname,
        @UpdateMode varchar(20),
        @SamplePercent int,
        @Cmd nvarchar(max),
        @StartTime datetime2(0),
        @EndTime datetime2(0),
        @RebuildCutoffTime time(0),
        @CutoffDateTime datetime2(0),
        @DurationMin decimal(10,2),
        @SkippedByCutoff int = 0;

    IF @DatabaseName IS NULL OR LTRIM(RTRIM(@DatabaseName)) = N''
        THROW 50041, N'usp_UpdateStatistics_Process: не задан параметр @DatabaseName.', 1;

    IF @RunId IS NULL
        THROW 50042, N'usp_UpdateStatistics_Process: не задан параметр @RunId.', 1;

    IF DB_ID(@DatabaseName) IS NULL
        THROW 50043, N'usp_UpdateStatistics_Process: указанная база не существует.', 1;

    SELECT 
        @IsEnabled = IsEnabled,
        @RebuildCutoffTime = RebuildCutoffTime
    FROM dbo.MaintenanceConfig
    WHERE DatabaseName = @DatabaseName;

    IF @IsEnabled IS NULL
        THROW 50044, N'usp_UpdateStatistics_Process: для базы отсутствует запись в dbo.MaintenanceConfig.', 1;

    IF @IsEnabled = 0
    BEGIN
        INSERT INTO dbo.MaintenanceLog
        (
            RunId, TaskType, TaskId, LogLevel, StepName, Message
        )
        VALUES
        (
            @RunId, 'RUN', NULL, 'WARN', 'STATS_UPDATE',
            N'Этап пропущен: обслуживание для базы отключено в dbo.MaintenanceConfig.'
        );

        RETURN;
    END;

    IF @RebuildCutoffTime IS NOT NULL
    BEGIN
        SET @CutoffDateTime =
            DATEADD
            (
                SECOND,
                DATEDIFF(SECOND, CONVERT(time(0), '00:00:00'), @RebuildCutoffTime),
                CONVERT(datetime2(0), CONVERT(date, SYSDATETIME()))
            );
    END;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel,
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN', 
		NULL, 
		'INFO', 
		'STATS_UPDATE',
        N'Начато обновление статистики для базы ' + QUOTENAME(@DatabaseName)
    );

    WHILE EXISTS
    (
        SELECT 1
        FROM dbo.StatsMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName
          AND Status = 'PENDING'
    )
    BEGIN
        IF @CutoffDateTime IS NOT NULL AND SYSDATETIME() >= @CutoffDateTime
        BEGIN
            UPDATE dbo.StatsMaintenanceTask
            SET
                Status = 'TIME_LIMIT',
                EndTime = SYSDATETIME(),
                ErrorMessage = N'Операция не начата: достигнуто ограничение RebuildCutoffTime.'
            WHERE RunId = @RunId
              AND DatabaseName = @DatabaseName
              AND Status = 'PENDING';

            SET @SkippedByCutoff = @@ROWCOUNT;

            INSERT INTO dbo.MaintenanceLog
            (
                RunId, 
                TaskType, 
                TaskId, 
                LogLevel, 
                StepName, 
                Message
            )
            VALUES
            (
                @RunId, 
                'RUN', 
                NULL, 
                'WARN',
                'STATS_UPDATE',
                N'Обработка очереди остановлена по RebuildCutoffTime. Пропущено задач: ' + CAST(@SkippedByCutoff AS nvarchar(20))
            );

            BREAK;
        END;

        SELECT TOP (1)
            @TaskId = TaskId,
            @SchemaName = SchemaName,
            @TableName = TableName,
            @StatsName = StatsName,
            @UpdateMode = UpdateMode,
            @SamplePercent = SamplePercent
        FROM dbo.StatsMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName
          AND Status = 'PENDING'
        ORDER BY TaskId;

        SET @StartTime = SYSDATETIME();

        UPDATE dbo.StatsMaintenanceTask
        SET Status = 'RUNNING',
            StartTime = @StartTime
        WHERE TaskId = @TaskId;

        SET @Cmd =
            N'USE ' + QUOTENAME(@DatabaseName) + N'; ' +
            N'UPDATE STATISTICS ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
            CASE
                WHEN @StatsName IS NOT NULL
                    THEN N' (' + QUOTENAME(@StatsName) + N')'
                ELSE N''
            END +
            CASE
                WHEN @UpdateMode = 'FULLSCAN'
                    THEN N' WITH FULLSCAN;'
                WHEN @UpdateMode = 'SAMPLE'
                    THEN N' WITH SAMPLE ' + CAST(@SamplePercent AS nvarchar(10)) + N' PERCENT;'
                ELSE N';'
            END;

        UPDATE dbo.StatsMaintenanceTask
        SET CommandText = @Cmd
        WHERE TaskId = @TaskId;

        BEGIN TRY
            EXEC sys.sp_executesql @Cmd;

            SET @EndTime = SYSDATETIME();
            SET @DurationMin = CAST(DATEDIFF(MILLISECOND, @StartTime, @EndTime) / 60000.0 AS decimal(10,2));

            UPDATE dbo.StatsMaintenanceTask
            SET
                Status = 'DONE',
                EndTime = @EndTime,
                DurationMin = @DurationMin,
                ErrorNumber = NULL,
                ErrorMessage = NULL
            WHERE TaskId = @TaskId;
        END TRY
        BEGIN CATCH
            SET @EndTime = SYSDATETIME();
            SET @DurationMin = CAST(DATEDIFF(MILLISECOND, @StartTime, @EndTime) / 60000.0 AS decimal(10,2));

            UPDATE dbo.StatsMaintenanceTask
            SET
                Status = 'ERROR',
                EndTime = @EndTime,
                DurationMin = @DurationMin,
                ErrorNumber = ERROR_NUMBER(),
                ErrorMessage = ERROR_MESSAGE()
            WHERE TaskId = @TaskId;

            INSERT INTO dbo.MaintenanceLog
            (
                RunId, 
				TaskType, 
				TaskId, 
				LogLevel, 
				StepName, 
				Message
            )
            VALUES
            (
                @RunId, 
				'STATS', 
				@TaskId, 
				'ERROR', 
				'STATS_UPDATE',
                N'Ошибка обновления статистики для ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'. Ошибка: ' + ERROR_MESSAGE()
            );
        END CATCH;
    END;

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel, 
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN', 
		NULL, 
		'INFO', 
		'STATS_UPDATE',
        N'Обновление статистики для базы ' + QUOTENAME(@DatabaseName) + N' завершено'
    );
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_RunMaintenance
(
    @DatabaseName sysname,
    @RunId bigint OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE
        @IsEnabled bit,
        @MinPages int,
        @MinFrag decimal(5,2),
        @MaxDop int,
        @MinRows bigint,
        @MinModPct decimal(5,2),
        @MinModAbs bigint,
        @LockTimeoutMs int,
        @RebuildCutoffTime time(0),
        @CutoffDateTime datetime2(0),
        @ErrorMessage nvarchar(max),
        @FinalStatus varchar(20),
        @IndexCandidates int,
        @IndexProcessed int,
        @IndexErrors int,
        @IndexLockTimeouts int,
        @IndexDeadlocks int,
        @IndexTimeLimitSkipped int,
        @StatsCandidates int,
        @StatsProcessed int,
        @StatsErrors int;

    IF @DatabaseName IS NULL OR LTRIM(RTRIM(@DatabaseName)) = N''
        THROW 50101, N'usp_RunMaintenance: не задан параметр @DatabaseName.', 1;

    IF DB_ID(@DatabaseName) IS NULL
        THROW 50102, N'usp_RunMaintenance: указанная база не существует.', 1;

    SELECT
        @IsEnabled = IsEnabled,
        @MinPages = MinPages,
        @MinFrag = MinFrag,
        @MaxDop = MaxDop,
        @MinRows = MinRows,
        @MinModPct = MinModPct,
        @MinModAbs = MinModAbs,
        @LockTimeoutMs = LockTimeoutMs,
        @RebuildCutoffTime = RebuildCutoffTime
    FROM dbo.MaintenanceConfig
    WHERE DatabaseName = @DatabaseName;

    IF @IsEnabled IS NULL
        THROW 50103, N'usp_RunMaintenance: для базы отсутствует запись в dbo.MaintenanceConfig.', 1;

    IF @RebuildCutoffTime IS NOT NULL
    BEGIN
        SET @CutoffDateTime =
            DATEADD
            (
                SECOND,
                DATEDIFF(SECOND, CONVERT(time(0), '00:00:00'), @RebuildCutoffTime),
                CONVERT(datetime2(0), CONVERT(date, SYSDATETIME()))
            );
    END;

    INSERT INTO dbo.MaintenanceRun
    (
        ServerName,
        DatabaseName,
        JobName,
        Initiator,
        SessionId,
        StartTime,
        Status,
        MinPages,
        MinFrag,
        MaxDop,
        MinRows,
        MinModPct,
        MinModAbs,
        LockTimeoutMs,
        CutoffTime
    )
    VALUES
    (
        @@SERVERNAME,
        @DatabaseName,
        NULL,
        'SQLAGENT',
        @@SPID,
        SYSDATETIME(),
        'RUNNING',
        @MinPages,
        @MinFrag,
        @MaxDop,
        @MinRows,
        @MinModPct,
        @MinModAbs,
        @LockTimeoutMs,
        @CutoffDateTime
    );

    SET @RunId = SCOPE_IDENTITY();

    INSERT INTO dbo.MaintenanceLog
    (
        RunId, 
		TaskType, 
		TaskId, 
		LogLevel, 
		StepName, 
		Message
    )
    VALUES
    (
        @RunId, 
		'RUN',
		NULL, 
		'INFO', 
		'START_RUN',
        N'Старт обслуживания базы ' + QUOTENAME(@DatabaseName)
    );

    IF @IsEnabled = 0
    BEGIN
        INSERT INTO dbo.MaintenanceLog
        (
            RunId, TaskType, TaskId, LogLevel, StepName, Message
        )
        VALUES
        (
            @RunId, 'RUN', NULL, 'WARN', 'CANCELLED_RUN',
            N'Обслуживание базы отключено в dbo.MaintenanceConfig. Отмена'
        );

        UPDATE dbo.MaintenanceRun
        SET
            EndTime = SYSDATETIME(),
            Status = 'CANCELLED',
            ErrorMessage = NULL,
            IndexCandidates = 0,
            IndexProcessed = 0,
            IndexErrors = 0,
            IndexLockTimeouts = 0,
            IndexDeadlocks = 0,
            IndexTimeLimitSkipped = 0,
            StatsCandidates = 0,
            StatsProcessed = 0,
            StatsErrors = 0
        WHERE RunId = @RunId;

        RETURN;
    END;

    BEGIN TRY
        EXEC dbo.usp_Index_SelectCandidates
            @DatabaseName = @DatabaseName,
            @RunId = @RunId,
            @PhaseName = 'LIMITED';

        EXEC dbo.usp_Index_RebuildProcess
            @DatabaseName = @DatabaseName,
            @RunId = @RunId,
            @PhaseName = 'LIMITED';

        EXEC dbo.usp_Index_SelectCandidates
            @DatabaseName = @DatabaseName,
            @RunId = @RunId,
            @PhaseName = 'SAMPLED';

        EXEC dbo.usp_Index_RebuildProcess
            @DatabaseName = @DatabaseName,
            @RunId = @RunId,
            @PhaseName = 'SAMPLED';

        EXEC dbo.usp_UpdateStatistics_SelectCandidates
            @DatabaseName = @DatabaseName,
            @RunId = @RunId;

        EXEC dbo.usp_UpdateStatistics_Process
            @DatabaseName = @DatabaseName,
            @RunId = @RunId;

        SELECT
            @IndexCandidates       = COUNT(*),
            @IndexProcessed        = SUM(CASE WHEN Status = 'DONE' THEN 1 ELSE 0 END),
            @IndexErrors           = SUM(CASE WHEN Status = 'ERROR' THEN 1 ELSE 0 END),
            @IndexLockTimeouts     = SUM(CASE WHEN Status = 'LOCK_TIMEOUT' THEN 1 ELSE 0 END),
            @IndexDeadlocks        = SUM(CASE WHEN Status = 'DEADLOCK' THEN 1 ELSE 0 END),
            @IndexTimeLimitSkipped = SUM(CASE WHEN Status = 'TIME_LIMIT' THEN 1 ELSE 0 END)
        FROM dbo.IndexMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName;

        SELECT
            @StatsCandidates = COUNT(*),
            @StatsProcessed  = SUM(CASE WHEN Status = 'DONE' THEN 1 ELSE 0 END),
            @StatsErrors     = SUM(CASE WHEN Status = 'ERROR' THEN 1 ELSE 0 END)
        FROM dbo.StatsMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName;

        SELECT
            @IndexCandidates       = ISNULL(@IndexCandidates, 0),
            @IndexProcessed        = ISNULL(@IndexProcessed, 0),
            @IndexErrors           = ISNULL(@IndexErrors, 0),
            @IndexLockTimeouts     = ISNULL(@IndexLockTimeouts, 0),
            @IndexDeadlocks        = ISNULL(@IndexDeadlocks, 0),
            @IndexTimeLimitSkipped = ISNULL(@IndexTimeLimitSkipped, 0),
            @StatsCandidates       = ISNULL(@StatsCandidates, 0),
            @StatsProcessed        = ISNULL(@StatsProcessed, 0),
            @StatsErrors           = ISNULL(@StatsErrors, 0);

        IF @IndexErrors > 0 OR @StatsErrors > 0
            SET @FinalStatus = 'ERROR';
        ELSE IF @IndexLockTimeouts > 0 OR @IndexDeadlocks > 0 OR @IndexTimeLimitSkipped > 0
            SET @FinalStatus = 'WARNING';
        ELSE
            SET @FinalStatus = 'SUCCESS';

        UPDATE dbo.MaintenanceRun
        SET
            EndTime = SYSDATETIME(),
            Status = @FinalStatus,
            ErrorMessage = NULL,
            IndexCandidates = @IndexCandidates,
            IndexProcessed = @IndexProcessed,
            IndexErrors = @IndexErrors,
            IndexLockTimeouts = @IndexLockTimeouts,
            IndexDeadlocks = @IndexDeadlocks,
            IndexTimeLimitSkipped = @IndexTimeLimitSkipped,
            StatsCandidates = @StatsCandidates,
            StatsProcessed = @StatsProcessed,
            StatsErrors = @StatsErrors
        WHERE RunId = @RunId;

        INSERT INTO dbo.MaintenanceLog
        (
            RunId, TaskType, TaskId, LogLevel, StepName, Message
        )
        VALUES
        (
            @RunId, 'RUN', NULL, 'INFO', 'FINISH_RUN',
            N'Запуск обслуживания базы ' + QUOTENAME(@DatabaseName) +
            N' завершен. Итоговый статус: ' + @FinalStatus + N'.'
        );
    END TRY
    BEGIN CATCH
        SET @ErrorMessage = ERROR_MESSAGE();

        SELECT
            @IndexCandidates       = COUNT(*),
            @IndexProcessed        = SUM(CASE WHEN Status = 'DONE' THEN 1 ELSE 0 END),
            @IndexErrors           = SUM(CASE WHEN Status = 'ERROR' THEN 1 ELSE 0 END),
            @IndexLockTimeouts     = SUM(CASE WHEN Status = 'LOCK_TIMEOUT' THEN 1 ELSE 0 END),
            @IndexDeadlocks        = SUM(CASE WHEN Status = 'DEADLOCK' THEN 1 ELSE 0 END),
            @IndexTimeLimitSkipped = SUM(CASE WHEN Status = 'TIME_LIMIT' THEN 1 ELSE 0 END)
        FROM dbo.IndexMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName;

        SELECT
            @StatsCandidates = COUNT(*),
            @StatsProcessed  = SUM(CASE WHEN Status = 'DONE' THEN 1 ELSE 0 END),
            @StatsErrors     = SUM(CASE WHEN Status = 'ERROR' THEN 1 ELSE 0 END)
        FROM dbo.StatsMaintenanceTask
        WHERE RunId = @RunId
          AND DatabaseName = @DatabaseName;

        SELECT
            @IndexCandidates       = ISNULL(@IndexCandidates, 0),
            @IndexProcessed        = ISNULL(@IndexProcessed, 0),
            @IndexErrors           = ISNULL(@IndexErrors, 0),
            @IndexLockTimeouts     = ISNULL(@IndexLockTimeouts, 0),
            @IndexDeadlocks        = ISNULL(@IndexDeadlocks, 0),
            @IndexTimeLimitSkipped = ISNULL(@IndexTimeLimitSkipped, 0),
            @StatsCandidates       = ISNULL(@StatsCandidates, 0),
            @StatsProcessed        = ISNULL(@StatsProcessed, 0),
            @StatsErrors           = ISNULL(@StatsErrors, 0);

        UPDATE dbo.MaintenanceRun
        SET
            EndTime = SYSDATETIME(),
            Status = 'ERROR',
            ErrorMessage = @ErrorMessage,
            IndexCandidates = @IndexCandidates,
            IndexProcessed = @IndexProcessed,
            IndexErrors = @IndexErrors,
            IndexLockTimeouts = @IndexLockTimeouts,
            IndexDeadlocks = @IndexDeadlocks,
            IndexTimeLimitSkipped = @IndexTimeLimitSkipped,
            StatsCandidates = @StatsCandidates,
            StatsProcessed = @StatsProcessed,
            StatsErrors = @StatsErrors
        WHERE RunId = @RunId;

        INSERT INTO dbo.MaintenanceLog
        (
            RunId, TaskType, TaskId, LogLevel, StepName, Message
        )
        VALUES
        (
            @RunId, 'RUN', NULL, 'ERROR', 'RUN_ERROR',
            N'Запуск обслуживания базы ' + QUOTENAME(@DatabaseName) +
            N' завершился с ошибкой: ' + @ErrorMessage
        );

        THROW;
    END CATCH;
END;
GO

/*-----------------------------------------------------------
	Отображаем конфиг
------------------------------------------------------------*/

SELECT *
FROM dbo.MaintenanceConfig
ORDER BY DatabaseName;
GO