1595 lines
45 KiB
Plaintext
1595 lines
45 KiB
Plaintext
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 |