lunes, diciembre 5, 2022

Índices repetidos en SQL Server ¿Cómo encontrarlos?

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.

SQL Server: Error 3414, servicio no inicia - El error 3414 se produce generalmente cuando el motor de la base de datos SQL…

¿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:

Roy Rojashttp://www.dotnetcr.com
Con más de 20 años de experiencia en programación, experto en lenguajes .NET, VB, C#, ASP.NET, Xamarin, XCode, DBA en SQL Server. Creador de dotnetcr.com, sitio web para programadores en español. royrojas.com | dotnetcr.com | GitHub

Redes Sociales

2,736FansMe gusta
326SeguidoresSeguir

Popular esta semana

Flutter – Error: ADB exited with exit code 1

En este artículo vamos a explorar algunas posibles causas y solución al error en Flutter Error: ADB exited with exit code 1....
SQL Server - Merge

MERGE en SQL Server para Insert, Delete y Update con dos tablas

Ejemplo práctico usando MERGE para sincronizar dos tablas, Insert, Update y Delete en un solo query. Válido para SQL SERVER 2008 o superior.

Convertir String a Base64 y Base64 a String

Hemos tenido algunas consultas en estos días de cómo convertir Texto String a Base64 y viceversa, entonces decidimos hacer este pequeño ejemplo. Las funciones son...

Últimos artículos

Índices repetidos en SQL Server ¿Cómo encontrarlos?

Los índices en la base de datos SQL Server es una herramienta que nos ayuda a mejorar la respuesta de nuestras consultas,...
Flutter - Instagram Clone App

Instagram Clone App con Flutter

Marvin Stanley Valenzuela nos comparte un reto y genialidad a la vez, excelente práctica de la que podemos aprender mucho, diseñó en...

¿Cómo agregar una IP adicional en una VM Ubuntu en Azure?

Lo más común al momento de crear y configurar un nuevo servidor es que a este le asigne un solo IP, pero...
flutter one year

Después de un año con Flutter, esto es lo que aprendí

La historia comenzó cuando un día desperté pensando en tener un nuevo proyecto personal. Me dije a mí misma que hay muchas...