Реорганизовать индекс против перестроить индекс в плане обслуживания Sql Server

В правилах SSW для улучшения базы данных SQL Server приведен пример полного плана обслуживания базы данных: SSW . В этом примере они запускают как индекс реорганизации, так и индекс перестройки, а затем обновляют статистику. Есть ли смысл в этом? Я думал, что Reorganize Index - быстрая, но менее эффективная версия Rebuild Index? и что перестроение индекса также будет автоматически обновлять статистику (по крайней мере для кластерного индекса).

11.08.2008 07:02:46
9 ОТВЕТОВ
РЕШЕНИЕ

Делать a, REORGANIZEа затем a REBUILDв одних и тех же индексах бессмысленно, так как любые изменения REORGANIZEбудут потеряны при выполнении REBUILD.

Хуже того, что на диаграмме плана обслуживания от SSW он выполняет SHRINKпервое, которое фрагментирует индексы как побочный эффект от способа освобождения места. Затем REBUILDвыделяет больше места для файлов базы данных снова в качестве рабочего пространства во время REBUILDоперации.

  • REORGANIZE это оперативная операция, которая дефрагментирует листовые страницы в кластерном или некластеризованном индексном листе за страницей с использованием небольшого дополнительного рабочего пространства.

  • REBUILDэто оперативная операция в выпусках Enterprise, офлайн в других выпусках, которая снова использует столько же дополнительного рабочего пространства, сколько размер индекса. Он создает новую копию индекса, а затем удаляет старую, таким образом избавляясь от фрагментации. Статистика пересчитывается по умолчанию как часть этой операции, но это можно отключить.

См. Реорганизация и восстановление индексов для получения дополнительной информации.

Не используйте, SHRINKкроме как с TRUNCATEONLYопцией, и даже если файл снова будет расти, вам следует подумать над тем, нужно ли это:

sqlservercentral_SHRINKFILE

17
24.02.2012 09:52:37
Удивительно, как много «авторитетов» в Интернете абсолютно неверны и вводят в заблуждение, то есть предлагают вам сократить базу данных !!
Nick.McDermaid 13.07.2014 07:50:39

Реорганизация и перестройка - это разные вещи.

Реорганизация: это дефрагментация для индексов. Принимает существующие индексы и дефрагментирует существующие страницы. Однако, если страницы не являются смежными, они остаются прежними. Меняется только содержание страниц.

Перестройка: фактически он удаляет индекс и восстанавливает его с нуля. Это означает, что вы получите совершенно новый индекс с дефрагментированными и смежными страницами.

Более того, с помощью rebuild вы можете изменять разделы или группы файлов, но с помощью reorganize вы можете дефрагментировать не только весь индекс, но и только один раздел индекса.

Статистика обновления выполняется автоматически для кластеризованных индексов, но не для некластеризованных.

31
22.08.2011 07:14:07
Правильно, но есть ли смысл использовать как реорганизацию, так и перестройку в одном и том же подплане технического обслуживания?
codeulike 22.08.2011 10:28:56
На самом деле, согласно Books Online, msdn.microsoft.com/en-us/library/ms189858.aspx Реорг реорганизует страницы, чтобы сделать их физически смежными. Вот точная цитата: «Реорганизация индекса дефрагментирует листовой уровень кластеризованных и некластеризованных индексов в таблицах и представлениях путем физического упорядочения страниц листового уровня в соответствии с логическим порядком (слева направо) листовых узлов. Расположение страниц в порядке повышает производительность сканирования индекса. Индекс реорганизуется в соответствии с выделенными ему существующими страницами; новые страницы не выделяются. "
Michael K. Campbell 2.03.2012 18:49:03
@ MichaelK.Campbell: Ваша цитата была немного вырвана из контекста. Хотя ReOrg будет переупорядочивать страницы, он ТОЛЬКО переупорядочивает их на самом низком уровне, на который указывают узлы промежуточного уровня. После повторного заполнения все страницы по всему индексу не гарантированно будут смежными. Вот лучшее объяснение: dba.stackexchange.com/a/36817/6816
MikeTeeVee 20.01.2014 03:37:12

Именно то, что сказал Бири . Вот как я бы переиндексировал всю базу данных:

EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"
8
23.05.2017 11:54:15
Предпочтительный способ теперь не использовать Alter Index - docs.microsoft.com/en-us/sql/t-sql/statements/…
Rohit Vipin Mathews 21.11.2019 09:16:54

При повторной обработке индекса, если индекс распределен по двум или более физическим файлам, данные будут дефрагментированы только внутри файла данных. Страницы не перемещаются из одного файла данных в другой.

Когда индекс находится в одном файле, реорг и переиндексация будут иметь одинаковый конечный результат.

Иногда реорг будет быстрее, а реиндекс - быстрее, в зависимости от того, насколько фрагментирован индекс. Чем менее фрагментирован индекс, чем реорг будет быстрее, тем более фрагментированным будет медленный реорг, но тем быстрее будет реиндекс.

10
3.09.2008 06:36:13

Еще лучше это:

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REINDEX'

или

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'
3
26.06.2013 05:56:20

Я использую этот SP

CREATE PROCEDURE dbo.[IndexRebuild]
AS 
DECLARE @TableName NVARCHAR(500);
DECLARE @SQLIndex NVARCHAR(MAX);
DECLARE @RowCount INT;
DECLARE @Counter INT;

DECLARE @IndexAnalysis TABLE
    (
      AnalysisID INT IDENTITY(1, 1)
                     NOT NULL
                     PRIMARY KEY ,
      TableName NVARCHAR(500) ,
      SQLText NVARCHAR(MAX) ,
      IndexDepth INT ,
      AvgFragmentationInPercent FLOAT ,
      FragmentCount BIGINT ,
      AvgFragmentSizeInPages FLOAT ,
      PageCount BIGINT
    )

BEGIN
    INSERT  INTO @IndexAnalysis
            SELECT  [objects].name ,
                    'ALTER INDEX [' + [indexes].name + '] ON ['
                    + [schemas].name + '].[' + [objects].name + '] '
                    + ( CASE WHEN (   [dm_db_index_physical_stats].avg_fragmentation_in_percent >= 20
                                    AND [dm_db_index_physical_stats].avg_fragmentation_in_percent < 40
                                  ) THEN 'REORGANIZE'
                             WHEN [dm_db_index_physical_stats].avg_fragmentation_in_percent > = 40
                             THEN 'REBUILD'
                        END ) AS zSQL ,
                    [dm_db_index_physical_stats].index_depth ,
                    [dm_db_index_physical_stats].avg_fragmentation_in_percent ,
                    [dm_db_index_physical_stats].fragment_count ,
                    [dm_db_index_physical_stats].avg_fragment_size_in_pages ,
                    [dm_db_index_physical_stats].page_count
            FROM    [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL,
                                                       NULL, 'LIMITED') AS   [dm_db_index_physical_stats]
                    INNER JOIN [sys].[objects] AS [objects] ON (   [dm_db_index_physical_stats].[object_id] = [objects].[object_id] )
                    INNER JOIN [sys].[schemas] AS [schemas] ON ( [objects].[schema_id]  = [schemas].[schema_id] )
                    INNER JOIN [sys].[indexes] AS [indexes] ON (  [dm_db_index_physical_stats].[object_id] = [indexes].[object_id]
                                                          AND  [dm_db_index_physical_stats].index_id = [indexes].index_id
                                                          )
            WHERE   index_type_desc <> 'HEAP'
                    AND [dm_db_index_physical_stats].avg_fragmentation_in_percent > 20
END

SELECT  @RowCount = COUNT(AnalysisID)
FROM    @IndexAnalysis

SET @Counter = 1
WHILE @Counter <= @RowCount 
    BEGIN

        SELECT  @SQLIndex = SQLText
        FROM    @IndexAnalysis
        WHERE   AnalysisID = @Counter

        EXECUTE sp_executesql @SQLIndex

        SET @Counter = @Counter + 1

    END
 GO

и создать одну работу, которая выполняет этот SP каждую неделю.

5
25.09.2013 11:37:54

Мои два цента ... Этот метод соответствует спецификации, изложенной в технической сети: http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

USE [MyDbName]
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [maintenance].[IndexFragmentationCleanup]
AS
DECLARE @reIndexRequest VARCHAR(1000)

DECLARE reIndexList CURSOR
FOR
SELECT INDEX_PROCESS
FROM (
    SELECT CASE 
            WHEN avg_fragmentation_in_percent BETWEEN 5
                    AND 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REORGANIZE;'
            WHEN avg_fragmentation_in_percent > 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REBUILD with(ONLINE=ON);'
            END AS INDEX_PROCESS
        ,avg_fragmentation_in_percent
        ,t.NAME
    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a
    INNER JOIN sys.indexes AS i ON a.object_id = i.object_id
        AND a.index_id = i.index_id
    INNER JOIN sys.tables t ON t.object_id = i.object_id
    WHERE i.NAME IS NOT NULL
    ) PROCESS
WHERE PROCESS.INDEX_PROCESS IS NOT NULL
ORDER BY avg_fragmentation_in_percent DESC

OPEN reIndexList

FETCH NEXT
FROM reIndexList
INTO @reIndexRequest

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY

        PRINT @reIndexRequest;

        EXEC (@reIndexRequest);

    END TRY

    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = 'UNABLE TO CLEAN UP INDEX WITH: ' + @reIndexRequest + ': MESSAGE GIVEN: ' + ERROR_MESSAGE()
            ,@ErrorSeverity = 9 
            ,@ErrorState = ERROR_STATE();

    END CATCH;

    FETCH NEXT
    FROM reIndexList
    INTO @reIndexRequest
END

CLOSE reIndexList;

DEALLOCATE reIndexList;

RETURN 0

GO
0
3.10.2014 17:52:09

Я исследовал в Интернете и нашел несколько хороших статей. В и я написал функцию и скрипт ниже, который реорганизует, воссоздает или перестраивает все индексы в базе данных.

Сначала вам может понадобиться прочитать эту статью, чтобы понять, почему мы не просто воссоздаем все индексы.

Во-вторых, нам нужна функция для создания скрипта создания индекса. Так что эта статья может помочь. Также я делюсь рабочей функцией ниже.

Последний шаг создания цикла while для поиска и упорядочения всех индексов в базе данных. Это видео является отличным примером, чтобы сделать это.

Функция:

create function GetIndexCreateScript(
    @index_name nvarchar(100)
) 
returns nvarchar(max)
as
begin

declare @Return   varchar(max)

SELECT @Return = ' CREATE ' + 
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +  
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +   
    I.name  + ' ON '  +  
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + 
    KeyColumns + ' )  ' + 
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + 
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' + 
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  + 
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  + 
    -- default value 
    'SORT_IN_TEMPDB = OFF '  + ','  + 
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  + 
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  + 
    -- default value  
    ' DROP_EXISTING = ON '  + ','  + 
    -- default value  
    ' ONLINE = OFF '  + ','  + 
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  + 
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' + 
   DS.name + ' ] '  
FROM sys.indexes I   
 JOIN sys.tables T ON T.Object_id = I.Object_id    
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid   
 JOIN (SELECT * FROM (  
    SELECT IC2.object_id , IC2.index_id ,  
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1  
    JOIN Sys.columns C   
       ON C.object_id = IC1.object_id   
       AND C.column_id = IC1.column_id   
       AND IC1.is_included_column = 0  
    WHERE IC1.object_id = IC2.object_id   
       AND IC1.index_id = IC2.index_id   
    GROUP BY IC1.object_id,C.name,index_id  
    ORDER BY MAX(IC1.key_ordinal)  
       FOR XML PATH('')), 1, 2, '') KeyColumns   
    FROM sys.index_columns IC2   
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables  
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4   
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id  
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id   
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id   
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id   
 LEFT JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name  
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 1   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id    
    GROUP BY IC1.object_id,C.name,index_id   
       FOR XML PATH('')), 1, 2, '') IncludedColumns    
   FROM sys.index_columns IC2    
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
   GROUP BY IC2.object_id ,IC2.index_id) tmp1   
   WHERE IncludedColumns IS NOT NULL ) tmp2    
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id   
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 
AND I.[name] = @index_name

return @Return

end

Sql на время:

declare @RebuildIndex Table(
    IndexId int identity(1,1),
    IndexName varchar(100),
    TableSchema varchar(50),
    TableName varchar(100),
    Fragmentation decimal(18,2)
)


insert into @RebuildIndex (IndexName,TableSchema,TableName,Fragmentation)
SELECT 
    B.[name] as 'IndexName', 
    Schema_Name(O.[schema_id]) as 'TableSchema',
    OBJECT_NAME(A.[object_id]) as 'TableName',
    A.[avg_fragmentation_in_percent] Fragmentation
FROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A 
INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id  
INNER JOIN sys.objects O ON O.[object_id] = B.[object_id]  
 where B.[name] is not null and B.is_primary_key = 0 AND B.is_unique_constraint = 0 and A.[avg_fragmentation_in_percent] >= 5  

--select * from @RebuildIndex

 declare @begin int = 1
 declare @max int
 select @max = Max(IndexId) from @RebuildIndex
 declare @IndexName varchar(100), @TableSchema varchar(50), @TableName varchar(100) , @Fragmentation decimal(18,2)

 while @begin <= @max
 begin

    Select @IndexName = IndexName from @RebuildIndex where IndexId = @begin
    select @TableSchema = TableSchema  from @RebuildIndex where IndexId = @begin
    select @TableName = TableName  from @RebuildIndex where IndexId = @begin 
    select @Fragmentation = Fragmentation  from @RebuildIndex where IndexId = @begin 

    declare @sql nvarchar(max)
    if @Fragmentation < 31
    begin
        set @sql = 'ALTER INDEX ['+@IndexName+'] ON ['+@TableSchema+'].['+@TableName+'] REORGANIZE WITH ( LOB_COMPACTION = ON )'
        print 'Reorganized Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation)
    end
    else
    begin
        set @sql = (select dbo.GetIndexCreateScript(@IndexName))
        if(@sql is not null)
        begin
            print 'Recreated Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation)
        end 
        else
        begin
            set @sql = 'ALTER INDEX ['+@IndexName+'] ON ['+@TableSchema+'].['+@TableName+'] REBUILD PARTITION = ALL WITH (ONLINE = ON)'
            print 'Rebuilded Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation)
        end
    end

    execute(@sql)


    set @begin = @begin+1

end
0
28.01.2015 22:39:06

Прежде чем рассмотреть вопрос об обслуживании индексов, важно ответить на два основных вопроса:

  1. Какова степень фрагментации?
  2. Какое уместное действие? Реорганизовать или перестроить?

Как описано в этой статье, http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/ , чтобы помочь вам определить, следует ли выполнять перестройку индекса или индекс реорганизации, пожалуйста, поймите следующее:

  • Реорганизация индекса - это процесс, при котором SQL Server просматривает существующий индекс и очищает его. Перестроение индекса - это сложный процесс, при котором индекс удаляется, а затем воссоздается с нуля с совершенно новой структурой, свободной от всех скопившихся фрагментов и страниц пустого пространства.

  • Хотя реорганизация индекса является чистой операцией очистки, которая оставляет состояние системы без блокировки затронутых таблиц и представлений, процесс перестройки блокирует затронутую таблицу на весь период перестройки, что может привести к длительным простоям, которые не могут быть приняты в некоторые среды. Имея это в виду, ясно, что перестройка индекса - это процесс с «более сильным» решением, но он идет с возможной длинной ценой блокировок на затронутых индексированных таблицах.

С другой стороны, реорганизация индекса - это «легкий» процесс, который решает фрагментацию менее эффективным способом, поскольку очищенный индекс всегда будет вторым после нового, полностью созданного с нуля. Но реорганизация индекса намного лучше с точки зрения эффективности, так как он не блокирует затронутую индексированную таблицу в ходе работы.

Вышеупомянутая статья также объясняет, как реорганизовать и перестроить индексы, используя SSMS, T-SQL (для реорганизации / перестроения индексов в таблице) и сторонний инструмент под названием ApexSQL Backup.

24
27.01.2016 10:39:28
необходимо ли при обновлении индекса обновлять статистику таблицы? особенно если есть некластерный индекс?
Moudiz 3.04.2018 14:57:49