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

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

Lecciones aprendidas

1. Nunca uses funciones escalares T-SQL sobre millones de filas. Usa inline TVF o procesa la lógica directamente en la query.

1. DOP 1 es la sentencia de muerte para tablas grandes. Si ves ActualDOP="1" cuando debería ser paralelo, busca funciones escalares.

1. El row count es un indicador clave: si después de horas sigue en 0 o 1, la query está atrapada.

1. Las funciones "de limpieza" son las más peligrosas: parecen simples, se usan en todas partes, y nadie las cuestiona.

1. 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.