SQL Performance Lab

El problema

Tres consultas usadas a diario en reportes de ventas, cartera e inventario eran lentas — no porque el servidor estuviera subdimensionado, sino porque cada una tenía un anti-patrón que impedía a SQL Server usar cualquier índice.

Anti-patrón 1 — Función en columna indexada (Q1)

-- ANTES: YEAR() envuelve OrderDate → SQL Server no puede usar el índice → scan completo en 169K filas
WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 1

-- DESPUÉS: rango explícito → Index Seek en IX_SalesOrders_Date_Covering
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2024-02-01'

Fix: covering index con OrderDate como leading key. Lecturas lógicas en SalesOrders: 2,856 → 19 (−99.3%). Elapsed: 5,056 ms → 2,053 ms (−59%).

Anti-patrón 2 — CONVERT no-sargable + correlated subquery (Q2)

-- ANTES: CONVERT envuelve DueDate → no puede usar ningún índice
WHERE CONVERT(varchar(10), DueDate, 23) < CONVERT(varchar(10), GETDATE(), 23)
-- + correlated subquery ejecuta un lookup por cada fila devuelta

Causa raíz: el 99.7% de las filas pasan el filtro de fecha — un índice por fecha es casi inútil. El predicado selectivo es BalanceDue > 0 (solo 1,872 de 182,070 filas). Fix: columna calculada persistida BalanceDue AS (OriginalAmount + Charges - Payments) como leading key del índice — SQL Server hace seek directo a las 1,872 filas con saldo. Lecturas: 4,958 → 38 (−99.2%).

Anti-patrón 3 — SELECT * + filtro sin índice + Sort implícito (Q3)

-- ANTES: lee las 14 columnas por página, sin índice en IsClosed ni PeriodId
SELECT * FROM InventoryMonthlyBalance
WHERE IsClosed = 1 AND PeriodId >= '202401' AND PeriodId <= '202412'
ORDER BY PeriodId, WarehouseId, ProductId

Fix: lista explícita de 11 columnas + covering index con (PeriodId, IsClosed, WarehouseId, ProductId) como keys. Páginas más angostas (−50% reads), e índice pre-ordenado en el mismo orden del ORDER BY → operador Sort eliminado completamente. Tiempo servidor: 7,024 ms → 16 ms (−99.8%).

Insight clave — no todo índice mejora toda consulta

Agregar un covering index en SalesOrderLines(OrderId, IsCancelled) para Q1 redujo las lecturas lógicas un 88% (60,418 → 7,450) pero aumentó el elapsed de 2,053 ms a 4,119 ms. El optimizador cambió de Hash Match en paralelo (9 hilos) a Nested Loop (2,256 seeks secuenciales). Para un scan mensual que devuelve 12K filas, el plan paralelo gana en tiempo real aunque lea más páginas. Por eso se mide antes de enviar cualquier índice a producción.

The Problem

Three queries used daily in sales, collections, and inventory reports were slow — not because the database was undersized, but because each query had an anti-pattern that prevented SQL Server from using any index.

Anti-pattern 1 — Function wrapper on indexed column (Q1)

-- BEFORE: YEAR() wraps OrderDate → SQL Server cannot use the index → full scan on 169K rows
WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 1

-- AFTER: explicit range → Index Seek on IX_SalesOrders_Date_Covering
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2024-02-01'

Fix: covering index with OrderDate as leading key. SalesOrders logical reads: 2,856 → 19 (−99.3%). Elapsed: 5,056 ms → 2,053 ms (−59%).

Anti-pattern 2 — Non-sargable CONVERT + correlated subquery (Q2)

-- BEFORE: CONVERT wraps DueDate → cannot use any index on DueDate
WHERE CONVERT(varchar(10), DueDate, 23) < CONVERT(varchar(10), GETDATE(), 23)
-- + correlated subquery executes one lookup per row returned

Root cause: 99.7% of rows pass the date filter — so a date-range index is nearly useless. The selective predicate is BalanceDue > 0 (only 1,872 of 182,070 rows). Fix: persisted computed column BalanceDue AS (OriginalAmount + Charges - Payments) as the leading index key — SQL Server seeks directly to the 1,872 rows with balance. Correlated subquery replaced with LEFT JOIN. Reads: 4,958 → 38 (−99.2%).

Anti-pattern 3 — SELECT * + unindexed filter + implicit Sort (Q3)

-- BEFORE: reads all 14 columns per page, no index on IsClosed or PeriodId
SELECT * FROM InventoryMonthlyBalance
WHERE IsClosed = 1 AND PeriodId >= '202401' AND PeriodId <= '202412'
ORDER BY PeriodId, WarehouseId, ProductId

Fix: explicit column list (11 of 14) + covering index with (PeriodId, IsClosed, WarehouseId, ProductId) as keys. Pages are narrower (−50% reads), and index pre-sorts rows in ORDER BY order → Sort operator eliminated entirely. Server time: 7,024 ms → 16 ms (−99.8%).

Key Insight — Not Every Index Helps Every Query

Adding a covering index on SalesOrderLines(OrderId, IsCancelled) for Q1 reduced logical reads by 88% (60,418 → 7,450) but *increased* elapsed time from 2,053 ms to 4,119 ms. Why: the optimizer switched from a parallel Hash Match (9 threads) to a Nested Loop (2,256 sequential seeks). For a month-wide scan returning 12K rows, the parallel plan wins on wall-clock time despite reading more pages. This is why you benchmark before shipping any index to production.