Index Tuning

1. Detailed MetricsSQL Server

UID: sql_server_index_tuningVersión: 1.3.3 Paneles: 9 Queries SQL: 10

Propósito

Este dashboard proporciona información detallada sobre el estado de los índices en SQL Server, permitiendo identificar:

• Índices faltantes que podrían mejorar el rendimiento
• Índices no utilizados que consumen espacio innecesariamente
• Índices duplicados que generan overhead de mantenimiento
• Tablas Heap sin índice clustered

Importancia de la Gestión de Índices

Los índices son estructuras fundamentales para el rendimiento de cualquier base de datos SQL Server. Una gestión adecuada de índices puede significar la diferencia entre una consulta que toma milisegundos y una que tarda minutos.

Beneficios de una buena estrategia de índices:

Aspecto	Sin índices adecuados	Con índices optimizados
Lecturas	Table scans completos	Seeks directos a los datos
CPU	Alto consumo en ordenamiento	Datos pre-ordenados
I/O	Lectura de páginas innecesarias	Lectura mínima de páginas
Bloqueos	Bloqueos de tabla extensos	Bloqueos de fila precisos
Tiempo de respuesta	Segundos a minutos	Milisegundos

Impacto real en el negocio:

• Las consultas lentas afectan directamente la experiencia del usuario
• El exceso de I/O impacta en toda la instancia, no solo en una consulta
• Los bloqueos prolongados pueden causar timeouts en aplicaciones
• Un índice faltante en una tabla crítica puede degradar todo el sistema

Dato importante

Según Microsoft, los problemas de índices son responsables de más del 60% de los casos de rendimiento deficiente en SQL Server. El Missing Index DMV puede identificar índices que mejorarían el rendimiento hasta en un 99%.

Variables

Variable	Descripción	Tipo
$Server_Name	Servidor SQL a analizar	Query (hc_server_configuration)
$Database	Base de datos (o todas)	Query con opción "-- All Databases"
$Table	Tabla específica (o todas)	Query con opción "-- All Tables"

---

Paneles

Resumen de Índices

Index Summary Stats stat

Muestra contadores globales del estado de índices en la instancia seleccionada.

Métrica	Descripción	Acción si es alto
Duplicated	Índices duplicados detectados	Revisar y eliminar duplicados
Missing	Índices sugeridos por SQL Server	Evaluar creación
Unused	Índices creados pero no utilizados	Considerar eliminación
Total Index	Total de índices creados	Referencia
Tables Clustered	Tablas con índice clustered	Ideal: todas las tablas
Tables Non-Clustered	Tablas con solo índices NC	Normal

Recomendación

Un alto número de índices Missing combinado con queries lentas indica que el sistema podría beneficiarse significativamente de crear índices adicionales.

---

Missing Indexes

Missing Indexes table

Índices que SQL Server recomienda crear basándose en el análisis de queries ejecutadas.

Columnas importantes:

Columna	Significado
Equality Columns	Columnas usadas en condiciones WHERE col = valor
Inequality Columns	Columnas usadas en WHERE col > valor, BETWEEN, etc.
Included Columns	Columnas que deberían incluirse (INCLUDE)
User Seeks	Veces que se habría usado el índice para seeks
Avg User Impact	Porcentaje de mejora estimada (0-100%)
Create Statement	Script listo para crear el índice

Importante

No crear todos los índices sugeridos automáticamente. Evaluar:

• Impacto en operaciones de escritura (INSERT/UPDATE/DELETE)
• Espacio en disco necesario
• Tablas con alta actividad de escritura

Interpretación de User Impact:

Impacto	Prioridad	Acción
> 90%	Alta	Crear pronto
50-90%	Media	Evaluar
< 50%	Baja	Opcional

---

Unused Indexes

Unused Indexes table

Índices que existen pero no han sido utilizados desde el último reinicio del servicio SQL.

Candidatos a eliminación:

Un índice es candidato a eliminar si:

• User Seeks = 0
• User Scans = 0
• User Lookups = 0
• User Updates > 0 (indica overhead de mantenimiento)

Precaución

Antes de eliminar un índice:

1. Verificar que SQL Server lleva suficiente tiempo ejecutándose (preferiblemente > 1 semana)
2. Considerar si el índice se usa solo en procesos mensuales/anuales
3. Guardar el script de creación antes de eliminar

---

Duplicate Indexes

Duplicate Indexes table

Índices que son redundantes porque otro índice cubre las mismas columnas.

Tipos de duplicados:

Tipo	Descripción	Acción
Exacto	Mismas columnas clave e incluidas	Eliminar uno
Subset	Un índice contiene al otro	Eliminar el más pequeño
Overlap	Columnas parcialmente iguales	Evaluar caso por caso

---

Heap Tables

Heap Tables (Without Clustered Index) table

Tablas sin índice clustered que almacenan datos de forma desordenada.

¿Por qué evitar Heaps?

• Fragmentación: Los datos se almacenan sin orden, causando I/O aleatorio
• Table Scans: Sin clustered index, muchas queries hacen full table scans
• Forwarding pointers: Updates que aumentan el tamaño de filas crean punteros extra

Excepciones aceptables:

• Tablas de staging para cargas ETL
• Tablas muy pequeñas (< 1000 filas)
• Tablas de solo inserción sin búsquedas

---

Mejores Prácticas de Índices

Cuándo Crear Índices

Escenarios ideales para crear un índice:

Escenario	Tipo de índice recomendado
Columna frecuente en WHERE con igualdad	Non-clustered en esa columna
Columna usada en JOINs	Non-clustered (o clustered si es PK)
Columnas frecuentes en ORDER BY	Non-clustered cubriendo el orden
Consultas que siempre traen las mismas columnas	Índice con INCLUDE
Tabla sin PK natural	Clustered con identity o GUID secuencial
Búsquedas por rango de fechas	Clustered o NC en columna de fecha

Indicadores para crear un índice:

• Missing Index con User Seeks alto (> 1000) y Avg Impact > 80%
• Queries frecuentes que hacen Table Scan en tablas > 10,000 filas
• Consultas de reporting que tardan más de lo aceptable
• Bloqueos frecuentes en tablas específicas

Cuándo NO Crear Índices

Evitar crear índices en estos casos:

Situación	Razón
Tablas muy pequeñas (< 1000 filas)	El overhead supera el beneficio
Columnas con baja selectividad	Valores muy repetidos (ej: Sí/No, Activo/Inactivo)
Tablas con ratio escritura/lectura muy alto	El costo de mantener el índice es mayor que el beneficio
Missing Index con User Seeks < 100	Uso insuficiente para justificar el índice
Columnas que cambian frecuentemente	Genera fragmentación y overhead
Ya existe un índice que cubre esas columnas	Duplicaría el esfuerzo de mantenimiento

Regla general

No crear más de 5-7 índices por tabla en sistemas OLTP. Para tablas con alta actividad de escritura, considerar un máximo de 3-4 índices.

Mantenimiento de Índices: Reorganize vs Rebuild

El mantenimiento regular de índices es crucial para mantener el rendimiento. SQL Server ofrece dos operaciones principales:

Comparativa de operaciones:

Característica	REORGANIZE	REBUILD
Fragmentación objetivo	10% - 30%	> 30%
Bloqueo	Mínimo (online)	Bloqueo de tabla (offline) u online con Enterprise
Uso de recursos	Bajo	Alto
Tiempo de ejecución	Menor	Mayor
Actualiza estadísticas	No	Sí
Reconstruye páginas	No, solo las reordena	Sí, completamente
Transaccional	Puede detenerse y continuar	Todo o nada (offline)

Estrategia recomendada por nivel de fragmentación:

Fragmentación	Acción	Frecuencia sugerida
< 10%	Ninguna	N/A
10% - 30%	REORGANIZE	Semanal
> 30%	REBUILD	Semanal o según necesidad
> 50%	REBUILD urgente	Inmediato

Consejo práctico

Programar mantenimiento de índices en ventanas de bajo uso. Para sistemas 24/7, usar REBUILD ONLINE (requiere Enterprise Edition) o REORGANIZE que tiene impacto mínimo.

Fill Factor Recomendado

El Fill Factor determina el porcentaje de espacio en cada página de índice que se llena durante la creación o rebuild. El espacio restante permite inserciones futuras sin causar page splits.

Guía de Fill Factor según patrón de uso:

Tipo de tabla	Fill Factor	Razón
Solo lectura (lookup tables)	100%	No hay inserciones, máxima densidad
Lecturas >> Escrituras	90-95%	Pocas inserciones, buen balance
Balance lectura/escritura	80-90%	Espacio para inserciones moderadas
Escrituras >> Lecturas	70-80%	Reduce page splits frecuentes
Inserción masiva con valores aleatorios (GUIDs)	60-70%	GUIDs causan inserciones en medio de páginas
Tabla con identity (inserción al final)	95-100%	Las inserciones son siempre al final

Impacto de Fill Factor incorrecto:

• Fill Factor muy alto (100%) en tablas con inserciones: Causa page splits frecuentes, fragmentación inmediata
• Fill Factor muy bajo (< 70%) en tablas de lectura: Desperdicia espacio en disco, más I/O para leer los mismos datos

---

Impacto en Operaciones DML

Cada índice tiene un costo de mantenimiento. Cuando se ejecuta una operación de modificación de datos (INSERT, UPDATE, DELETE), SQL Server debe actualizar todos los índices afectados.

Costo por Operación

Operación	Impacto en Índices	Consideraciones
INSERT	Todos los índices deben actualizarse	Cada índice adicional = más I/O y más bloqueos
UPDATE	Solo índices con columnas modificadas	Si la columna está en la clave del índice, el costo es mayor
DELETE	Todos los índices deben actualizarse	Similar a INSERT, debe eliminar de cada índice

Cálculo del Overhead

Para estimar el impacto de índices en operaciones de escritura:

Número de índices NC	Overhead aproximado en INSERT
1-2	Mínimo (< 10% adicional)
3-5	Moderado (10-30% adicional)
6-10	Significativo (30-60% adicional)
> 10	Alto (> 60% adicional)

Advertencia para tablas OLTP

En tablas con alta frecuencia de INSERT (ej: logs, transacciones, auditoría), cada índice adicional puede degradar significativamente el rendimiento de escritura. Evaluar cuidadosamente la necesidad real de cada índice.

Síntomas de Exceso de Índices

• Tiempos de INSERT/UPDATE/DELETE aumentando gradualmente
• Wait stats mostrando PAGEIOLATCH_EX y PAGELATCH_EX elevados
• Unused Indexes con alto número de User Updates pero cero User Seeks/Scans
• Log de transacciones creciendo rápidamente durante cargas de datos

---

Estrategia de Evaluación

Utilice el siguiente workflow sistemático para evaluar las recomendaciones del dashboard y tomar decisiones informadas.

Workflow de Evaluación de Missing Indexes

┌─────────────────────────────────────────────────────────────────┐
│                    MISSING INDEX DETECTADO                       │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│  1. EVALUAR MÉTRICAS BÁSICAS                                    │
│     - User Seeks > 1000?                                        │
│     - Avg User Impact > 80%?                                    │
│     - Last User Seek reciente (últimos 7 días)?                 │
└─────────────────────────────────────────────────────────────────┘
                              │
              ┌───────────────┴───────────────┐
              │ Sí a todas                    │ No
              ▼                               ▼
┌──────────────────────────┐    ┌──────────────────────────────┐
│ Continuar evaluación     │    │ Baja prioridad - Documentar  │
│                          │    │ y revisar en próximo ciclo   │
└──────────────────────────┘    └──────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  2. VERIFICAR ÍNDICES EXISTENTES                                │
│     - ¿Existe índice similar que podría ampliarse?              │
│     - ¿Las columnas están en otro índice como INCLUDE?          │
│     - ¿Duplicaría un índice existente?                          │
└─────────────────────────────────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  3. ANALIZAR LA TABLA                                           │
│     - Tamaño de la tabla (filas y MB)                           │
│     - Ratio lectura/escritura                                   │
│     - Número actual de índices NC                               │
│     - ¿Es tabla crítica para el negocio?                        │
└─────────────────────────────────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  4. DECISIÓN                                                    │
│     - Crear índice (si beneficio > costo)                       │
│     - Modificar índice existente (agregar columnas INCLUDE)     │
│     - Descartar (documentar razón)                              │
│     - Postergar (monitorear en siguiente ciclo)                 │
└─────────────────────────────────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  5. SI SE CREA EL ÍNDICE                                        │
│     - Probar primero en ambiente de desarrollo/staging          │
│     - Crear en horario de bajo uso                              │
│     - Monitorear impacto en escrituras después de crear         │
│     - Verificar uso real después de 1 semana                    │
└─────────────────────────────────────────────────────────────────┘

Checklist de Evaluación

Antes de crear cualquier índice recomendado, responda estas preguntas:

Pregunta	Si es Sí	Si es No
¿El User Impact es > 80%?	Continuar	Considerar prioridad baja
¿La tabla tiene < 5 índices NC?	Continuar	Evaluar consolidación
¿La tabla tiene más lecturas que escrituras?	Continuar	Precaución extra
¿El índice beneficiaría queries críticas?	Alta prioridad	Prioridad normal
¿Existe índice similar modificable?	Modificar existente	Crear nuevo
¿Se probó en ambiente no productivo?	Implementar	Probar primero

---

Guía de Uso

Workflow recomendado

1. Revisar Missing Indexes con impacto > 80%
2. Evaluar Unused Indexes grandes (> 100 MB)
3. Eliminar Duplicate Indexes obvios
4. Añadir clustered index a Heaps grandes con actividad

Frecuencia de revisión

Tarea	Frecuencia
Revisar Missing Indexes	Semanal
Evaluar Unused Indexes	Mensual
Limpiar Duplicates	Trimestral
Convertir Heaps	Según necesidad
Mantenimiento (Rebuild/Reorganize)	Semanal
Revisar Fill Factor	Anual o cuando hay cambios de patrón

---

Referencias de Microsoft

Para profundizar en los conceptos de índices y su gestión, consulte la documentación oficial de Microsoft:

Fundamentos de Índices

• Guía de diseño y arquitectura de índices - Guía completa sobre tipos de índices y diseño
• Índices en SQL Server - Página principal de documentación de índices

DMVs de Índices

• sys.dm_db_missing_index_details - Detalles de índices faltantes
• sys.dm_db_index_usage_stats - Estadísticas de uso de índices
• sys.dm_db_index_physical_stats - Fragmentación y estadísticas físicas

Mantenimiento

• Reorganizar y volver a generar índices - Guía oficial de mantenimiento
• Especificar factor de relleno para un índice - Configuración de Fill Factor

Mejores Prácticas

• Solución de problemas: Índices faltantes - Cómo usar las recomendaciones de Missing Indexes
• Directrices para el diseño de índices agrupados - Cuándo usar clustered vs non-clustered