Tuning basado en Execution Plans

1. ¿Qué es un Execution Plan?

Cuando SQL Server recibe una consulta, el Query Optimizer analiza diferentes alternativas y selecciona un plan de ejecución.

El plan permite visualizar:

• cómo accede a las tablas;
• qué índices utiliza;
• cómo relaciona las tablas;
• cuántas filas estima procesar;
• qué operaciones tienen mayor costo;
• si utiliza paralelismo.

SQL Server permite analizar tanto el Estimated Execution Plan como el Actual Execution Plan.

El Actual Execution Plan es especialmente útil porque permite comparar lo que SQL Server esperaba hacer con lo que realmente ocurrió.

2. ¿Qué buscamos?

Al analizar un plan buscamos detectar:

• Table Scan o Index Scan sobre grandes volúmenes;
• Index Seek acompañado de muchos Key Lookups;
• estimaciones de filas muy diferentes a las reales;
• JOINs costosos;
• Sorts o Aggregations innecesarios;
• uso excesivo de CPU;
• operaciones paralelas que no aportan beneficio.

La pregunta principal es:

¿SQL Server está haciendo más trabajo del necesario?

3. JOINs

SQL Server puede utilizar diferentes estrategias:

• Nested Loops
• Hash Match
• Merge Join

No existe un JOIN “bueno” o “malo”.

Por ejemplo:

• Nested Loops puede ser muy eficiente con pocas filas.
• Hash Match puede ser conveniente con grandes volúmenes.
• Merge Join puede ser eficiente cuando los datos ya están ordenados.

El plan permite determinar qué estrategia eligió SQL Server y analizar si es adecuada para el volumen de datos que realmente está procesando.

4. Índices

El plan permite identificar si SQL Server utiliza:

• Index Seek;
• Index Scan;
• Table Scan;
• Key Lookup.

Un caso clásico es:

Index Seek
     ↓
Key Lookup
     ↓
Clustered Index

Si el Key Lookup se ejecuta cientos de miles de veces, puede convertirse en un problema.

Una posible solución puede ser crear un índice que incluya las columnas necesarias.

5. Estadísticas y cardinalidad

Un punto fundamental es comparar:

Estimated Rows
Actual Rows

Si SQL Server estima:

100 rows

pero realmente procesa:

2.000.000 rows

el Optimizer pudo haber tomado una decisión incorrecta debido a una estimación de cardinalidad deficiente.

Esto puede llevar a investigar:

• estadísticas;
• distribución de datos;
• parámetros;
• predicates;
• índices.

6. Paralelismo y Query Hints

SQL Server puede ejecutar determinadas consultas utilizando múltiples threads.

El plan permite identificar operadores de Parallelism.

El tuning puede involucrar:

• MAXDOP;
• Cost Threshold for Parallelism;
• configuración de la instancia;
• Query Hints.

También es posible influir explícitamente sobre determinadas decisiones del Optimizer, por ejemplo:

• cantidad máxima de threads;
• estrategia de JOIN;
• utilización de determinados índices.

Los hints deben utilizarse como una herramienta controlada y no como sustituto de la corrección de estadísticas, índices o SQL.

7. Forzar un plan de ejecución

SQL Server permite forzar un plan de ejecución determinado y mantenerlo asociado a una consulta.

Esto es especialmente útil cuando:

• una consulta tenía un plan eficiente;
• posteriormente el Optimizer selecciona otro plan;
• el nuevo plan genera una regresión de performance.

De esta manera podemos estabilizar temporalmente el comportamiento de una consulta mientras se busca una solución definitiva.

El concepto es:

Plan eficiente
      ↓
Regresión
      ↓
Nuevo plan menos eficiente
      ↓
Forzar plan conocido
      ↓
Performance estabilizada

8. ¿Cómo optimizamos?

Dependiendo del problema podemos:

• crear o modificar índices;
• actualizar estadísticas;
• modificar la consulta;
• revisar JOINs;
• reducir la cantidad de datos procesados;
• revisar Key Lookups;
• ajustar paralelismo;
• utilizar Query Hints;
• forzar un plan conocido cuando sea necesario.

Después se genera nuevamente el Actual Execution Plan y se comparan los resultados.

9. Objetivo

El objetivo es que SQL Server:

lea menos → procese menos → utilice menos CPU/I/O → responda más rápido.

El Execution Plan permite entender por qué SQL Server eligió determinada estrategia y dónde podemos intervenir para mejorarla.