Cómo automatizar el mantenimiento de índices
Actualizado: 2009-04-30
La siguiente secuencia de comandos comprueba todos los índices de una base de datos de Planning Server y busca los que tienen un promedio de fragmentación superior al 10 por ciento. A continuación, según el grado de fragmentación, realiza la reorganización o reconstrucción de dichos índices.
Ejemplo
Consulta de ejemplo
-- ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag < 30.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT 'Executed ' + @command;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO
Resultados de ejemplo
Los resultados siguientes son los resultados de ejemplo de la ejecución de esta consulta en una base de datos de aplicaciones de planeación.
ALTER INDEX XPKAsyncWorkItems ejecutado en dbo.AsyncWorkItems REBUILD
ALTER INDEX XPKAsyncWorkItemStatusHistory ejecutado en dbo.AsyncWorkItemStatusHistory REBUILD
ALTER INDEX XPKWorkbooks ejecutado en dbo.Workbooks REBUILD
ALTER INDEX XPKRecurrentCycles ejecutado en dbo.RecurrentCycles REBUILD
ALTER INDEX XPKCycleInstances ejecutado en dbo.CycleInstances REBUILD
ALTER INDEX XPKAG_AccountType ejecutado en dbo.AG_AccountType REBUILD
ALTER INDEX XPKAssignmentDefinitions ejecutado en dbo.AssignmentDefinitions REBUILD
ALTER INDEX XPKD_Account ejecutado en dbo.D_Account REBUILD
ALTER INDEX XD_Account_Label_U ejecutado en dbo.D_Account REBUILD
ALTER INDEX XPKAssignments ejecutado en dbo.Assignments REBUILD
ALTER INDEX MG_ConsolidationModel_MeasureGroup_default_partition_PK ejecutado en dbo.MG_ConsolidationModel_MeasureGroup_default_partition REBUILD
ALTER INDEX H_Account_Consolidation_PK ejecutado en dbo.H_Account_Consolidation REBUILD
ALTER INDEX D_Product_PK ejecutado en dbo.D_Product REBUILD
ALTER INDEX H_Product_ResortProducts_PK ejecutado en dbo.H_Product_ResortProducts REBUILD
ALTER INDEX MG_Corporate Costs_MeasureGroup_default_partition_PK ejecutado en dbo.MG_Corporate Costs_MeasureGroup_default_partition REORGANIZE
ALTER INDEX XPKRuleSetsOrRules ejecutado en dbo.RuleSetsOrRules REBUILD
ALTER INDEX RuleSetsOrRules_GUID_U ejecutado en dbo.RuleSetsOrRules REBUILD
ALTER INDEX RuleSetsOrRules_Label_U ejecutado en dbo.RuleSetsOrRules REBUILD
ALTER INDEX XPKRuleSets_Rules_Assoc ejecutado en dbo.RuleSets_Rules_Assoc REBUILD
ALTER INDEX XPKBizRoles ejecutado en dbo.BizRoles REBUILD
ALTER INDEX H_HR_HR_PK ejecutado en dbo.H_HR_HR REORGANIZE
ALTER INDEX XPKBizDataPermissions ejecutado en dbo.BizDataPermissions REBUILD
El administrador de la base de datos puede personalizar la secuencia de comandos para volver a generar o reorganizar distintos índices con distintas frecuencias. Por ejemplo, puede modificar la secuencia de comandos para volver a generar o reorganizar los índices de clúster de las tablas del grupo de medida con una frecuencia diaria, y otros índices con una frecuencia semanal.