Leer un plan real de SQL Server con evidencias

Un diagrama parece ofrecer una solución rápida: localizar el mayor porcentaje y eliminar ese operador. Sin embargo, esos porcentajes son costes estimados por el optimizador, no tiempos medidos de la ejecución observada. Una investigación útil conecta la solicitud concreta, las filas que circulan por el plan y los recursos utilizados.

Registrar lo que realmente se ejecutó

Conserva el texto SQL, los valores y tipos de parámetros, el nivel de compatibilidad y las opciones relevantes de la sesión. Pegar la consulta en otra ventana con valores distintos cambia el experimento. Activa el plan real en SSMS o utiliza STATISTICS XML. Obtenerlo ejecuta la instrucción; en un lote que modifica datos no equivale a consultar un plan estimado sin ejecutar.

El ejemplo compara la misma agregación antes y después de crear un índice. Activa el plan real y ejecútalo en una sesión de prueba.

CREATE TABLE #PlanOrders
(
    OrderId int NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    Amount decimal(12,2) NOT NULL
);
;WITH N AS
(
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT #PlanOrders (OrderId, CustomerId, Amount)
SELECT n, n % 100, CONVERT(decimal(12,2), n % 250)
FROM N;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;

CREATE INDEX IX_PlanOrders_Customer
ON #PlanOrders (CustomerId) INCLUDE (Amount);

SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
DROP TABLE #PlanOrders;

Ambos SELECT deben devolver el mismo total. Compara las lecturas lógicas de la pestaña de mensajes y examina los operadores de acceso. La segunda consulta dispone de un índice que cubre CustomerId y Amount, pero registra la elección efectiva del optimizador en lugar de garantizar que aparezca una determinada figura.

La tabla es pequeña para mantener el ensayo controlado. La selectividad y el ancho de las filas de producción pueden cambiar la decisión. Separa también la preparación de la consulta: la creación del índice aparece en el lote y consume recursos, pero su plan no corresponde al SELECT. Guarda los planes con sus medidas y no vacíes las cachés de un servidor compartido para fabricar una prueba limpia.

Seguir la primera diferencia importante de filas

Recorre el flujo desde los accesos a datos hacia el resultado. Compara filas estimadas y reales en puntos significativos. Si un filtro debía producir 100 filas y entrega 200.000, un join caro posterior puede ser consecuencia de esa estimación inicial. Corregir solamente la ordenación final dejaría intacta la causa.

Distingue filas leídas de filas devueltas. Un seek puede localizar un rango amplio y descartar casi todo mediante un predicado residual. Su nombre no demuestra que haya realizado poco trabajo. Abre las propiedades y revisa predicados de búsqueda, predicados residuales y cantidades examinadas. A veces, recorrer una tabla pequeña resulta más barato que hacer numerosas búsquedas aleatorias.

En Nested Loops importa cuántas veces se ejecuta la operación interna. Un acceso barato repetido 100.000 veces puede dominar el coste total. Comprueba si las estimaciones son por ejecución o agregadas, especialmente en planes paralelos. No compares cifras que representan unidades distintas.

Los lookups, sorts, spools y hash joins no son defectos por definición. Pregunta para qué están presentes y cuánto trabajo hacen. Un índice que elimina búsquedas adicionales puede aumentar almacenamiento y coste de escritura. Un spool puede ahorrar trabajo repetido y una ordenación puede ser un requisito real de la aplicación.

Convertir la observación en una prueba

Formula una hipótesis que puedas refutar: un predicado residual lee demasiado, un filtro sesgado provoca una mala estimación o columnas innecesarias ensanchan una ordenación. Realiza un cambio relacionado con esa hipótesis. Repite casos representativos de parámetros y compara resultados correctos, lecturas, CPU, duración y efecto bajo concurrencia.

Las advertencias merecen atención, pero no constituyen un diagnóstico completo. Un derrame a disco puede ser grave en un sistema ocupado y poco relevante en una consulta pequeña ocasional. Una recomendación de índice faltante pertenece a un contexto de optimización concreto; compárala con los índices existentes y el trabajo de escritura antes de crearla.

Por último, relaciona el trabajo del servidor con la espera del usuario. Poca CPU y pocas lecturas no descartan bloqueos, esperas de memoria ni un cliente que consume resultados lentamente. El plan real complementa las estadísticas de espera y las medidas de extremo a extremo. Conserva el plan original y la razón del cambio para poder investigar futuras regresiones con una referencia reproducible.

Referencias técnicas: Microsoft Learn: Actual execution plans · Microsoft Learn: Showplan operators · Microsoft Learn: STATISTICS IO.