Funciones escalares en SQL Server: cómo una función de limpieza bloqueó el servidor 11 horas
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 incidente
A primera hora de la mañana, el equipo de operaciones reporta que las cadenas ETL del Data Warehouse llevan horas de retraso. Al investigar, encontramos una sesión que lleva más de 11 horas ejecutándose, consumiendo 84 GB de memory grant y quemando CPU sin avance real.
El culpable: una función escalar T-SQL que limpia caracteres especiales de strings, invocada sobre millones de filas.
La función problemática
CREATE FUNCTION dbo.LimpiarCaracteres(@Input varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX('%[^@a-zA-Z0-9 .,;-/\()|]%', @Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input, @pos, 1, '')
SET @Pos = PATINDEX('%[^@a-zA-Z0-9 .,;-/\()|]%', @Input)
END
RETURN @Input
END
Parece inocente: recorre un string, busca caracteres no permitidos con PATINDEX, los elimina con STUFF, y repite. En un string individual, tarda microsegundos. El problema es invocarla sobre millones de filas.
Por qué las funciones escalares T-SQL son devastadoras
1. Ejecución fila a fila (RBAR)
SQL Server invoca la función una vez por cada fila. No hay vectorización. Si tienes 5 millones de filas y la función se aplica a 3 columnas, son 15 millones de invocaciones secuenciales.
2. DOP forzado a 1
Esta es la limitación más destructiva: la presencia de una función escalar T-SQL impide el paralelismo. El plan entero cae a DOP 1, sin importar la configuración.
En nuestro caso, el pool del Resource Governor tenía DOP 26 (26 cores). Con la función escalar, solo usaba 1 core — desperdiciando el 96% de la capacidad.
3. Caja negra para el optimizador
El contenido de la función es opaco. No puede estimar costes, reorganizar operaciones ni integrar la lógica en el plan.
Las métricas del desastre
| Métrica | Valor | Interpretación |
|---|---|---|
| Duración | >11 horas | Inaceptable para una carga diaria |
| CPU | 18M ms (~5 horas) | Cómputo puro, no espera |
| Memory grant | 84 GB (máximo del pool) | Reservado pero apenas usado |
| Logical reads | 923M (sin incremento) | Atrapada en el cómputo |
| Row count | 1 | No completó la primera operación |
| DOP | 1 | Forzado por la función escalar |
| Wait type | Ninguno (running) | CPU pura |
El row count de 1 tras 11 horas es revelador: la query ni siquiera había terminado su primer INSERT...SELECT.
La solución: inline TVF con tally table
CREATE OR ALTER FUNCTION dbo.LimpiarCaracteres_inline(@Input varchar(1000))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN (
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E1 b),
Tally(N) AS (SELECT TOP(LEN(@Input)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E3)
SELECT (
SELECT SUBSTRING(@Input, T.N, 1)
FROM Tally T
WHERE PATINDEX('%[^@a-zA-Z0-9 .,;-/\()|]%', SUBSTRING(@Input, T.N, 1)) = 0
ORDER BY T.N
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'varchar(1000)') AS CleanedValue
);
Ventajas: - SQL Server la despliega (inline) dentro del plan - Permite paralelismo (DOP 26) - Procesamiento set-based en vez de fila a fila
Cambio en la query: CROSS APPLY dbo.LimpiarCaracteres_inline(columna) c
Mejora esperada
| Aspecto | Escalar + WHILE | Inline TVF |
|---|---|---|
| DOP | 1 (forzado) | Hasta 26 |
| Procesamiento | Fila a fila | Set-based |
| Duración | >11 horas | Minutos |
| CPU | 5h en 1 core | Distribuida en 26 cores |
| Memory grant | 84 GB bloqueados 11h | Liberado en minutos |
Lecciones aprendidas
- Nunca uses funciones escalares T-SQL sobre millones de filas. Usa inline TVF o procesa la lógica directamente en la query.
- DOP 1 es la sentencia de muerte para tablas grandes. Si ves
ActualDOP="1"cuando debería ser paralelo, busca funciones escalares.
- El row count es un indicador clave: si después de horas sigue en 0 o 1, la query está atrapada.
- Las funciones "de limpieza" son las más peligrosas: parecen simples, se usan en todas partes, y nadie las cuestiona.
- Revisa bugs silenciosos: en este caso, la función también tenía un bug — le faltaba una letra en la lista de caracteres permitidos.