Nexus - AI Task Orchestrator

13-database-index-analysis
Procedimiento: An├ílisis de ├ìndices de Base de Datos

Base de Datos
1 plugin(s)
Titulo
Descripcion
Contenido Markdown
12410 caracteres
# Procedimiento: An├ílisis de ├ìndices de Base de Datos

## Metadata
- **ID**: PROC-13
- **Frecuencia**: Semanal
- **Duraci├│n estimada**: 30-45 min
- **Requiere**: Acceso SQL Server (permisos DMV) o PostgreSQL (pg_stat)
- **Dependencias**: Ninguna
- **Bloquea**: Creaci├│n de ├¡ndices requiere ventana de mantenimiento
- **Agentes**: database-optimizer, dotnet-architect

---

## Objetivo

Analizar el uso de ├¡ndices para:
1. Detectar ├¡ndices no utilizados (candidatos a eliminaci├│n)
2. Identificar ├¡ndices faltantes (sugerencias del motor)
3. Detectar ├¡ndices duplicados o redundantes
4. Generar DDL priorizado para optimizaci├│n

---

## Detecci├│n de Stack

```yaml
# Leer de .ai/project-config.yml si existe
stack:
  database: "sqlserver"  # sqlserver | postgresql | both
```

Si no existe config, detectar autom├íticamente:
- Buscar `appsettings.json` ÔåÆ `ConnectionString` con `Data Source=` ÔåÆ SQL Server
- Buscar `appsettings.json` ÔåÆ `ConnectionString` con `Host=` o `Server=` sin `Data Source` ÔåÆ PostgreSQL

---

## Checklist Ejecutable

### 1. Conectar a la base de datos

- [ ] Identificar motor de base de datos
- [ ] Conectar con credenciales apropiadas
- [ ] Verificar permisos de lectura en vistas de sistema

---

## Para SQL Server

### 2. ├ìndices No Utilizados

```sql
-- ├ìndices que no se han usado desde el ├║ltimo reinicio del servidor
-- IMPORTANTE: Verificar uptime del servidor antes de decidir eliminar
SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates,
    ps.row_count,
    CAST(ps.reserved_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS size_mb,
    'DROP INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME(i.object_id) + '].[' + OBJECT_NAME(i.object_id) + '];' AS drop_statement
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
    ON i.object_id = us.object_id AND i.index_id = us.index_id
    AND us.database_id = DB_ID()
LEFT JOIN sys.dm_db_partition_stats ps
    ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.type > 0  -- Excluir heaps
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND OBJECT_SCHEMA_NAME(i.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
    AND (us.user_seeks = 0 AND us.user_scans = 0 AND us.user_lookups = 0)
    OR us.object_id IS NULL
ORDER BY ps.reserved_page_count DESC;
```

- [ ] Ejecutar query de ├¡ndices no utilizados
- [ ] Verificar uptime del servidor: `SELECT sqlserver_start_time FROM sys.dm_os_sys_info;`
- [ ] Marcar ├¡ndices candidatos a eliminaci├│n (si uptime > 7 d├¡as)

### 3. ├ìndices Faltantes (Sugeridos por SQL Server)

```sql
-- ├ìndices sugeridos por el motor ordenados por impacto
SELECT
    OBJECT_SCHEMA_NAME(mid.object_id) AS schema_name,
    OBJECT_NAME(mid.object_id) AS table_name,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_score,
    migs.user_seeks,
    migs.user_scans,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_'
        + REPLACE(REPLACE(REPLACE(COALESCE(mid.equality_columns, mid.inequality_columns), '[', ''), ']', ''), ', ', '_')
        + '] ON [' + OBJECT_SCHEMA_NAME(mid.object_id) + '].[' + OBJECT_NAME(mid.object_id) + '] ('
        + COALESCE(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END
        + COALESCE(mid.inequality_columns, '')
        + ')'
        + CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END
        + ';' AS create_statement
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_score DESC;
```

- [ ] Revisar top 10 ├¡ndices sugeridos
- [ ] Validar que no existan ├¡ndices similares
- [ ] Priorizar por improvement_score

### 4. ├ìndices Duplicados

```sql
-- Detectar ├¡ndices con las mismas columnas clave
WITH IndexColumns AS (
    SELECT
        i.object_id,
        i.index_id,
        i.name AS index_name,
        i.type_desc,
        (
            SELECT QUOTENAME(c.name) + ','
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH('')
        ) AS key_columns
    FROM sys.indexes i
    WHERE i.type > 0
        AND OBJECT_SCHEMA_NAME(i.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
)
SELECT
    OBJECT_SCHEMA_NAME(ic1.object_id) AS schema_name,
    OBJECT_NAME(ic1.object_id) AS table_name,
    ic1.index_name AS index1,
    ic2.index_name AS index2,
    ic1.key_columns,
    'Revisar si ' + ic2.index_name + ' es redundante' AS recommendation
FROM IndexColumns ic1
JOIN IndexColumns ic2
    ON ic1.object_id = ic2.object_id
    AND ic1.key_columns = ic2.key_columns
    AND ic1.index_id < ic2.index_id
ORDER BY OBJECT_NAME(ic1.object_id);
```

- [ ] Identificar pares de ├¡ndices duplicados
- [ ] Decidir cu├íl mantener (verificar includes y filtros)

---

## Para PostgreSQL

### 2. ├ìndices No Utilizados

```sql
-- ├ìndices que no se han usado (idx_scan = 0)
-- NOTA: Estad├¡sticas se resetean con pg_stat_reset() o reinicio
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    'DROP INDEX IF EXISTS ' || schemaname || '.' || indexrelname || ';' AS drop_statement
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'  -- No eliminar PKs
    AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;
```

- [ ] Ejecutar query de ├¡ndices no utilizados
- [ ] Verificar desde cu├índo se recolectan stats: `SELECT pg_stat_get_db_stat_reset_time(oid) FROM pg_database WHERE datname = current_database();`
- [ ] Marcar candidatos a eliminaci├│n (si stats > 7 d├¡as)

### 3. Tablas sin ├ìndices Adecuados (Candidatas a Optimizaci├│n)

```sql
-- Tablas con alto ratio de seq_scan vs idx_scan (posibles ├¡ndices faltantes)
SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    idx_scan,
    CASE WHEN seq_scan > 0
        THEN ROUND(100.0 * idx_scan / (seq_scan + idx_scan), 2)
        ELSE 100
    END AS idx_scan_pct,
    n_live_tup AS estimated_rows,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > 100  -- M├¡nimo 100 seq scans
    AND n_live_tup > 1000  -- Tablas con > 1000 filas
    AND (idx_scan = 0 OR seq_scan > idx_scan * 10)  -- Mayor├¡a seq scans
ORDER BY seq_scan DESC
LIMIT 20;
```

- [ ] Identificar tablas con muchos sequential scans
- [ ] Ejecutar `EXPLAIN ANALYZE` en queries frecuentes a esas tablas
- [ ] Proponer ├¡ndices basados en condiciones WHERE frecuentes

### 4. ├ìndices Duplicados

```sql
-- Detectar ├¡ndices con mismas columnas
SELECT
    a.indrelid::regclass AS table_name,
    a.indexrelid::regclass AS index1,
    b.indexrelid::regclass AS index2,
    pg_get_indexdef(a.indexrelid) AS index1_def,
    pg_get_indexdef(b.indexrelid) AS index2_def
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid < b.indexrelid
    AND a.indkey = b.indkey
WHERE a.indrelid::regclass::text NOT LIKE 'pg_%';
```

- [ ] Identificar pares duplicados
- [ ] Revisar definiciones completas antes de eliminar

### 5. ├ìndices con Baja Selectividad

```sql
-- ├ìndices que podr├¡an no ser ├║tiles por baja cardinalidad
SELECT
    schemaname || '.' || tablename AS table_name,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS index_size,
    (SELECT n_distinct FROM pg_stats
     WHERE schemaname = psi.schemaname
       AND tablename = psi.tablename
       AND attname = (SELECT attname FROM pg_attribute
                      WHERE attrelid = psi.indexrelid AND attnum = 1)
    ) AS first_col_distinct
FROM pg_stat_user_indexes psi
JOIN pg_indexes pi ON psi.indexrelname = pi.indexname AND psi.schemaname = pi.schemaname
WHERE idx_scan < 50
ORDER BY pg_relation_size(schemaname || '.' || indexname) DESC
LIMIT 20;
```

- [ ] Revisar ├¡ndices con n_distinct bajo
- [ ] Evaluar si el ├¡ndice aporta valor

---

## Output Esperado

### Resumen Ejecutivo
```
====== PROC-13 COMPLETADO [TIMESTAMP] ======
Motor: SQL Server / PostgreSQL
Base de datos: [nombre]
Uptime/Stats desde: [fecha]

├ìNDICES NO UTILIZADOS: X
- Total espacio recuperable: XX MB
- Candidatos seguros a eliminar: Y

├ìNDICES FALTANTES SUGERIDOS: X
- Impacto estimado alto: Y
- DDL generado: S├¡

├ìNDICES DUPLICADOS: X
- Pares identificados: Y

ACCIONES RECOMENDADAS:
1. [acci├│n prioritaria]
2. [acci├│n secundaria]
```

### Archivo de Salida
Generar `index-analysis-[fecha].sql` con:
- Secci├│n DROP para ├¡ndices no usados (comentada)
- Secci├│n CREATE para ├¡ndices sugeridos
- Notas sobre duplicados

---

## Criterios de ├ëxito

- [ ] An├ílisis completo ejecutado sin errores
- [ ] ├ìndices no utilizados identificados con DDL
- [ ] ├ìndices faltantes priorizados por impacto
- [ ] Duplicados documentados
- [ ] Archivo SQL generado para revisi├│n

---

## Alertas y Escalaci├│n

| Severidad | Condici├│n | Acci├│n |
|-----------|-----------|--------|
| CRITICAL | ├ìndice de PK marcado como no usado | Verificar - posible error en an├ílisis |
| WARNING | >10 ├¡ndices sugeridos con score alto | Programar ventana de mantenimiento |
| WARNING | ├ìndices no usados > 1GB total | Planificar limpieza |
| INFO | <5 ├¡ndices problem├íticos | Documentar y monitorear |

---

## Notas por Proyecto

| Proyecto | Motor | Notas |
|----------|-------|-------|
| DMI | SQL Server | Servidor HERMES permite cambios, LEGACY solo lectura |
| Zetes | SQL Server | Azure SQL - usar portal para ├¡ndices |
| Auvasa | PostgreSQL | Verificar vacuum despu├®s de drops |
| Veolia | SQL Server | Similar a DMI |

---


---

## Output Estructurado (Nexus)

Al finalizar, el agente DEBE generar un bloque JSON con el siguiente formato para que Nexus pueda procesarlo automaticamente:

```json
{
  "result": "success",
  "summary": "Ejecucion de PROC-13 completada. [Descripcion breve de resultados]",
  "metrics": {
    "issues_found": 0,
    "issues_resolved": 0,
    "queries_analyzed": 50, "slow_queries": 3,
    "custom": {
      "procedure_specific_metric": "value"
    }
  },
  "backlog_items": [
    {
      "title": "Titulo del item de seguimiento",
      "description": "Descripcion detallada si se requiere accion futura",
      "priority": "medium",
      "type": "improvement",
      "tags": ["proc-13"]
    }
  ],
  "next_steps": [
    "Accion recomendada 1",
    "Accion recomendada 2"
  ],
  "warnings": [
    "Advertencias encontradas durante la ejecucion"
  ]
}
```

**Campos requeridos:**
- `result`: `"success"` | `"partial"` | `"failed"`
- `summary`: Resumen ejecutivo en 1-3 lineas

**Metricas especificas de este procedure:**
- indexes_analyzed, missing_indexes, unused_indexes

**Criterios de resultado:**
- `success`: Procedimiento completado sin errores criticos
- `partial`: Completado con algunos problemas menores o items pendientes
- `failed`: Error critico o no se pudo completar

## Historial de Ejecuciones

| Fecha | Proyecto | Motor | No Usados | Faltantes | Duplicados | Acciones |
|-------|----------|-------|-----------|-----------|------------|----------|
| | | | | | | |