Загрузка ...
Реорганизация всех индексов. А если надо перестроение. Во всех базах данных.
Это можно воткнуть например в ежедневный план обслуживания.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
-- http://www.sql-server-performance.com/2012/performance-tuning-re-indexing-update-statistics/ -- Для индексов с фрагментацией > 10% выполняется REORGANIZE -- Для индексов с фрагментацией > 30% выполняется REBUILD SET NOCOUNT ON; DECLARE @objectid int; DECLARE @dbid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @dbname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag, database_id as dbid INTO #work_to_do FROM sys.dm_db_index_physical_stats (0, NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; SELECT * FROM #work_to_do; -- Declare the cursor for the list of partitions to be processed. DECLARE cr_partitions CURSOR FOR SELECT * FROM #work_to_do; OPEN cr_partitions; WHILE (1=1) BEGIN FETCH NEXT FROM cr_partitions INTO @objectid, @indexid, @partitionnum, @frag, @dbid; IF @@FETCH_STATUS < 0 BREAK; set @dbname = QUOTENAME(DB_NAME(@dbid)); -- список баз исключений IF @dbname = '[tempdb]' continue; IF @dbname = '[msdb]' continue; IF @dbname = '[u424495_autocall]' continue; set @command = N'USE '+@dbname+N'; SELECT @objectname=QUOTENAME(o.name),@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=@objectid' exec sp_executesql @command, N'@objectid int, @objectname nvarchar(130) OUTPUT, @schemaname nvarchar(130) OUTPUT', @objectid, @objectname output, @schemaname output; set @command = N'USE '+@dbname+N'; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid' exec sp_executesql @command, N'@objectid int, @indexid int, @indexname nvarchar(130) OUTPUT', @objectid, @indexid, @indexname output; set @command = N'USE '+@dbname+N'; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid' exec sp_executesql @command, N'@objectid int, @indexid int, @partitioncount int OUTPUT', @objectid, @indexid, @partitioncount output; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @dbname + N'.' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @dbname + N'.' + @schemaname + N'.' + @objectname + N' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); print(@command) EXEC (@command); PRINT @objectname+'.'+@indexname+'.'+CAST(@partitionnum AS nvarchar(10)); PRINT N'Executed: ' + @command; END; CLOSE cr_partitions; DEALLOCATE cr_partitions; DROP TABLE #work_to_do; |
ShoGUN
Можно улучшить, при заполнении #work_to_do не надо делать лишнюю работу, лучше сделать доп. условие:
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 128
Источник: MS советует перестраивать только большие индексы(>128 страниц), т.к. маленькие индексы хранятся вместе, вперемешку с индексами других объектов(при условии, что такие объекты есть, конечно), и перестраивай их, не перестраивай — фрагментация не уменьшится. Можете сами поэкспериментировать.
Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are sometimes stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.
Отсюда: https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017