Home > Scripts > Deframmentazione indici

Deframmentazione indici

Abbiamo trattato il problema della frammentazione degli indici nei post Index Fragmentation e Index Fragmentation (Parte 2) definendo la stored procedure USP_ExecReorgRebuildIndex() in grado di eseguire la deframmentazione degli indici con avg_fragmentation_in_percent maggiore del 10% in un database SQL Server 2005.

 

La DMV sys.dm_db_index_physical_stats non è presente nella versione 2000 di SQL Server (di cui esistono numerose installazioni attive in produzione) da qui la non compatibilità della stored procedure USP_ExecReorgRebuildIndex() su SQL Server 2000.

 

La necessità di deframmentare gli indici di un database SQL Server 2000 mi ha portato a realizzare la stored procedure USP_DBCC_IndexDefrag_DBReindex() definita di seguito.

 

— Drop procedure dbo.USP_DBCC_IndexDefrag_DBReindex if exists

IF OBJECT_ID(‘USP_DBCC_IndexDefrag_DBReindex’) IS NOT NULL

  DROP PROCEDURE dbo.USP_DBCC_IndexDefrag_DBReindex

GO

 

— Create procedure dbo.USP_DBCC_IndexDefrag_DBReindex

CREATE PROCEDURE dbo.USP_DBCC_IndexDefrag_DBReindex

 (@MaxFrag AS DECIMAL,

  @Exec AS INT,

  @Print AS INT,

  @DefragMode AS INT,

  @FillFactor AS INT)

AS BEGIN

 

  /*

    Descrizione:

 

      Utilizzo di DBCC SHOWCONTIG, DBCC INDEXDEFRAG e DBCC DBREINDEX per

      eseguire la deframmentazione degli indici di un database

 

    Parametri:

 

      @MaxFrag = Massima frammentazione consentita (in percentuale)

 

      @Exec = La stored procedure esegue la deframmentazione dell’indice

              (@Exec = -1 esegue, @Exec <> -1 non esegue)

 

      @Print = La stored procedure restituisce in output (stampa)

               i comandi REORGANIZE o REBUILD dell’indice

               (@Print = -1 stampa i comandi, @Print <> -1 non stampa i comandi)

 

      @DefragMode = Modalità di deframmentazione (1= DBCC INDEXDEFRAG, 2= DBCC DBREINDEX)

 

      @FillFactor = FillFactor per DBCC DBREINDEX

 

  */

 

  DECLARE @index_id AS INT,

          @index_name SYSNAME,

          @action_to_do_1 AS VARCHAR(1024),       

          @action_to_do_2 AS VARCHAR(1024),       

          @action_to_do_3 AS VARCHAR(1024),       

          @LogicalFrag FLOAT,

          @table_name AS VARCHAR(512)

 

  SET NOCOUNT ON

 

  IF ((@DefragMode <> 1) AND (@DefragMode <> 2))

  BEGIN

    — Modalità di deframmentazione non prevista

    PRINT ‘Il valore del parametro @DefragMode(=’ + LTRIM(STR(@DefragMode)) + ‘) è errato.’

    PRINT ‘Valori ammessi per @DefragMode: 1 per DBCC INDEXDEFRAG, 2 per DBCC DBREINDEX.’

    PRINT ‘Deframmentazione annullata.’

    PRINT

  END

  ELSE BEGIN

    — Drop tabella dbo.FragList

    IF (OBJECT_ID(‘FragList’) IS NOT NULL)

      DROP TABLE dbo.FragList

 

    — Create tabella dbo.FragList

    CREATE TABLE dbo.FragList (

      ObjectName CHAR (255),

      ObjectId INT,

      IndexName CHAR (255),

      IndexId INT,

      Lvl INT,

      CountPages INT,

      CountRows INT,

      MinRecSize INT,

      MaxRecSize INT,

      AvgRecSize INT,

      ForRecCount INT,

      Extents INT,

      ExtentSwitches INT,

      AvgFreeBytes INT,

      AvgPageDensity INT,

      ScanDensity DECIMAL,

      BestCount INT,

      ActualCount INT,

      LogicalFrag DECIMAL,

      ExtentFrag DECIMAL)

 

    — ShowContig Into dbo.FragList di tutti gli indici del database corrente

    INSERT INTO dbo.FragList

    EXEC (‘DBCC SHOWCONTIG WITH ALL_LEVELS, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)

 

    — Declare cursore CUR

    DECLARE CUR CURSOR FOR

      SELECT

        F.IndexId,

        F.IndexName,

        F.LogicalFrag,

        ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(F.objectid) + ‘, ‘ +

                                  RTRIM(F.indexid) + ‘) ‘ AS action_to_do_1,

        ‘DBCC DBREINDEX (”’ + RTRIM(T.Table_Catalog) + ‘.’ +

                               RTRIM(T.Table_Schema) + ‘.’ +

                               RTRIM(T.Table_Name) + ”’, ‘ +

                               RTRIM(F.IndexName) + ‘, ‘ +

                               LTRIM(STR(12)) + ‘) ‘ AS action_to_do_2,

        RTRIM(T.Table_Catalog) + ‘.’ + RTRIM(T.Table_Schema) + ‘.’ + RTRIM(T.Table_Name) AS Table_Name

      FROM dbo.FragList F

      JOIN INFORMATION_SCHEMA.TABLES T On F.ObjectName = T.Table_Name

      WHERE (T.TABLE_TYPE = ‘BASE TABLE’)

      AND (INDEXPROPERTY(F.ObjectId, F.IndexName, ‘IndexDepth’) > 0)

      AND (F.LogicalFrag >= @MaxFrag)

      ORDER BY F.LogicalFrag ASC

 

    — Open cursore CUR

    OPEN CUR

 

    — Fetch fuori ciclo

    FETCH NEXT FROM CUR INTO @index_id, @index_name, @LogicalFrag, @action_to_do_1, @action_to_do_2, @table_name

 

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

      IF (@Exec = 1)

      BEGIN

        IF (@DefragMode = 1)

        BEGIN

          — DBCC INDEXDEFRAG

          PRINT ‘Executing DBCC INDEXDEFRAG (0, ‘ + RTRIM(@table_name) + ‘, ‘ + RTRIM(@index_id) + ‘) – fragmentation currently ‘ +

                RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’

          PRINT @action_to_do_1

          — *Exec*

          EXEC(@action_to_do_1)

          PRINT

        END

        ELSE BEGIN

          — DBCC DBREINDEX

          PRINT ‘Executing DBCC DBREINDEX (”’ + RTRIM(@table_name) + ”’, ‘ + RTRIM(@index_name) + ‘, ‘ + LTRIM(STR(12)) + ‘) – fragmentation currently ‘ +

                RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’

          PRINT @action_to_do_2

          — *Exec*

          EXEC(@action_to_do_2)

          PRINT

        END

      END

 

      IF (@Print = 1)

      BEGIN

        SET @action_to_do_3 = ‘DBCC SHOWCONTIG (”’ + RTRIM(@table_name) + ”’, 1) WITH ALL_INDEXES’

        — *Exec*

        EXEC(@action_to_do_3)

        PRINT

 

        IF (@DefragMode = 1)

        BEGIN

          — DBCC INDEXDEFRAG

          PRINT ‘Printing DBCC INDEXDEFRAG (0, ‘ + RTRIM(@table_name) + ‘, ‘ + RTRIM(@index_id) + ‘) – fragmentation currently ‘ +

                RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’

          PRINT @action_to_do_1

          PRINT

        END

        ELSE BEGIN

          — DBCC DBREINDEX

          PRINT ‘Printing DBCC DBREINDEX (”’ + RTRIM(@table_name) + ”’, ‘ + RTRIM(@index_name) + ‘, ‘ + LTRIM(STR(12)) + ‘) – fragmentation currently ‘ +

                 RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’

          PRINT @action_to_do_2

          PRINT

        END

      END

 

      FETCH NEXT FROM CUR INTO @index_id, @index_name, @LogicalFrag, @action_to_do_1, @action_to_do_2, @table_name

    END  — WHILE

 

    — Close e deallocate cursore CUR

    CLOSE CUR

    DEALLOCATE CUR

 

    — Delete tabella temporanea

    DROP TABLE dbo.fraglist

  END

 

  SET NOCOUNT OFF

END

 

 

Per ottenere informazioni sugli indici del database TestDB con Logical Scan Fragmentation maggiore o uguale al 2% sarà sufficiente eseguire:

 

USE TestDB

 

DECLARE

  @MaxFrag AS DECIMAL,

  @Exec AS INT,

  @Print AS INT,

  @DefragMode AS INT,

  @FillFactor AS INT

 

SET @MaxFrag= 2.0

SET @Exec= 0

SET @Print= 1

SET @DefragMode= 1

SET @FillFactor= 10

 

EXEC dbo.USP_DBCC_IndexDefrag_DBReindex @MaxFrag, @Exec, @Print, @DefragMode, @FillFactor

L’output sarà:

DBCC SHOWCONTIG scanning ‘TMPTAB’ table…

Table: ‘TMPTAB’ (1716201164); index ID: 1, database ID: 7

TABLE level scan performed.

– Pages Scanned…………………………..: 212

– Extents Scanned…………………………: 31

– Extent Switches…………………………: 32

– Avg. Pages per Extent……………………: 6.8

– Scan Density [Best Count:Actual Count]…….: 81.82% [27:33]

– Logical Scan Fragmentation ………………: 3.77%

– Extent Scan Fragmentation ……………….: 12.90%

– Avg. Bytes Free per Page…………………: 3059.6

– Avg. Page Density (full)…………………: 62.20%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Printing DBCC INDEXDEFRAG (0, TestDB.dbo.TMPTAB, 1) – fragmentation currently 4%

DBCC INDEXDEFRAG (0, 1716201164, 1)

 

 

Per eseguire la deframmentazione, in modalità DBCC INDEXDEFRAG, degli indici del database TestDB con Logical Scan Fragmentation maggiore o uguale al 2% sarà sufficiente eseguire:

 

 

USE TestDB

 

DECLARE

  @MaxFrag AS DECIMAL,

  @Exec AS INT,

  @Print AS INT,

  @DefragMode AS INT,

  @FillFactor AS INT

 

SET @MaxFrag= 2.0

SET @Exec= -1

SET @Print= 0

SET @DefragMode= 1

SET @FillFactor= 10

 

EXEC dbo.USP_DBCC_IndexDefrag_DBReindex @MaxFrag, @Exec, @Print, @DefragMode, @FillFactor

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

sp_alter_column – La stored procedure per modificare una colonna!

La stored procedure sp_alter_column permette di modificare agevolmente il tipo di dato ed il nome di una colonna!