REORGANIZE OR REBUILD INDEX

-- =========================================================================================

-- AUTOR:           MANUEL OMAR OLGUÍN HERNÁNDEZ

-- FECHA            15-JUL-2013

-- DESCRIPCIÓN      REORGANIZA O RECONSTRUYE LOS ÍNDICES DE LA BASES DE DATOS QUE CONTENGAN FRAGMENTACIÓN SOBRE SUS TABLAS ENTRE

-- 5  - 30 % REORGANIZE

-- 31 - 100% REBUILD

--

-- NOTA:            EN LA BASE SELECCIONADA SEGÚN EL CONTEXTO

-- =========================================================================================

USE DATABASENAME;

GO

 SELECT I.NAME AS INDICE , T.NAME AS TABLENAME, S.NAME AS SCHEMANAME, AVG_FRAGMENTATION_IN_PERCENT

             ,IND.INDEX_TYPE_DESC

             ,IND.DATABASE_ID

             ,IND.INDEX_ID

             ,DB.NAME AS DATABASE_NAME

             ,ID = ROW_NUMBER () OVER (ORDER BY I.NAME, IND.INDEX_ID)

 INTO #INDICES

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (NULL,null,NULL,NULL,NULL)AS IND

 INNER JOIN SYS.INDEXES AS I ON IND.OBJECT_ID = I.OBJECT_ID

 INNER JOIN SYS.TABLES AS T ON IND.OBJECT_ID = T.OBJECT_ID

 INNER JOIN SYS.SCHEMAS AS S ON T.SCHEMA_ID = S.SCHEMA_ID

 INNER JOIN SYS.DATABASES AS DB ON IND.DATABASE_ID = DB.DATABASE_ID

 WHERE IND.INDEX_ID > 0 AND I.NAME IS NOT NULL

 AND IND.AVG_FRAGMENTATION_IN_PERCENT BETWEEN 5 AND 100

 -- SELECT * FROM #INDICES order by ID

 DECLARE @ID               INT

 DECLARE @INDICE           NVARCHAR(500)

 DECLARE @INDEX_ID         INT

 DECLARE @SCHEMANAME       VARCHAR(200);

 DECLARE @TABLENAME        VARCHAR(200);

 DECLARE @STRSQL           NVARCHAR(500);

 DECLARE @DESCRIPTION      NVARCHAR (500);

 DECLARE @INDEX_TYPE       NVARCHAR (50);

 DECLARE @INSTRUCCION      NVARCHAR(150);

 DECLARE @FRAGMENTATION    DECIMAL (8,2)

 SET @ID =0

 WHILE (@ID IS NOT NULL)

  BEGIN

  /*

   SET @SCHEMANAME  = (SELECT TOP 1 SCHEMANAME                                  FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   SET @TABLENAME   = (SELECT TOP 1 TABLENAME                                   FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   SET @INDICE             = (SELECT TOP 1 INDICE                                             FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   SET @DESCRIPTION = (SELECT TOP 1 AVG_FRAGMENTATION_IN_PERCENT   FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   */

   IF @ID IS NOT NULL

             BEGIN

                SELECT           TOP 1

                                        @SCHEMANAME         = SCHEMANAME,

                                        @TABLENAME          = TABLENAME,

                                        @INDICE                    = INDICE,

                                        @FRAGMENTATION      = AVG_FRAGMENTATION_IN_PERCENT,

                                        @INDEX_TYPE         = INDEX_TYPE_DESC,                           

                                        @INDEX_ID           = INDEX_ID

                FROM                    #INDICES

                WHERE ID > @ID

                ORDER BY ID

                SET @ID   = (SELECT TOP 1 ID                                    FROM #INDICES WHERE ID > @ID ORDER BY ID )

                IF @FRAGMENTATION <= 30 SET @INSTRUCCION = 'REORGANIZE';

                          

                      --IF @FRAGMENTATION > 30  SET @INSTRUCCION = 'REBUILD';

                IF @FRAGMENTATION > 30  SET @INSTRUCCION = 'REBUILD PARTITION = ALL WITH (FILLFACTOR = 85)';

                SET @STRSQL             = ('ALTER INDEX ' + @INDICE + ' ON ' + @SCHEMANAME + '.' + @TABLENAME + ' ' + @INSTRUCCION)

                SET @DESCRIPTION = 'ID:' + CONVERT(VARCHAR, @ID) +  '  INDEX:' + CONVERT(VARCHAR,@INDEX_ID) + ' ' + @STRSQL + '   - ' + @INDEX_TYPE  + '   AVG FRAGMENTATION:' + CONVERT(VARCHAR,@FRAGMENTATION);

                RAISERROR (@DESCRIPTION,0,1) WITH NOWAIT

                EXECUTE SP_EXECUTESQL @STRSQL

             END

   

 END

 

   SELECT I.NAME AS INDICE , T.NAME AS TABLENAME, S.NAME AS SCHEMANAME, AVG_FRAGMENTATION_IN_PERCENT

             ,IND.INDEX_TYPE_DESC

             ,IND.DATABASE_ID

             ,IND.INDEX_ID

             ,DB.NAME AS DATABASE_NAME

             ,ID = ROW_NUMBER () OVER (ORDER BY I.NAME, IND.INDEX_ID)

 INTO #INDICES_REBUILD

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (NULL,null,NULL,NULL,NULL)AS IND

 INNER JOIN SYS.INDEXES AS I ON IND.OBJECT_ID = I.OBJECT_ID

 INNER JOIN SYS.TABLES AS T ON IND.OBJECT_ID = T.OBJECT_ID

 INNER JOIN SYS.SCHEMAS AS S ON T.SCHEMA_ID = S.SCHEMA_ID

 INNER JOIN SYS.DATABASES AS DB ON IND.DATABASE_ID = DB.DATABASE_ID

 WHERE IND.INDEX_ID > 0 AND I.NAME IS NOT NULL

 AND IND.AVG_FRAGMENTATION_IN_PERCENT BETWEEN 0 AND 100

 SELECT             A.Indice, A.SchemaName, A.TableName

                    ,OLD_FRAGMENTATION = CONVERT(DECIMAL(5,2),(A.AVG_FRAGMENTATION_IN_PERCENT))

                    ,NEW_FRAGMENTATION = CONVERT(DECIMAL(5,2),(B.AVG_FRAGMENTATION_IN_PERCENT))

                    ,Fragmentation = CONVERT(VARCHAR(10), CONVERT(DECIMAL(5,2), (A.AVG_FRAGMENTATION_IN_PERCENT)  - (B.AVG_FRAGMENTATION_IN_PERCENT))) + '%'

                    ,A.INDEX_ID 

                    ,A.INDEX_TYPE_DESC AS INDEX_TYPE

                    ,A.DATABASE_NAME

                   

 FROM        #INDICES                   AS A

 LEFT JOIN   #INDICES_REBUILD    AS B ON A.TABLENAME = B.TABLENAME AND A.SCHEMANAME = B.SCHEMANAME AND A.DATABASE_ID = B.DATABASE_ID AND A.INDEX_ID = B.INDEX_ID AND A.INDICE = B.INDICE

 ORDER BY A.SCHEMANAME, A.TABLENAME, A.INDICE, A.INDEX_ID

 DROP TABLE #INDICES;

 DROP TABLE #INDICES_REBUILD

  /* To rebuild all indexes in a table changing the fillfactor (cuando se hace el rebuild conviene ajustar el fill factor de nuevo, por si ya está lleno)*/

--ALTER INDEX ALL ON Avispa.Brokers

--REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);