Esta es una versión mejorada del procedimiento automático de desfragmentación de índices, que usa una tabla para almacenar el resultado de la consulta y así evita usar una y otra vez dm_db_index_physical_stats que tiene serios problemas de rendimiento.
USE <BaseDatos>;
GO
CREATE FUNCTION FilteredIndexFragmentation(
@DatabaseID INT
, @ObjectID INT
, @IndexID INT
, @PartitionNumber INT=NULL
, @AverageFragmentation INT =0
, @FragmentCount BIGINT =0)
— Author: Javier Loria, Solid Quality Mentors
— Create date: 5/Dic/2008
— Description: Funcion que lista los indices con un porcentaje de fragmentacion LOGICA mayor al indicado,
— y con una cantidad mayor de fragmentos.
— Encapsula dm_db_index_physical_stats., se requiere para poder hacer CROSS APPLY.
— No reporta fragmentacion de tablas sin indices, indices XML o Geograficos.
— Emplea el modo limitado ‘LIMITED’, por el alto costo y mal desempeno del modo ‘DETAILED’
RETURNS @IndexStats TABLE(
DatabaseID SMALLINT
, ObjectID INT
, IndexID INT
, PartitionNumber INT
, IndexDepth TINYINT
, FragmentationRate FLOAT
, FragmentCount BIGINT
, AverageFragmentSize FLOAT
, PageCount BIGINT)
BEGIN
INSERT INTO @IndexStats(DatabaseID, ObjectID, IndexID, PartitionNumber, IndexDepth,FragmentationRate
, FragmentCount, AverageFragmentSize, PageCount)
SELECT database_id, object_id, index_id, partition_number,
index_depth, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count
FROM sys.dm_db_index_physical_stats (@DatabaseID, @ObjectID, @IndexID, @PartitionNumber, ‘LIMITED’ )
WHERE index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)
AND avg_fragmentation_in_percent > @AverageFragmentation
AND fragment_count>@FragmentCount
RETURN
END
GO
CREATE TABLE dbo.FragmentacionIndices(
Name sysname NOT NULL
PRIMARY KEY
, Indice varchar(512) NOT NULL
, Tamano varchar(8) NOT NULL
, TotalPages bigint NOT NULL
);
GO
CREATE PROCEDURE [dbo].[DefragmentaIndices](
@Tipo VARCHAR(10)=‘Grandes’ — Medianas, Pequenas, Genera
)
AS
DECLARE @db_id INT;
DECLARE @NumPages BIGINT;
DECLARE @NumIndexes INT;
DECLARE @Comando NVARCHAR(MAX);
DECLARE @DB INT
— Parametros “ALAMBRADOS”
— +8192 Paginas: Grande
— +256 Paginas: Mediano
— -256 Paginas: Pequeno
— -32 Paginas: No Defragmenta
— La tabla se llena con las 400 Tablas + Grandes
— Grandes: Defragmenta 10, Medianas: Defragmenta 10, Pequenas: Defragmanta 100
SET NOCOUNT ON;
SET @DB=DB_ID() –Requerido por modo de compatibilidad 80.
IF (@Tipo NOT IN(‘Grandes’, ‘Medianas’, ‘Pequenas’, ‘Genera’))
BEGIN
RAISERROR(‘Parametro @Tipo Invalido, use: Grandes, Medianas o Pequenas’, 16,1);
RETURN;
END
SET @db_id = DB_ID();
SET @Comando=”;
IF @Tipo=‘Genera’
BEGIN
TRUNCATE TABLE dbo.FragmentacionIndices;
INSERT INTO dbo.FragmentacionIndices(Name, Indice, Tamano, TotalPages)
SELECT TOP 400
IndexPages.Name
, ‘ALTER INDEX ‘
+ IndexPages.Name
+‘ ON ‘+OBJECT_NAME(ObjectID)+‘ REBUILD;’ AS Indice
, CASE WHEN totalPages>=8192 THEN ‘Grandes’
WHEN totalPages>=256 THEN ‘Medianas’
ELSE ‘Pequenas’ END AS Tamano
, TotalPages
FROM (SELECT indexes.object_id
, indexes.index_id
, Indexes.Name
, sum(allocation_units.total_pages) as totalPages
FROM sys.indexes AS indexes
JOIN sys.partitions AS partitions
ON indexes.object_id = partitions.object_id
and indexes.index_id = partitions.index_id
JOIN sys.allocation_units AS allocation_units
ON partitions.partition_id = allocation_units.container_id
WHERE indexes.index_id >0
AND allocation_units.total_pages>0
GROUP BY indexes.object_id, indexes.index_id, Indexes.Name
HAVING sum(allocation_units.total_pages)> 32) AS IndexPages
— CROSS APPLY FilteredIndexFragmentation(@DB, IndexPages.object_id, IndexPages.index_id, NULL, 20,3) AS FIF
JOIN FilteredIndexFragmentation(@DB, NULL, NULL, NULL, 20,3) AS FIF
ON IndexPages.object_id=FIF.ObjectID
AND IndexPages.index_id=FIF.IndexID
ORDER BY (IndexDepth*IndexDepth*FragmentationRate*FragmentCount/100) DESC;
RETURN;
END
IF @Tipo=‘Grandes’
BEGIN
— Reindexa las 10 mas grandes sin importar el tamano
SET @Comando=”
SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice
FROM dbo.FragmentacionIndices
ORDER BY TotalPages DESC;
— Borra
DELETE dbo.FragmentacionIndices
FROM dbo.FragmentacionIndices
JOIN (SELECT TOP 10 Name FROM dbo.FragmentacionIndices
ORDER BY TotalPages DESC) AS A
ON FragmentacionIndices.Name=A.Name
END
IF @Tipo=‘Medianas’
BEGIN
— Reindexa las 10 mas grandes, que no sean “Grandes” (Medianas, Pequenas)
SET @Comando=”
SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice
FROM dbo.FragmentacionIndices
WHERE Tamano<>‘Grandes’
ORDER BY TotalPages DESC;
DELETE dbo.FragmentacionIndices
FROM dbo.FragmentacionIndices
JOIN (SELECT TOP 10 Name
FROM dbo.FragmentacionIndices
WHERE Tamano<>‘Grandes’
ORDER BY TotalPages DESC) AS A
ON FragmentacionIndices.Name=A.Name
END
IF @Tipo=‘Pequenas’
BEGIN
— Reindexa las 100 Pequenas + Grandes
SET @Comando=”
SELECT TOP 100 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice
FROM dbo.FragmentacionIndices
WHERE Tamano=‘Pequenas’
ORDER BY TotalPages DESC;
DELETE dbo.FragmentacionIndices
FROM dbo.FragmentacionIndices
JOIN (SELECT TOP 100 Name
FROM dbo.FragmentacionIndices
WHERE Tamano=‘Pequenas’
ORDER BY TotalPages DESC) AS A
ON FragmentacionIndices.Name=A.Name
END
EXEC sp_executesql @Comando
GO
/*
— Pruebas
EXEC [dbo].[DefragmentaIndices] ‘Genera’
SELECT * FROM dbo.FragmentacionIndices
EXEC [dbo].[DefragmentaIndices] ‘Grandes’
EXEC [dbo].[DefragmentaIndices] ‘Medianas’
EXEC [dbo].[DefragmentaIndices] ‘Pequenas’
—
*/
