NOT IN en SQL Server: el anti-patrón que generó 2.500 millones de lecturas para 2.139 filas

Nota: Este artículo está basado en un incidente real de producción. Los datos de rendimiento son reales, pero los nombres de servidores, bases de datos, tablas y procedimientos han sido modificados para proteger la confidencialidad.

El problema

Una consulta diaria que busca registros huérfanos — contratos referenciados en una tabla de incidencias que no existen en la tabla maestra de contratos — se había convertido silenciosamente en el mayor consumidor de I/O de toda la base de datos: 2.478 millones de lecturas lógicas por ejecución para encontrar tan solo 2.139 filas.

La query era engañosamente simple:

SELECT DISTINCT id_contrato
INTO tmp.contratos_huerfanos
FROM dbo.incidencias
WHERE id_contrato NOT IN (
    SELECT DISTINCT id_contrato
    FROM dbo.contratos_historico
)

Objetivo: Identificar contratos referenciados en incidencias (697K filas) que no tienen registro en contratos_historico (190 millones de filas, 20 GB).

Las métricas que encendieron las alarmas

Más de un millón de lecturas por cada fila devuelta. Eso no es ineficiencia — es un desastre arquitectural.

Diagnóstico: el índice que faltaba

La tabla contratos_historico tenía 7 índices no clustered, pero ninguno tenía id_contrato como columna líder. Todos los que incluían esta columna la tenían como segunda columna tras otra columna, impidiendo un Index Seek directo.

El optimizador lo pedía explícitamente en el plan de ejecución con un impacto estimado del 93%.

El plan destructivo

Sin índice, SQL Server generaba un plan con Nested Loops + Top 1 sobre un Clustered Index Scan completo de 190 millones de filas. Para cada contrato de la tabla incidencias:

• Si el contrato SÍ existía (~695K): el Top 1 encontraba match rápidamente
• Si el contrato NO existía (2.139): tenía que escanear las 190M filas completas

Cálculo: 2.139 contratos sin match × ~2,5M páginas por scan ≈ 5.300 millones de lecturas.

El doble anti-patrón: NOT IN y NULLs

NOT IN tiene un problema semántico: si la subquery devuelve algún NULL, devuelve vacío para TODAS las filas. SQL Server debe garantizar esta semántica, complicando el plan. La alternativa NOT EXISTS no tiene este problema.

La solución: un índice de 4,6 GB

CREATE NONCLUSTERED INDEX [IX_contratos_historico_id_contrato]
ON [dbo].[contratos_historico] ([id_contrato])
WITH (ONLINE = ON, MAXDOP = 4, SORT_IN_TEMPDB = ON);

Lecciones aprendidas

1. NOT IN no es el único problema: aunque NOT EXISTS es generalmente preferible, el verdadero problema aquí era la falta de índice.

1. El ratio lecturas/fila es tu mejor indicador de alarma: más de 1.000.000 es un defecto de diseño.

1. Revisa los Missing Index del Query Store: cuando el optimizador pide un índice con impacto del 93%, escúchalo.

1. Las tablas con muchos índices NC pueden tener gaps críticos: 7 índices (80+ GB) pero ninguno cubría id_contrato como columna líder.

1. Query Store es tu mejor herramienta de diagnóstico: sin él, esta query habría seguido generando 20 mil millones de lecturas semanales en silencio.