Los índices en la base de datos SQL Server es una herramienta que nos ayuda a mejorar la respuesta de nuestras consultas, pero un problema muy recurrente es tener índices repetidos o similares, que nos puede llegar a ocacionar en la mayoría de los casos problemas de rendimiento o que el motor de la base de datos ni lo toma en cuenta.
En este artículo te explico como encontrar esos índices duplicados para analizar que medidas tomar, ya sea modificarlos o eliminar los que no estemos utilizando.
MERGE in SQL Server for Insert, Delete and Update with two tables - Practical example using MERGE to synchronize two tables, Insert, Update and Delete in a single… ¿Por qué tenemos índices duplicados?
Pueden existir distintas razones, te detallamos las más comunes:
- No existe alguien responsable de la base de datos, un DBA, y cada desarrollador crea los índices que el cree necesarios, y así cada desarrollador por separado, lo que lleva a acumular índices que se repiten, muchos no se terminan utilizando.
- Utilizar herramientas de análisis de consultas, las cuales hacen recomendaciones de índices y crearlos sin analizar si ya existen similares. Las herramientas de optimización de consultas son de mucha ayuda, pero hay que utilizarlos con cuidado y mucho análisis para saber cuales índices aplicar.
¿Cuáles problemas podemos tener con índices similares o repetidos?
Los índices repetidos parcial o total nos pueden generar una serie de problemas:
- Problema de rendimiento, al tener que actualizar los índices en cada insert, update o delete, el motor de la base de datos utiliza recursos extra.
- Al hacer las consultas SQL debe analizar los índices a utilizar, lo que puede ocacionar una sobrecarga inecesaria.
- Vamos a tener backups de mayor tamaño, el motor de la base de datos por cada índice almacena los datos (como si fuera una tabla) para retornarlos en nuestras consultas, por lo que si tenemos índices repetidos vamos a tener la información duplicada, y si estas tablas son de gran tamaño el consumo puede ser un problema de megas o gigas que nos podemos ahorrar.
- Transaction Log de gran tamaño, ocacionado por la actualización de los índices, que nos puede ocacionar problemas de espacio en disco. Aumentado si no tenemos un correcto plan de backups de nuestras bases de datos.
- Sobrecarga del servidor al reorganizar o reconstruir índices.
¿Cómo encontrar índices repetidos?
Vamos a tomar como ejemplo la siguiente tabla
CREATE TABLE [dbo].[Producto]( [codigoProducto] [int] NOT NULL, [codigoBarra] [varchar](20) NOT NULL, [codigoCategoria] [int] NOT NULL, [nombreProducto] [varchar](100) NOT NULL, [fechaRegistro] [datetime] NOT NULL, [fechaActualizado] [datetime] NOT NULL, [activo] [tinyint] NOT NULL, [precio] [money] NOT NULL, [cuentaContable] [varchar](20) NOT NULL, ) GO ALTER TABLE [dbo].[Producto] ADD CONSTRAINT [PK_Producto] PRIMARY KEY CLUSTERED ([codigoProducto]) GO
SQL de forma automática nos va a crear el índice para la llave primaria codigoProducto.
Para el ejemplo vamos a tener los siguientes índices, 2 iguales y los otros similares. Este es un ejemplo extremo, pero necesitamos bastantes ejemplos para entender en análisis más facilmente.
- IX_Producto_cProducto_cBarra
- IX_Producto_cProducto_cBarra_codCategoria
- IX_Producto_cProd_cBarra_cCategoria
- IX_Producto_cProducto_cBarra_activo
Por el nombre de los índices podemos notar que codigoProducto y codigoBarra lo repetimos en todos los índices, los otros índices agegan columnas a los otros índices.
Solución
Después de buscar en internet varios ejemplos adapté dos ejemplos para nos requerimientos. Links a los blogs al final del post.
El siguiente script nos va a retornar informacion por tabla, índice, columnas compartidas. Debe de retornar una información similar a la imagen siguiente.
Como podemos ver nos retorna el nombre del índice, agrupado por la tabla para tener más orden, cuales son las columnas en conflicto y las columnas que utiliza cada indice, indicando cuales están repetidas.
El siguiente script es una base de trabajo y lo puedes modificar según tus requerimientos.
WITH BaseNumbers AS (
SELECT 1 AS ColId
UNION ALL
SELECT ColId+1
FROM BaseNumbers
--Columnas maximas por indice por analizar
--WHERE ColId<32
)
, IndexColumns AS (
SELECT Sch.name AS SchemaName,
objetos.name AS TableName,
indices.name AS IndexName,
indices.type_desc AS IndexType,
indices.index_id,
Idc.key_ordinal,
Col.name AS ColumnName
FROM sys.indexes AS indices
INNER JOIN sys.objects AS objetos
ON indices.object_id = objetos.object_id
INNER JOIN sys.schemas AS SCH
ON objetos.schema_id = SCH.schema_id
JOIN sys.index_columns AS Idc
ON indices.index_id = Idc.index_id
AND indices.object_id = Idc.object_id
INNER JOIN sys.columns AS Col
ON Col.column_id = Idc.column_id
AND Col.object_id = Idc.object_id
WHERE indices.index_id > 0 --- Not MS Products
--- Not MS Products
AND objetos.is_ms_shipped=0
--- Solo Tablas
AND objetos.type in ('U ')
--- CLUSTERED, NONCLUSTERED AND NONCLUSTERED HASH
AND indices.type IN (1,2,7)
--- Sin included columns
AND Idc.is_included_column=0
)
, IndexColumnsAgg AS (
SELECT SchemaName, TableName, IndexName, IndexType, index_id, key_ordinal AS NumeroColumna
, CAST(ColumnName AS VARCHAR(MAX)) AS IndexColumns
FROM IndexColumns
WHERE key_ordinal=1
UNION ALL
SELECT AGG.SchemaName, AGG.TableName, AGG.IndexName, AGG.IndexType, IC.index_id AS NumeroColumna, IC.key_ordinal
, CAST(CONCAT(AGG.IndexColumns, ', ', ColumnName) AS VARCHAR(MAX)) AS IndexColumns
FROM IndexColumnsAgg AS AGG
JOIN IndexColumns AS IC
ON AGG.SchemaName=IC.SchemaName
AND AGG.TableName=IC.TableName
AND AGG.IndexName=IC.IndexName
AND AGG.NumeroColumna+1=IC.key_ordinal
)
, IndexColumnsAggExt AS (
SELECT *,
LAST_VALUE(NumeroColumna)
OVER(PARTITION BY SchemaName, TableName, IndexName ORDER BY NumeroColumna ASC
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS TotalCantidadColumnas
, LAST_VALUE(IndexColumns)
OVER(PARTITION BY SchemaName, TableName, IndexName ORDER BY NumeroColumna ASC
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FullIndexColumns
FROM IndexColumnsAgg
)
, IndexPairs AS (
SELECT L.SchemaName AS 'Schema'
, L.TableName AS Nombretabla
, L.IndexName AS PrimerIndice
, R.IndexName AS SegundoIndice
, L.FullIndexColumns AS PrimerIndiceColumnas
, R.FullIndexColumns AS SegundoIndiceColumnas
, L.IndexColumns AS ColumnasCompartidas
, L.IndexType AS PrimerTipoIndice
, R.IndexType AS SegundoTipoIndice
, L.TotalCantidadColumnas AS CantColumnasPrimerIndice
, R.TotalCantidadColumnas AS CantColumnasSegundoIndice
, L.NumeroColumna AS CantidadColumnasCompartidas
, CASE WHEN L.TotalCantidadColumnas > R.TotalCantidadColumnas
THEN L.TotalCantidadColumnas
ELSE R.TotalCantidadColumnas END AS CantidadMaximaColumnas
, ROW_NUMBER() OVER(PARTITION BY L.SchemaName, L.TableName, L.IndexName, R.IndexName
ORDER BY L.NumeroColumna DESC) AS Pos
FROM IndexColumnsAggExt AS L
JOIN IndexColumnsAggExt AS R
ON L.SchemaName = R.SchemaName
AND L.TableName = R.TableName
AND L.IndexColumns = R.IndexColumns
AND L.NumeroColumna = R.NumeroColumna
AND L.index_id < R.index_id
)
SELECT C.Criteria as 'Criterio',
IndexPairs.*
FROM IndexPairs
CROSS APPLY (SELECT
CASE
WHEN CantidadColumnasCompartidas = CantidadMaximaColumnas
THEN 'Iguales'
WHEN CantColumnasPrimerIndice = CantidadMaximaColumnas OR
CantColumnasSegundoIndice = CantidadMaximaColumnas
THEN 'Similares'
ELSE 'Compartidas'
END AS Criteria) AS C
WHERE Pos=1
ORDER BY Nombretabla, PrimerIndice, Criterio
Espero que el artículo te sea de utilidad y no dudes en escribirme sobre consultas de este artículo y sobre cualquier otro tema de SQL Server.
Frases relacionadas:
- Problema con índices en SQL Server
- Rendimiento con índices en SQL Server
- SQL Server no utiliza mi índice
- ¿Los índices consumen espacio en disco?
- Índices clustered y nonclustered SQL Server
Referencias:
