sql server - reorganizar - Reorganice el índice frente a Rebuild Index en el plan de mantenimiento del servidor Sql

reorganize vs rebuild index

Question

En las reglas de SSW para mejorar la base de datos de SQL Server hay un ejemplo de un plan de mantenimiento de base de datos completo: SSW . En el ejemplo, ejecutan un Reorganize Index y luego un Rebuild Index y luego Update Statistics. ¿Hay algún punto para esto? Pensé que reorganizar Index era una versión rápida pero menos efectiva de Rebuild Index? y que una reconstrucción de índice también actualizaría las estadísticas automáticamente (al menos en el índice agrupado).

Answer 1

Al realizar una reorganización de un índice, si el índice está distribuido en dos o más archivos físicos, los datos solo se desfragmentarán dentro del archivo de datos. Las páginas no se mueven de un archivo de datos a otro.

Cuando el índice está en un solo archivo, la reorganización y reindex tendrán el mismo resultado final.

Algunas veces la reorganización será más rápida, y algunas veces el reindexamiento será más rápido dependiendo de cuán fragmentado sea el índice. Mientras menos fragmentado sea el índice, una reorganización será más rápida, cuanto más fragmentada, más lenta será la reorganización, pero más rápido será un reindex.

Answer 2

La reorganización y la reconstrucción son cosas diferentes.

Reorganizar: es una desfragmentación para los índices. Toma los índices existentes y defragmenta las páginas existentes. Sin embargo, si las páginas no están contiguas, se mantienen como antes. Solo el contenido de las páginas está cambiando.

Reconstruir: en realidad, suelta el índice y lo reconstruye desde cero. Significa que obtendrá un índice completamente nuevo, con páginas desfragmentadas y contiguas.

Además, con la reconstrucción puede cambiar el particionado o los grupos de archivos, pero con reorganizar puede desfragmentar no solo todo el índice, sino también una sola partición del índice.

Las estadísticas de actualización son automáticas en índices agrupados, pero no en los agrupados.

Answer 3

Hacer un REORGANIZE y luego REBUILD en los mismos índices es inútil, ya que cualquier cambio por la REORGANIZE se perdería haciendo la REBUILD .

Peor aún es que en el diagrama del plan de mantenimiento de SSW, primero realiza un SHRINK , que fragmenta los índices como un efecto secundario de la forma en que libera espacio. Luego, el REBUILD asigna más espacio a los archivos de la base de datos nuevamente como espacio de trabajo durante la operación REBUILD .

• REORGANIZE es una operación en línea que desfragmenta páginas hoja en un índice agrupado o no agrupado página por página usando poco espacio de trabajo adicional.

• REBUILD es una operación en línea en ediciones Enterprise, sin conexión en otras ediciones, y utiliza tanto espacio de trabajo adicional de nuevo como el tamaño del índice. Crea una nueva copia del índice y luego descarta la anterior, eliminando así la fragmentación. Las estadísticas se vuelven a calcular de manera predeterminada como parte de esta operación, pero se pueden deshabilitar.

Consulte Reorganización y reconstrucción de índices para obtener más información.

No use SHRINK excepto con la opción TRUNCATEONLY e incluso si el archivo volverá a crecer, entonces debe pensar detenidamente si es necesario:

sqlservercentral_SHRINKFILE

Answer 4

Aún mejor es:

EXEC sp_MSforeachtable ''ALTER INDEX ALL ON ? REINDEX''

o

EXEC sp_MSforeachtable ''ALTER INDEX ALL ON ? REORGANIZE''

Answer 5

Yo uso este 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

y crea un trabajo que ejecuta este SP cada semana.

Answer 6

Mis dos centavos ... Este método sigue las especificaciones descritas en tech net: 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

Answer 7

Investigué en la web y encontré algunos buenos artículos. En el y escribí la función y el script a continuación que reorganiza, recrea o reconstruye todos los índices en una base de datos.

Primero, puede necesitar leer este artículo para comprender por qué no solo recreamos todos los índices.

En segundo lugar, necesitamos una función para crear el script de creación para el índice. Entonces este artículo puede ayudar. También estoy compartiendo la función de trabajo a continuación.

Último paso haciendo un ciclo while para buscar y organizar todos los índices en la base de datos. Este video es un buen ejemplo para hacer esto.

Función:

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 por tiempo:

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

Answer 8

Antes de considerar el mantenimiento de los índices, es importante responder dos preguntas principales:

1. ¿Cuál es el grado de fragmentación?
2. ¿Cuál es la acción apropiada? Reorganizar o reconstruir?

Como se describe en este artículo, http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/ , y para ayudarlo a determinar si debe realizar la reconstrucción del índice o reorganización de índice, por favor, comprenda lo siguiente:

• La reorganización del índice es un proceso en el que SQL Server pasa por el índice existente y lo limpia. La reconstrucción de índices es un proceso de alta resistencia en el que se elimina el índice y luego se recrea desde cero con una estructura completamente nueva, libre de todos los fragmentos acumulados y las páginas de espacio vacío.

• Mientras que la reorganización del índice es una operación de limpieza pura que deja el estado del sistema sin bloquear las tablas y vistas afectadas, el proceso de reconstrucción bloquea la tabla afectada durante todo el período de reconstrucción, lo que puede dar como resultado tiempos de inactividad prolongados que no podrían ser aceptables. algunos ambientes Teniendo esto en cuenta, está claro que la reconstrucción del índice es un proceso con una solución "más sólida", pero viene con un precio: posibles bloqueos largos en las tablas indexadas afectadas.

Por otro lado, la reorganización del índice es un proceso ''liviano'' que resolverá la fragmentación de una manera menos efectiva, ya que el índice limpio siempre será segundo después del nuevo totalmente creado desde cero. Pero reorganizar el índice es mucho mejor desde el punto de vista de la eficiencia, ya que no bloquea la tabla indexada afectada durante el curso de la operación.

El artículo mencionado anteriormente también explica cómo reorganizar y reconstruir índices utilizando SSMS, T-SQL (para reorganizar / reconstruir índices en una tabla) y una herramienta de terceros llamada ApexSQL Backup.

Answer 9

Exactamente lo que dijo Biri . Aquí es cómo reindexaría una base de datos completa:

EXEC [sp_MSforeachtable] @command1="RAISERROR(''DBCC DBREINDEX(''''?'''') ...'',10,1) WITH NOWAIT DBCC DBREINDEX(''?'')"