Задача делать реиндекс только определенных таблиц.
Создаём таблицу
USE [ax_molniya] GO /****** Object: Table [dbo].[www_reindex_tablename] Script Date: 08/17/2018 08:45:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[www_reindex_tablename]( [objectname] [nvarchar](128) NULL, [group] [nvarchar](1) NULL, [id] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY] GO
Наполняем её таблицами которые нужно реиндексировать
Делаем хранимую процедуру
USE [ax_molniya] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[www_reindex_ALTER] -- Add the parameters for the stored procedure here -- если readonly = 0 (значение по- умолчанию), тогда выполняется вся работа скрипта) -- если readonly = 1, тогда предоставится информация о состоянии индексов на таблицу и реиндекс не будет выполнен @readonly bit = 0, -- защита от случайного запуска @password nvarchar(max) = '', -- тихий режим -- если @displayoff = 0 (значение по- умолчанию), тогда прогрес выполнения скрипта дублируется текстовыми сообщениями") -- если @displayoff = 1, тогда прогресс выполнения скрипта не дублируется(например если реиндекс инициализирован джобом,то нет необходимости показывать текстовые сообщения) @displayoff bit = 0, -- имя базы данных @dbname nvarchar(64), -- имя таблицы @tablename nvarchar(128), -- уведомление по email -- если 0 - выключено, 1 - включено @emailalert bit = 1 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; -- дата выполнения реиндекс в формате ггггммдд DECLARE @wjobday nvarchar(8) -- hash password DECLARE @adminhash varbinary DECLARE @hashpassword varbinary -- ключ для выборки данных DECLARE @seckey nvarchar(12) -- текущее время чч:мм DECLARE @b nvarchar(5) -- два правых символа от @b DECLARE @c nvarchar(2) -- два левых символа от @b DECLARE @d nvarchar(2) -- флаг выполнения задания DECLARE @flag bit DECLARE @email nvarchar(512) DECLARE @at nvarchar(500); DECLARE @createindexstart nvarchar(128) DECLARE @createindexerror nvarchar(128) DECLARE @createindexstop nvarchar(128) DECLARE @createindexnone nvarchar(128) DECLARE @dropindex nvarchar(128) DECLARE @dropindexerror nvarchar(128) DECLARE @indexname nvarchar(256) DECLARE @createindexcommand nvarchar(max) DECLARE @dropindexcommand nvarchar(max) DECLARE @errmes nvarchar(512) DECLARE @wrongpassalert nvarchar(256) -- ALTER переменные DECLARE @re_objectid int; DECLARE @re_indexid int; DECLARE @re_partitioncount bigint; DECLARE @re_schemaname nvarchar(130); DECLARE @re_objectname nvarchar(130); DECLARE @re_indexname nvarchar(130); DECLARE @re_partitionnum bigint; DECLARE @re_partitions bigint; DECLARE @re_frag float; DECLARE @re_command nvarchar(4000); -- получаем соль пароля SET @password = CONVERT(nvarchar, @password); SET @hashpassword = (SELECT HASHBYTES('MD5', @password)); -- обнуляем флаг SET @flag = 0 -- текущая дата SET @wjobday = CONVERT(nvarchar(8), GETDATE(), 112) SET @b = (SELECT CONVERT(nvarchar(5), GETDATE(), 108)) SET @c = RIGHT(@b, 2) SET @d = LEFT(@b, 2) -- получаем значение seckey SET @seckey = @wjobday + @d + @c /* --------------------#CONFIG--------------------------- секция с настройками скрипта, необходимо обязательно заполнить(проверить) перед запуском */ -- имя индекса SET @indexname = 'temp_index' -- соль пароля на запуск скрипта,НЕ ТРОГАТЬ если не знаешь,что это SET @adminhash = 0x385B43E3833EA9639158D535A765196D -- имя задания --set @jobname = '' -- email для уведомления SET @email = 'event.messages@ctmol.ru;' --SET @email = 'event.messages@ctmol.ru;' --SET @email = 'it.sysadmin.dba1@sparural.ru' /* -------------------#END CONFIG------------------------ */ -------------------------------------------------------------------- SET @createindexstart = 'Реиндекс на таблице dbo.' + @tablename + ' начат ' SET @createindexerror = '======ОШИБКА====== еиндекс на таблице на таблице ' + @tablename + ' ======ОШИБКА====== ' SET @createindexstop = 'Реиндекс на таблице на таблице dbo.' + @tablename + ' завершено ' SET @createindexnone = 'Все индексы на таблице '+ @tablename + ' в норме,перестраивать не нужно' SET @dropindex = 'Удаление временного кластеризованного индекса на таблице dbo.' + @tablename + ' выполнено ' SET @dropindexerror = '======ОШИБКА====== Удаление временного кластеризованного индекса на таблице dbo.' + @tablename + ' ======ОШИБКА====== ' SET @createindexcommand = 'CREATE CLUSTERED INDEX [' + @indexname + '] ON [dbo].[' + @tablename + ']([RECID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);' --set @createindexcommand = 'ALTER TABLE [dbo].['+@tablename+'] ADD CONSTRAINT ['+@indexname+'] PRIMARY KEY CLUSTERED ([DATAAREAID] ASC,[PRODID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' SET @dropindexcommand = 'DROP INDEX [' + @indexname + '] ON [dbo].[' + @tablename + '] WITH (ONLINE = ON);' --set @dropindexcommand ='ALTER TABLE [dbo].['+@tablename+'] DROP CONSTRAINT ['+@indexname+']' --------------------------------------------------------------------- -- проверка входных параметров IF ((SELECT COUNT(name) FROM sys.databases WHERE [name] = @dbname) = 0) BEGIN IF (@displayoff = 0) BEGIN PRINT 'указанной бд [' + @dbname + '] на сервере не существует, проверь секцию #CONFIG в процедуре [www_reindex_' + @tablename + ']' PRINT ' ' END SELECT @ErrorMessage = 'указанной бд [' + @dbname + '] на сервере не существует, проверь секцию #CONFIG в процедуре [www_reindex_' + @tablename + ']', @ErrorSeverity = 16, @ErrorState = 100; RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT END ELSE BEGIN IF (@hashpassword <> @adminhash) BEGIN IF (@displayoff = 0) BEGIN PRINT '--------------------------------------------------' PRINT '' PRINT 'ОШИБКА - введен не правильный пароль - ОШИБКА' PRINT '' PRINT '--------------------------------------------------' END SET @wrongpassalert = 'ОШИБКА - введен не правильный пароль - ОШИБКА, пользователь ' + SUSER_SNAME() EXEC www_reindex_stat_log @objectname = @tablename, @description = @wrongpassalert, @jobday = @wjobday, @seckey = @seckey SELECT @ErrorMessage = @wrongpassalert, @ErrorSeverity = 16, @ErrorState = 100; RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT END ELSE BEGIN IF (@readonly = 1) BEGIN IF (@displayoff = 0) BEGIN PRINT 'Дата проведения операции ' + @wjobday PRINT '--------------------------------------------------' PRINT '' PRINT 'Имя базы данных-> ' + @dbname PRINT 'Имя таблицы ->' + @tablename PRINT '' PRINT '--------------------------------------------------' END -- смотрим статистику по индексам таблицы EXEC www_reindex_stat_index_all_log @objectname = @tablename, @dbname = @dbname, @readonly = @readonly END ELSE BEGIN IF (@displayoff = 0) BEGIN PRINT 'Дата проведения реиндекса ' + @wjobday PRINT '--------------------------------------------------' PRINT '' PRINT 'Имя базы данных-> ' + @dbname PRINT 'Имя таблицы ->' + @tablename PRINT '' PRINT '--------------------------------------------------' END /* шаг 1 */ BEGIN TRY ----------------------------------------------------- -- смотрим состояние индексов до EXEC www_reindex_stat_index_all_log @objectname = @tablename, @jobday = @wjobday, @status = 'before', @dbname = @dbname, @seckey = @seckey IF (@displayoff = 0) BEGIN PRINT @createindexstart + CAST(GETDATE() AS varchar); RAISERROR ('', 0, 1) WITH NOWAIT END -- пишем в лог, о начале перестройки индексов на таблице EXEC www_reindex_stat_log @objectname = @tablename, @description = @createindexstart, @jobday = @wjobday, @seckey = @seckey END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage, 'шаг 1 ошибка вставки данных в таблицы лога'; SET @errmes = 'шаг 1 ошибка вставки данных в таблицы лога - ' + ERROR_MESSAGE() EXEC www_reindex_stat_log @objectname = @tablename, @description = @errmes, @jobday = @wjobday, @seckey = @seckey SELECT @ErrorMessage = @errmes, @ErrorSeverity = 16, @ErrorState = 100; RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT END CATCH ----------------------------------------------------- /* шаг 1 конец */ /* шаг 2 */ /*------------перестройка индекса начало--------------*/ BEGIN TRY SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(@dbname),OBJECT_ID(@tablename), NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; if ((select count(*) from #work_to_do) = 0 ) BEGIN IF (@displayoff = 0) BEGIN print @createindexnone + CAST(GETDATE() AS varchar); RAISERROR ('', 0, 1) WITH NOWAIT END -- пишем в лог EXEC www_reindex_stat_log @objectname = @tablename, @description = @createindexnone, @jobday = @wjobday, @seckey = @seckey END -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @re_objectid, @re_indexid, @re_partitionnum, @re_frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @re_objectname = QUOTENAME(o.name), @re_schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @re_objectid; SELECT @re_indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @re_objectid AND index_id = @re_indexid; SELECT @re_partitioncount = count (*) FROM sys.partitions WHERE object_id = @re_objectid AND index_id = @re_indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @re_frag < 30.0 SET @re_command = N'ALTER INDEX ' + @re_indexname + N' ON ' + @re_schemaname + N'.' + @re_objectname + N' REORGANIZE'; IF @re_frag >= 30.0 SET @re_command = N'ALTER INDEX ' + @re_indexname + N' ON ' + @re_schemaname + N'.' + @re_objectname + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON)'; IF @re_partitioncount > 1 SET @re_command = @re_command + N' PARTITION=' + CAST(@re_partitionnum AS nvarchar(10)); EXEC (@re_command); IF (@displayoff = 0) BEGIN PRINT N'Executed: ' + @re_command +CAST(GETDATE() AS varchar); RAISERROR ('', 0, 1) WITH NOWAIT END END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do; -- EXEC sp_executesql @createindexcommand -- пишем в лог EXEC www_reindex_stat_log @objectname = @tablename, @description = @createindexstop, @jobday = @wjobday, @seckey = @seckey IF (@displayoff = 0) BEGIN -- выводим на экран PRINT @createindexstop + CAST(GETDATE() AS varchar); RAISERROR ('', 0, 1) WITH NOWAIT END --ставим флаг SET @flag = 1 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage, 'ошибка при создании индекса'; SET @errmes = 'ошибка при создании индекса- ' + ERROR_MESSAGE() EXEC www_reindex_stat_log @objectname = @tablename, @description = @errmes, @jobday = @wjobday, @seckey = @seckey SELECT @ErrorMessage = @errmes, @ErrorSeverity = 16, @ErrorState = 100; RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT IF (@displayoff = 0) BEGIN -- выводим на экран, если не получилось PRINT @createindexerror + CAST(GETDATE() AS varchar); END -- ставим флаг SET @flag = 0 END CATCH /*------------перестройка индекса конец--------------*/ /* шаг 4 */ -- если предыдущее действие завершилось успешно,то делаем следующий шаг IF (@flag = 1) BEGIN -- пишем инфу о индексах после проведения операции BEGIN TRY EXEC www_reindex_stat_index_all_log @objectname = @tablename, @jobday = @wjobday, @status = 'after', @dbname = @dbname, @seckey = @seckey SET @flag = 1 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage, 'шаг 4 ошибка вставки данных в таблицы лога'; SET @errmes = 'шаг 4 ошибка вставки данных в таблицы лога - ' + ERROR_MESSAGE() EXEC www_reindex_stat_log @objectname = @tablename, @description = @errmes, @jobday = @wjobday, @seckey = @seckey SELECT @ErrorMessage = @errmes, @ErrorSeverity = 16, @ErrorState = 100; RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT SET @flag = 0 END CATCH END IF (@displayoff = 0) BEGIN IF (@flag = 1) BEGIN SELECT t1.[index_id], t1.[index_type_desc], t1.[avg_fragmentation_in_percent], t2.[avg_fragmentation_in_percent], t1.[avg_fragmentation_in_percent] - t2.[avg_fragmentation_in_percent] AS [profit] FROM [dbo].[www_reindex_stat_index_all] t1 FULL OUTER JOIN [www_reindex_stat_index_all] t2 ON t1.index_id = t2.index_id WHERE t1.objectname = @tablename AND t1.seckey = @seckey AND t1.status = 'before' AND t2.status = 'after' AND t2.objectname = @tablename AND t2.seckey = @seckey END END IF (@flag = 1) BEGIN BEGIN TRY SET @at = 'задание по реиндексу выполнено ' + @tablename + ' '+CAST(GETDATE() AS varchar); if (@emailalert = 1) EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'atrans', @recipients = @email, @subject = @tablename, @body = @at END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage, 'ошибка отправки email'; SELECT @ErrorMessage = 'ошибка отправки email', @ErrorSeverity = 16, @ErrorState = 100; RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT END CATCH END ELSE BEGIN BEGIN TRY SET @at = 'Ошибка - задание по реиндексу ' + @tablename + CAST(GETDATE() AS varchar); if (@emailalert = 1) EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'atrans', @recipients = @email, @subject = @tablename, @body = @at END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage, 'ошибка отправки email'; SELECT @ErrorMessage = 'ошибка отправки email', @ErrorSeverity = 16, @ErrorState = 100; RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT END CATCH END /* шаг 4 конец */ -- конец бегина с параметром ридонли END; -- конец бегина с проверкой пароля END; -- конец первого бегина END; -- конец процедуры END
Запуск хранимой процедуры
DECLARE @objectname nvarchar(130); DECLARE partitions1 CURSOR FOR SELECT objectname FROM www_reindex_tablename where [group] = 'a' -- Open the cursor. OPEN partitions1; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions1 INTO @objectname IF @@FETCH_STATUS < 0 BREAK; --print @objectname exec www_reindex_alter @dbname = 'ax_molniya', @tablename = @objectname, @password='день победы', @emailalert = 1, @displayoff = 1, @readonly = 0 END; -- Close and deallocate the cursor. CLOSE partitions1; DEALLOCATE partitions1;
Хранимая процедура для записи лога
USE [ax_molniya] GO /****** Object: StoredProcedure [dbo].[www_reindex_stat_index_all_log] Script Date: 08/17/2018 09:02:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[www_reindex_stat_index_all_log] -- Add the parameters for the stored procedure here @objectname nvarchar(128) ,@jobday nvarchar(8) = '' ,@status nvarchar(10) = 'before' ,@dbname nvarchar(64) ,@seckey nvarchar(12) = '' ,@readonly bit = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF(@readonly = 0) BEGIN insert into www_reindex_stat_index_all( [database_id], [object_id], [index_id] , [partition_number], [index_type_desc], [alloc_unit_type_desc], [index_depth], [index_level] , [avg_fragmentation_in_percent], [fragment_count], [avg_fragment_size_in_pages], [page_count], [avg_page_space_used_in_percent] , [record_count], [ghost_record_count] , [version_ghost_record_count], [min_record_size_in_bytes], [max_record_size_in_bytes], [avg_record_size_in_bytes] , [forwarded_record_count] , [jobday], [objectname], [status], [seckey] ) SELECT [database_id], [object_id], [index_id] , [partition_number], [index_type_desc], [alloc_unit_type_desc], [index_depth], [index_level] , [avg_fragmentation_in_percent], [fragment_count], [avg_fragment_size_in_pages], [page_count], [avg_page_space_used_in_percent] , [record_count], [ghost_record_count] , [version_ghost_record_count], [min_record_size_in_bytes], [max_record_size_in_bytes], [avg_record_size_in_bytes] , [forwarded_record_count] , @jobday as [jobday], @objectname as [objectname], @status as [status], @seckey as [seckey] FROM sys.dm_db_index_physical_stats(DB_ID(@dbname), OBJECT_ID(@objectname), NULL, NULL , 'LIMITED'); END ELSE BEGIN SELECT [database_id], @objectname as [objectname], [object_id], [index_id] , [partition_number], [index_type_desc], [alloc_unit_type_desc], [index_depth], [index_level] , [avg_fragmentation_in_percent], [fragment_count], [avg_fragment_size_in_pages], [page_count], [avg_page_space_used_in_percent] , [record_count], [ghost_record_count] , [version_ghost_record_count], [min_record_size_in_bytes], [max_record_size_in_bytes], [avg_record_size_in_bytes] , [forwarded_record_count] FROM sys.dm_db_index_physical_stats(DB_ID(@dbname), OBJECT_ID(@objectname), NULL, NULL , 'LIMITED'); END -- Insert statements for procedure here END GO
Таблица для записи лога
USE [ax_molniya] GO /****** Object: Table [dbo].[www_reindex_stat_index_all] Script Date: 08/17/2018 09:04:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[www_reindex_stat_index_all]( [database_id] [smallint] NULL, [object_id] [int] NULL, [index_id] [int] NULL, [partition_number] [int] NULL, [index_type_desc] [nvarchar](60) NULL, [alloc_unit_type_desc] [nvarchar](60) NULL, [index_depth] [tinyint] NULL, [index_level] [tinyint] NULL, [avg_fragmentation_in_percent] [float] NULL, [fragment_count] [bigint] NULL, [avg_fragment_size_in_pages] [float] NULL, [page_count] [bigint] NULL, [avg_page_space_used_in_percent] [float] NULL, [record_count] [bigint] NULL, [ghost_record_count] [bigint] NULL, [version_ghost_record_count] [bigint] NULL, [min_record_size_in_bytes] [int] NULL, [max_record_size_in_bytes] [int] NULL, [avg_record_size_in_bytes] [float] NULL, [forwarded_record_count] [bigint] NULL, [createdate] [datetime] NOT NULL, [createdate_unix] [int] NOT NULL, [jobday] [nvarchar](8) NOT NULL, [objectname] [nvarchar](128) NULL, [status] [nvarchar](10) NULL, [seckey] [nvarchar](12) NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[www_reindex_stat_index_all] ADD DEFAULT (getdate()) FOR [createdate] GO ALTER TABLE [dbo].[www_reindex_stat_index_all] ADD DEFAULT (datediff(second,'1970-01-01 00:00:00',getutcdate())) FOR [createdate_unix] GO