Reorganizați indexul vs. Rebuild Index în planul de întreținere a serverului Sql

În regulile SSW la o bază de date SQL Server mai bună există un exemplu de plan de întreținere a bazei de date complete: SSV . În exemplu, ele rulează atât un index Reorganizare, apoi un index Rebuild și apoi Update Statistics. E vreo problemă? Am crezut că Reorganizarea Indexului a fost o versiune rapidă, dar mai puțin eficientă a Indexului Rebuild? și că o refacere a indexului ar actualiza automat și statisticile (cel puțin pe indexul grupat).

0
fr hi bn

9 răspunsuri

Exact ceea ce Biri a spus. Iată cum aș reindexona o întreagă bază de date:

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

Reorganizarea și reconstruirea sunt lucruri diferite.

Reorganizează: este un defrag pentru indexuri. Extinde indexul existent și defragmentează paginile existente. Cu toate acestea, dacă paginile nu sunt într-o manieră contiguă, ele rămân ca înainte. Numai conținutul paginilor se schimbă.

Rebuild: de fapt, scade indicele și îl reconstruiește de la zero. Înseamnă că veți obține un index complet nou, cu pagini defragmentate și învecinate.

Mai mult, cu reconstrucția puteți schimba grupurile de partiții sau grupuri de fișiere, dar cu reorganizarea puteți defragmenta nu numai întregul index, ci și numai o singură partiție a indexului.

Statisticile actualizării sunt automate pe indexurile grupate, dar nu pe cele care nu sunt grupate.

0
adăugat
Corect, dar există vreun folos în a avea atât o reorganizare și o reconstrucție în același plan de întreținere?
adăugat autor codeulike, sursa
@ MichaelK.Campbell: Citatul dvs. a fost luat puțin din context. În timp ce ReOrg va re-ordona paginile, se rearanjează NUMAI la cel mai mic nivel pe care nodurile nivelului intermediar le îndreaptă spre ele. După o ReOrg, toate paginile din întregul Index nu sunt garantate ca fiind învecinate. Iată o explicație mai bună: dba.stackexchange.com/a/36817/6816
adăugat autor MikeTeeVee, sursa
De fapt, în conformitate cu Books Online msdn.microsoft.com/en-us/library/ ms189858.aspx Reorg Reorganizează paginile pentru a le face fizic învecinate. Iată citatul exact: "Reorganizarea unui index creează defragmentarea nivelului de frunze al indexurilor grupate și nesclocate pe tabele și vizualizări prin reordonarea fizică a paginilor la nivel de frunză pentru a se potrivi ordinii logice (de la stânga la dreapta) a nodurilor frunze. îmbunătățește performanța scanării indexului. Indexul este reorg
adăugat autor Michael K. Campbell, sursa

Eu folosesc acest 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

și creați un job care execută acest SP în fiecare săptămână.

0
adăugat

Când faci un reorg al unui index, dacă indexul este împărțit în două sau mai multe fișiere fizice, datele vor fi defragmentate doar în fișierul de date. Paginile nu sunt mutate dintr-un fișier de date în altul.

Când indexul este într-un singur fișier, reorgul și reindexul vor avea același rezultat final.

Uneori, reordul va fi mai rapid și uneori reindexul va fi mai rapid, în funcție de fragmentarea indexului. Cu cât fragmentul este mai puțin fragmentat, atunci un reorg va fi mai rapid, cu atât mai fragmentat va fi mai lent reordul, dar cu atât mai repede va fi un reindex.

0
adăugat

Făcând un REORGANIZE și apoi un REBUILD pe aceleași indexuri este inutilă, orice modificări făcute de REORGANIZE REBUILD .

Mai rău decât acesta este faptul că în diagrama planului de întreținere de la SSW, acesta efectuează mai întâi un SHRINK , care fragmentează indexurile ca efect secundar al modului în care eliberează spațiu. Apoi, REBUILD alocă din nou spațiu mai mult fișierelor bazei de date ca spațiu de lucru în timpul operației REBUILD .

  • REORGANIZE este o operație online care defragă paginile frunze într-o pagină indexată în grup sau neasamblată,

  • REBUILD este o operație online în edițiile Enterprise, offline în alte ediții și utilizează din nou un spațiu de lucru suplimentar ca dimensiunea indexului. Creează o nouă copie a indexului și apoi scade cel vechi, eliminând astfel fragmentarea. Statisticile sunt recuperate implicit ca parte a acestei operații, dar pot fi dezactivate.

Consultați Reorganizarea și reconstruirea indexurilor pentru mai multe informații.

Nu utilizați SHRINK cu excepția opțiunii TRUNCATEONLY și chiar dacă fișierul va crește din nou, atunci ar trebui să vă gândiți greu dacă este necesar:

sqlservercentral_SHRINKFILE

0
adăugat
Este uimitor cât de multe "autorități" online sunt complet incorecte și înșelătoare, adică sugerând că ar trebui să faceți o reducere la o bază de date!
adăugat autor Nick.McDermaid, sursa

Chiar mai bine este:

EXEC sp_MSfsaueachtable 'ALTER INDEX ALL ON ? REINDEX'

sau

EXEC sp_MSfsaueachtable 'ALTER INDEX ALL ON ? REsauGANIZE'
0
adăugat

My two cents... This method follows the spec outlined on 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
0
adăugat

Am cercetat pe web și am găsit câteva articole bune. La și i-am scris funcția și scriptul de mai jos, care se reorganizează, recreează sau reconstruiesc toate indexurile dintr-o bază de date.

Mai întâi trebuie să citiți acest articol pentru a înțelege de ce nu suntem doar recrearea tuturor indexurilor.

În al doilea rând, avem nevoie de o funcție pentru a construi scriptul de creare a indexului. Deci, acest articol vă poate ajuta. De asemenea, împărtășesc funcția de lucru de mai jos.

Ultimul pas de a face o buclă în timp pentru a găsi și a organiza toate indexurile din baza de date. Acest videoclip este un exemplu pentru a face acest lucru.

Funcţie:

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 pentru că:

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 ['[email protected]+'] ON ['[email protected]+'].['[email protected]+'] 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 ['[email protected]+'] ON ['[email protected]+'].['[email protected]+'] 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
adăugat

Înainte de a lua în considerare menținerea indexurilor, este important să răspundeți la două întrebări principale:

  1. Care este gradul de fragmentare?
  2. Care este acțiunea potrivită? Reorganizați sau reconstruiți?

După cum este descris în acest articol, http : //solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/ și pentru a vă ajuta să determinați dacă ar trebui să efectuați re-indexarea indexului sau reorganizarea indexului , vă rugăm să înțelegeți următoarele:

  • Reorganizarea indexului este un proces în care SQL Server trece prin indexul existent și îl curăță. Recondiționarea indexului este un proces greu în care indexul este șters și apoi recreat de la zero, cu o structură complet nouă, fără fragmente și pagini cu spațiu gol.

  • În timp ce reorganizarea indexului este o operație de curățare pură care lasă starea sistemului așa cum este fără blocarea tabelelor și vizualizărilor afectate, procesul de reconstrucție blochează tabelul afectat pentru întreaga perioadă de reconstrucție, ceea ce poate duce la perioade lungi de timp care nu ar putea fi acceptabile în anumite medii. Având în vedere acest lucru, este clar că indexul reconstruit este un proces cu "mai puternic" soluție, dar vine cu un preț? posibile blocări lungi pe tabele indexate afectate.

Pe de alta parte, reorganizarea indexului este o "usoara" proces care va rezolva fragmentarea într-un mod mai puțin eficient? deoarece indicele curățat va fi întotdeauna cel de-al doilea decât cel nou pe deplin realizat de la zero. Indicele de reorganizare este cu mult mai bun din punctul de vedere al eficienței, deoarece nu blochează tabelul indexat afectat în timpul funcționării.

Articolul explicat mai sus explică și modul de reorganizare și reconstruire a indexurilor utilizând SSMS, T-SQL (pentru a reorganiza / reconstrui indiciile dintr-un tabel) și un instrument terț numit ApexSQL Backup.

0
adăugat
atunci când se reface indexul, este necesar să se actualizeze statistcs de tabel? mai ales dacă există un indice non clustered?
adăugat autor Moudiz, sursa