--
=========================================================================================
-- 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);
