Nexus - AI Task Orchestrator

10-database-performance-review
Procedimiento: Revisi├│n de Rendimiento de Base de Datos

Base de Datos
1 plugin(s)
Titulo
Descripcion
Contenido Markdown
15009 caracteres
# Procedimiento: Revisi├│n de Rendimiento de Base de Datos

## Metadata
- **ID**: PROC-10
- **Frecuencia**: Semanal, o cuando se detectan timeouts/lentitud
- **Duraci├│n estimada**: 30-60 min
- **Requiere**: Acceso SQL Server (lectura + permisos DMV), SSMS o Azure Data Studio
- **Dependencias**: Ninguna
- **Bloquea**: Cambios de ├¡ndices requieren ventana de mantenimiento (solo en HERMES)

## Objetivo

Identificar y resolver problemas de rendimiento en base de datos: queries lentas, ├¡ndices faltantes, ├¡ndices no usados, y bloqueos.

## Arquitectura de Servidores

| Servidor | Alias | Permisos | Bases de Datos |
|----------|-------|----------|----------------|
| `api.dmi.es` | HERMES | Lectura + Escritura | Hermes_v1.2, Hermes_v1.3, IA, PricingCacheDB, etc. |
| `sql.dmi.es` | LEGACY | **Solo Lectura** | CommercePortal_COMMERCE |

**ÔÜá´©Å IMPORTANTE**: El servidor LEGACY tiene `ApplicationIntent=ReadOnly`. NO ejecutar cambios (CREATE INDEX, UPDATE STATISTICS, etc.) en ese servidor.

**­ƒôØ NOTA**: HERMES usa SQL Server 2016 (pre-2017). Funciones como `STRING_AGG` no est├ín disponibles. Usar alternativas compatibles.

## Connection Strings

Ubicaci├│n: `SupplierCatalog.Sync/appsettings.json`
```json
{
  "DatabaseSettings": {
    "ConnectionString": "Data Source=api.dmi.es;Initial Catalog=Hermes_v1.2;..."
  },
  "LegacySettings": {
    "ConnectionString": "Data Source=sql.dmi.es;Initial Catalog=CommercePortal_COMMERCE;...;ApplicationIntent=ReadOnly"
  }
}
```

## Par├ímetros de Ejecuci├│n

Antes de iniciar, definir:

| Par├ímetro | Valor | Opciones |
|-----------|-------|----------|
| **Servidor** | ___ | `HERMES (api.dmi.es)` / `LEGACY (sql.dmi.es)` |
| **Base de Datos** | ___ | Ver lista abajo |
| **Modo** | ___ | `An├ílisis` / `An├ílisis + Cambios` (solo HERMES) |

### Bases de Datos Disponibles

**HERMES (api.dmi.es)** - An├ílisis + Cambios:
| BD | Uso en proyecto |
|----|-----------------|
| `Hermes_v1.2` | Icecat: features, filtros, profiles, ScopeFilterProfile |
| `Hermes_v1.3` | UlisesIQ: AI feedback |
| `IA` | Datos IA general |
| `PricingCacheDB` | Cache de precios |

**LEGACY (sql.dmi.es)** - Solo An├ílisis:
| BD | Uso en proyecto |
|----|-----------------|
| `CommercePortal_COMMERCE` | Cat├ílogo, productos, clientes, pedidos |

## Checklist Ejecutable

### 0. Conectar a la base de datos seleccionada
```sql
-- En SSMS o Azure Data Studio
-- Conectar al servidor seleccionado

-- HERMES
-- Server: api.dmi.es
-- Authentication: SQL Server Authentication
-- User: sa

-- LEGACY (solo an├ílisis)
-- Server: sql.dmi.es
-- Authentication: SQL Server Authentication
-- User: rmcarlavilla
-- Options > Additional Connection Parameters: ApplicationIntent=ReadOnly

-- Seleccionar la BD
USE [Hermes_v1.2];  -- Cambiar seg├║n par├ímetro elegido
GO
```

- [ ] Conectado al servidor: ___
- [ ] Base de datos seleccionada: ___
- [ ] Modo: An├ílisis / An├ílisis + Cambios

### 1. Identificar queries lentas (Top 10)
```sql
-- Top 10 queries por tiempo promedio de ejecuci├│n
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_elapsed_time_ms,
    qs.execution_count,
    qs.total_elapsed_time / 1000 AS total_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS query_text,
    DB_NAME(qt.dbid) AS database_name,
    qs.creation_time AS plan_created,
    qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.dbid = DB_ID()
  AND qs.execution_count > 10
ORDER BY avg_elapsed_time_ms DESC;
```

- [ ] Ejecutado
- [ ] Queries con avg > 1000ms: ___

### 2. Detectar ├¡ndices faltantes sugeridos
```sql
-- ├ìndices que SQL Server recomienda crear
SELECT TOP 20
    CONVERT(DECIMAL(18,2),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
    ) AS improvement_measure,
    migs.user_seeks,
    migs.user_scans,
    CONVERT(DECIMAL(18,2), migs.avg_total_user_cost) AS avg_cost,
    CONVERT(DECIMAL(18,2), migs.avg_user_impact) AS avg_impact_percent,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id)
        + '_' + CONVERT(VARCHAR, mid.index_handle) + '] ON '
        + mid.statement + ' ('
        + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL
               THEN ', ' ELSE '' END
        + ISNULL(mid.inequality_columns, '') + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;
```

- [ ] Ejecutado
- [ ] ├ìndices con improvement > 10000: ___
- [ ] ÔÜá´©Å Si servidor LEGACY: Solo anotar, NO crear

### 3. Revisar ├¡ndices de tabla espec├¡fica
```sql
-- Cambiar @TableName seg├║n necesidad
DECLARE @TableName NVARCHAR(128) = 'IcecatProductFeatures';

SELECT
    i.name AS index_name,
    i.type_desc AS index_type,
    i.is_unique,
    i.is_primary_key,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns,
    STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name END, ', ')
        WITHIN GROUP (ORDER BY ic.key_ordinal) AS included_columns
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID(@TableName)
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, i.index_id
ORDER BY i.index_id;
```

**Tablas cr├¡ticas por BD:**

| BD | Tablas a revisar |
|----|------------------|
| Hermes_v1.2 | `IcecatProductFeatures`, `IcecatConversationProfile`, `ScopeFilterProfile`, `IcecatProductJson` |
| Hermes_v1.3 | `AiFeedbackEvent`, `AiConversationSummary` |
| CommercePortal_COMMERCE | `Products`, `COMMERCE_COMERCIALES`, `Orders` (solo an├ílisis) |

- [ ] Tablas cr├¡ticas revisadas: ___

### 4. Detectar ├¡ndices no utilizados
```sql
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,
    ISNULL(ius.user_seeks, 0) AS user_seeks,
    ISNULL(ius.user_scans, 0) AS user_scans,
    ISNULL(ius.user_lookups, 0) AS user_lookups,
    ISNULL(ius.user_updates, 0) AS user_updates,
    ius.last_user_seek,
    ius.last_user_scan,
    CASE
        WHEN (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0)) = 0
             AND ISNULL(ius.user_updates, 0) > 100
        THEN 'CANDIDATO A ELIMINAR'
        WHEN (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0)) < 10
        THEN 'REVISAR'
        ELSE 'OK'
    END AS recommendation
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.type_desc <> 'HEAP'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
ORDER BY (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0));
```

- [ ] Ejecutado
- [ ] ├ìndices candidatos a eliminar: ___
- [ ] ÔÜá´©Å Si servidor LEGACY: Solo documentar

### 5. Analizar query espec├¡fica problem├ítica
```sql
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Pegar query problem├ítica aqu├¡
-- SELECT ...

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
```

En SSMS: Ctrl+M para plan de ejecuci├│n gr├ífico

| S├¡ntoma en Plan | Problema | Soluci├│n |
|-----------------|----------|----------|
| Table Scan en tabla grande | Falta ├¡ndice | Crear ├¡ndice (solo HERMES) |
| Index Scan (no Seek) | ├ìndice no ├│ptimo | Revisar columnas |
| Key Lookup (alto costo) | Faltan columnas INCLUDE | Modificar ├¡ndice (solo HERMES) |
| Nested Loop con muchas filas | JOINs ineficientes | Optimizar query |

- [ ] Query analizada
- [ ] Problema: ___
- [ ] Soluci├│n propuesta: ___

### 6. Verificar bloqueos activos
```sql
SELECT
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    DB_NAME(blocked.database_id) AS database_name,
    blocked.wait_type,
    blocked.wait_time / 1000 AS wait_time_seconds,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.blocking_session_id <> 0;
```

- [ ] Bloqueos activos: ___

### 7. Revisar estad├¡sticas desactualizadas
```sql
SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter,
    CASE
        WHEN sp.modification_counter > sp.rows * 0.2 THEN 'ACTUALIZAR'
        WHEN DATEDIFF(DAY, sp.last_updated, GETDATE()) > 7 THEN 'REVISAR'
        ELSE 'OK'
    END AS recommendation
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
    AND sp.modification_counter > 1000
ORDER BY sp.modification_counter DESC;
```

- [ ] Estad├¡sticas desactualizadas: ___
- [ ] ÔÜá´©Å Si servidor LEGACY: Solo documentar

### 8. Documentar hallazgos

| # | Tabla | Problema | Severidad | Servidor | Acci├│n | Estado |
|---|-------|----------|-----------|----------|--------|--------|
| 1 | | | | HERMES/LEGACY | | |
| 2 | | | | | | |

### 9. Aplicar cambios (SOLO HERMES)

**ÔÜá´©Å VERIFICAR: Servidor = HERMES (api.dmi.es)**
```sql
-- SOLO ejecutar en HERMES, NUNCA en LEGACY

-- Crear ├¡ndice
CREATE NONCLUSTERED INDEX [IX_TableName_Columns]
ON dbo.TableName (Column1, Column2)
INCLUDE (Column3, Column4)
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

-- Actualizar estad├¡sticas
UPDATE STATISTICS dbo.TableName WITH FULLSCAN;

-- Eliminar ├¡ndice no usado (despu├®s de 7+ d├¡as sin uso)
DROP INDEX [IX_IndexName] ON dbo.TableName;
```

- [ ] Ô£à Verificado que estoy en servidor HERMES
- [ ] Cambios aplicados: ___

### 10. Verificaci├│n post-cambio
```sql
-- Verificar que nuevo ├¡ndice se usa
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    ius.user_seeks,
    ius.user_scans,
    ius.last_user_seek
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
WHERE i.name = 'IX_NuevoIndice';
```

- [ ] ├ìndice siendo usado: S├¡ / No / Pendiente (esperar carga)

## Criterios de Decisi├│n

### Cu├índo crear ├¡ndice (solo HERMES)

| Criterio | Umbral |
|----------|--------|
| improvement_measure | > 5000 |
| avg_elapsed_time_ms | > 1000ms |
| Ejecuciones/d├¡a | > 100 |
| Table Scan en tabla | > 10,000 rows |

### Cu├índo reportar a equipo LEGACY

Si encuentras problemas en CommercePortal_COMMERCE:
- Documentar hallazgo completo
- Crear ticket con: query, plan, sugerencia
- NO aplicar cambios directamente

## Troubleshooting

| S├¡ntoma | Causa | Acci├│n |
|---------|-------|--------|
| No puedo conectar a HERMES | Credenciales/firewall | Verificar VPN, credenciales |
| No puedo conectar a LEGACY | ReadOnly rechazado | A├▒adir ApplicationIntent=ReadOnly |
| DMVs vac├¡as | SQL Server reci├®n reiniciado | Esperar carga representativa |
| Permission denied en DMV | Falta permiso VIEW SERVER STATE | Solicitar permiso |

## Resultado

- **├ëxito**:
  - Queries lentas identificadas y optimizadas (HERMES) o documentadas (LEGACY)
  - ├ìndices sugeridos evaluados
  - Sin bloqueos cr├│nicos

- **Parcial**:
  - Problemas en LEGACY documentados, pendiente ticket

- **Fallo**:
  - No se pudo conectar ÔåÆ Verificar credenciales
  - Problemas cr├¡ticos sin resolver ÔåÆ Escalar

## Advertencias

- **NUNCA** ejecutar cambios (CREATE, DROP, UPDATE) en servidor LEGACY
- **NO** eliminar ├¡ndices sin 7+ d├¡as de datos de no uso
- Las DMVs se resetean con cada reinicio de SQL Server
- Probar cambios en staging antes de producci├│n (cuando aplique)

## Mensaje de Finalizaci├│n

**IMPORTANTE - ACCI├ôN REQUERIDA AL FINALIZAR:**

Cuando hayas completado todos los pasos de este procedimiento, DEBES ejecutar el siguiente comando usando la herramienta Bash:

Ejecuta: `echo "====== PROCESO TERMINADO [$(date +%H%M%S)] ======" && echo "RESULTADO: BD analizada, X queries lentas, Y indices sugeridos"`

Sustituye X e Y por los n├║meros reales.


---

## 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-10 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-10"]
    }
  ],
  "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:**
- queries_analyzed, slow_queries, optimizations_applied

**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 | Ejecutor | Servidor | BD | Problemas | Acciones | Resultado |
|-------|----------|----------|-----|-----------|----------|-----------|
| | | | | | | |