Reconstruir índices en SQL Server

Reconstruir los índices en SQL Server

Índices en SQL Server

Un índice de SQL Server es una estructura en disco o en memoria asociada con una tabla o vista que acelera la recuperación de filas de la tabla o vista. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista.
El diseño eficaz de los índices tiene gran importancia para conseguir un buen rendimiento de una base de datos y una aplicación, es por ese motivo que no solamente es muy útil crearlos sino cada cierto tiempo reconstruirlos, este tiempo para a depender de la cantidad de información que cambia en el indice en la tabla o vista.

Para mayor información ver
Índices en SQL Server
Índices particionados en SQL Server
Índices en vistas
Modificación de Índices
Cursores en SQL Server
Variables tipo tabla
Procedimientos almacenados
Esquemas en SQL Server

Ejercicio

Para este ejercicio se va a usar la base de datos Northwind, se va a crear un cursor que guarde los datos de los índices para crear la instrucción de reconstrucción del índice, todo será en un procedimiento almacenado para reconstruir los índices.

Los metadatos

SQL Server guarda los datos de los objetos de la base de datos en lo que se conoce como metadatos, estos metadatos se almacenan en vista del sistema, para este ejemplo vamos a usar las siguientes vistas del sistema:
sys.tables que tiene la información de las tablas.
sys.schemas que tiene la información de los esquemas
sys.indexes que tiene la información de los índices
sys.dm_db_index_physical_stats que tiene la información de las estadísticas de los índices.

Usando la base de datos Northwind
Abrir la base de datos

use northwind
go

El procedimiento almacenado recibe de manera opcional dos parámetros, uno que indica que los indices se van a reconstruir y el otro con el valos de fragmentación máxima del índice. Se crea la variable tipo tabla llamada @ListaIndices y se incluyen los datos necesarios para crear la instrucción para reconstruir el índice.

Instrucción Alter index

Permite modificar el índice.
Para el ejemplo será:
Alter Index NombreDelIndice ON [Esquema].[Tabla] Rebuild WITH (ONLINE = OFF)

Procedimiento almacenado para reconstruir los índices.

El procedimiento almacenado es como sigue:

CREATE or alter PROCEDURE dbo.ReconstruirIndices
@MostrarRecontruir nvarchar(10) = ‘Mostrar’,
@FragmentacionMaxima decimal(20, 2) = 20.0
AS
BEGIN
— Declarar variables
SET NOCOUNT ON
DECLARE
@Esquema nvarchar(128), @Tabla nvarchar(128),
@Indice nvarchar(128), @InstruccionAlterIndex nvarchar(4000),
@IdBaseDatos int, @IdEsquema int,
@IdTabla int, @lndexId int;
— Variable tipo tabla para los indices
DECLARE @ListaIndices TABLE
(
BaseDatos nvarchar(128) NOT NULL,
IdBaseDatos int NOT NULL,
Esquema nvarchar(128) NOT NULL,
IdEsquema int NOT NULL,
NombreTabla nvarchar(128) NOT NULL,
IdTabla int NOT NULL,
NombreIndice nvarchar(128),
IdIndice int NOT NULL,
Fragmentacion decimal(20, 2),
PRIMARY KEY (IdBaseDatos, IdEsquema, IdTabla, IdIndice) )
— Llenar la lista de Indices, la información se obtiene de las vista de sistema — Tables, Schemas, Indexes y dm_db_index_physical_stats

INSERT INTO @ListaIndices
(BaseDatos, IdBaseDatos, Esquema, IdEsquema,
NombreTabla, IdTabla, NombreIndice, IdIndice, Fragmentacion)
SELECT db_name(), db_id(), S.Name, S.schema_id, T.Name, T.object_id, I.Name, I.index_id, MAX(F.avg_fragmentation_in_percent) FROM sys.tables As T Join sys.schemas As S ON T.schema_id = S.schema_id Join sys.indexes As I ON T.object_id = I.object_id Join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) As F ON F.object_id = T.object_id AND F.index_id = I.index_id WHERE F.database_id = db_id() Group by S.Name, S.schema_id, T.Name, t.object_id, I.Name, I.index_id;

— Si recibe el parámetro comprobar si es Rebuild para reconstruir.
IF @MostrarRecontruir = ‘Rebuild’
BEGIN
— Cursor para crear las instrucciones SQL
DECLARE CursorInstruccionesIndices Cursor Fast_Forward
FOR SELECT Esquema, NombreTabla, NombreIndice
FROM @ListaIndices
WHERE Fragmentacion > @FragmentacionMaxima
Order by Fragmentacion DESC, NombreTabla Asc, NombreIndice Asc
OPEN CursorInstruccionesIndices
Fetch Next From CursorInstruccionesIndices INTO @Esquema, @Tabla, @Indice
— Recorrer el cursor
While (@@FETCH_STATUS = 0)
BEGIN
— Crear la instrucción Alter index
SET @InstruccionAlterIndex = ‘Alter Index ‘ + QUOTENAME(RTRIM(@Indice)) +
‘ ON ‘ + QUOTENAME(RTRIM(@Esquema)) + ‘.’ + QUOTENAME(RTRIM(@Tabla)) +
‘ Rebuild WITH (ONLINE = OFF) ‘
PRINT @InstruccionAlterIndex
— Para mostrar el en panel de mensajes la instrucción.
— Ejecutar la instrucción
Execute (@InstruccionAlterIndex)
Fetch Next From CursorInstruccionesIndices INTO @Esquema, @Tabla, @Indice
END
CLOSE CursorInstruccionesIndices
DEALLOCATE CursorInstruccionesIndices
END

— Mostrar resultados
SELECT
L.BaseDatos As ‘Base de datos’ , L.Esquema As ‘Esquema’,
L.NombreTabla As ‘Tabla’, L.NombreIndice As ‘Índice’,
L.Fragmentacion As ‘Fragmentación Inicial’,
Max(Cast(F.avg_fragmentation_in_percent As Decimal(20, 2))) As ‘Fragmentación Final’
FROM @ListaIndices As L
join sys.dm_db_index_physical_stats(@IdBaseDatos, NULL, NULL, NULL, NULL) As F
ON L.IdBaseDatos = F.database_id AND L.IdTabla = F.object_id
AND L.IdIndice= F.index_id
Group by L.BaseDatos, L.Esquema, L.NombreTabla, L.NombreIndice, L.Fragmentacion
Order by L.Fragmentacion Desc, L.NombreTabla Asc, L.NombreIndice Asc
Return
End
go

Ejecutar el SP para reconstruir los índices de la base de datos abierta.

Execute ReconstruirIndices Rebuild
go
El resultado se muestra en la siguiente imagen.

Las instrucciones creadas mostradas con el comando Print se muestran en la siguiente imagen.

Importante
Se recomienda crear un plan de mantenimiento e incluir una tarea para ejecutar el SP que reconstruya los índices.
Ver Planes de mantenimiento.