Reorganizar y volver a generar índices - optimizar la consulta de datos

Los indices de una tabla, por ejemplo la tabla Department del esquema HumanResources de la base de datos AdventureWorks2012, se pueden listar con la siguiente instrucción:

--
--
use AdventureWorks2012
GO;

SELECT 
s.name,
tab.name as tablename,
ind.name as indexname  
FROM sys.indexes ind 
INNER JOIN sys.tables tab ON ind.object_id = tab.object_id
INNER JOIN sys.schemas s on tab.schema_id = s.schema_id
where tab.name = 'Department' and s.name = 'HumanResources'
ORDER BY  s.name,tab.name

Cada vez que se realizan operaciones de inserción, actualización o eliminación de los datos de una tabla, el motor de base de datos de SQL Server da mantenimiento a los indices de dicha tabla. Sin embargo, con el tiempo la información del índice se dispersa por la base de datos, es decir, se fragmenta, ocasionando que la ordenación lógica que está basada en el valor de clave no coincida con la ordenación física dentro del archivo de datos. Cuando los índices están muy fragmentados hay mayor probabilidad de que las consultas a los datos de la tabla sean lentas. La fragmentación del índice se soluciona reorganizándolo o volviéndolo a generar. El proceso de volver a generar un índice quita y vuelve a crear el índice. Para saber si los índices de una tabla están fragmentados podemos usar la siguiente consulta:

--
--
SELECT a.index_id, name as nombre_indice, avg_fragmentation_in_percent  as porcentaje_fragmentacion
FROM sys.dm_db_index_physical_stats (DB_ID(N'BD_TRIBUTACION'), 
      OBJECT_ID(N'dbo.Cta_Corriente'), NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b 
      ON a.object_id = b.object_id AND a.index_id = b.index_id;   
GO  

-- o en AdventureWorks2012
SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), 
      OBJECT_ID(N'HumanResources.Department'), NULL, NULL, NULL) AS a  
    JOIN sys.indexes AS b 
      ON a.object_id = b.object_id AND a.index_id = b.index_id;   
GO

Para reorganizar un índice en una tabla se debe emplear la siguiente instrucción:

--
--
ALTER INDEX [NombreDelIndice] ON [schema].[TablaDondeEstaElIndice] REORGANIZE

Para reconstruir un índice en una tabla se debe emplear la siguiente instrucción:

--
--
ALTER INDEX [NombreDelIndice] ON [schema].[TablaDondeEstaElIndice] REBUILD

Para volver a reconstruir todos los índices de una tabla determinada usar:

--
--
USE BD_TRIBUTACION; 
GO
-- Reorganizar todos los índices de la tabla dbo.Cta_corriente.
ALTER INDEX ALL ON dbo.Cta_corriente
REBUILD; 
GO

Para obtener información más técnica acerca de la fragmentación de los índices, puede consultar el siguiente enlace: https://technet.microsoft.com/es-es/library/ms189858(v=sql.110).aspx. En esta página se menciona que la decisión de si se reorganiza o se reconstruye un índice de una tabla depende del porcentaje de fragmentación, si está entre un 5% y 30% es mejor REORGANIZARLO, en caso sea mayor al 30% de fragmentación entonces es mejor RECONSTRUIRLO, si es menor a un 5% no es conveniente aplicarle ningún tratamiento. A continuación he elaborado un script que permite reorganizar reconstruir o dejar intacto los índices de una tabla dada en función a la recomendación mencionada del porcentaje de fragmentación.

--
--

SELECT a.index_id, name as nombre_indice, OBJECT_name(a.object_id) as nombre_tabla,
avg_fragmentation_in_percent as porcentaje_fragmentacion, 
case when avg_fragmentation_in_percent <= 30 then 'RO' -- REORGANIZAR
else 'RI' -- REINDEXAR 
END as 'ACCION'
into #reorganizar_reindexar
FROM sys.dm_db_index_physical_stats (DB_ID('N'BD_TRIBUTACION'), OBJECT_ID(N'dbo.cta_corriente'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent >= 5 

--select * from #reorganizar_reindexar

DECLARE @i int = 1, @ix int
declare @nombre_indice varchar(200), @nombre_tabla varchar(200), @accion varchar(10)
declare @sql nvarchar(200)=''


select top 1 @i = index_id, @nombre_tabla = nombre_tabla, @nombre_indice = nombre_indice, @accion = accion
from #reorganizar_reindexar
order by index_id

while @i is not null
begin
 --RAISERROR(@i, 0, 1) WITH NOWAIT
 Print @i
 RAISERROR(N'', 0, 1) WITH NOWAIT

 if @accion = 'RO'
 begin
  set @sql = 'ALTER INDEX ' + @nombre_indice + ' ON dbo.' + @nombre_tabla + ' REORGANIZE' --WITH (ONLINE = ON);
 end 
 else
 begin
  set @sql = 'ALTER INDEX '+  @nombre_indice + ' ON dbo.' + @nombre_tabla + ' REBUILD' --WITH (ONLINE = ON);;
 end
 
 --RAISERROR(@sql, 0, 1) WITH NOWAIT
 Print @sql
 RAISERROR(N'', 0, 1) WITH NOWAIT
 execute sp_executesql @sql

 set @ix = null
 select top 1 @ix = index_id, @nombre_tabla = nombre_tabla, @nombre_indice = nombre_indice, @accion = accion
 from #reorganizar_reindexar
 where index_id > @i
 order by index_id

 set @i = @ix
 
end

drop table #reorganizar_reindexar

Utilizo RAISERROR tan solo para ver los mensajes durante la ejecución del WHILE, porque de otra manera los mensajes se muestran al final del while o después de cierto tiempo, por ello el RAISEERROR muestra el mensaje inmediatamente.

2/04/2018 04:30:00 p. m. Share: