Buenas,

hemos tenido problemas de rendimiento en nuestra base de datos de produccion, comparando los tiempos de ejecucion contra la base de datos de pruebas el rendimiento es mucho mejor en pruebas con muchos menos recursos, monitoreamos un procedimiento especifico que en un plazo de un mes paso de duras 15 minutos a mas de una hora, con un comportamiento inusual ya que dia a dia iba aumentado el tiempo de ejecucion, se hicieron trabajos a nivel de indices y el comportamiento no cambio sustancialmente, pero, por arte de magia a partir del 01 de este mes los tiempos de ejecucion volvieron a la normalidad.

Con el proveedor se hicieron varias pruebas e identificamos que las consultas que utilizaban funciones en el from tenian comportamientos inusuales, ademas tenemos un procedimiento que tiene varios insert, si ejecutamos las consultas dentro del procedimiento, se comporta muy diferente cuando aislamos la consulta, ya que los tiempos bajan sustancialmente.

De antemano gracias por toda la ayuda.

Hola,

El patrón que describes (degradación progresiva día a día, y que se «arregla sola» en una fecha concreta) es la firma clásica de dos problemas relacionados en SQL Server, no uno:

1. Parameter sniffing con plan cacheado obsoleto. SQL Server compila el plan de ejecución de un procedimiento la primera vez que se ejecuta, usando los valores de parámetro de esa primera llamada, y después reutiliza ese mismo plan para llamadas posteriores con parámetros distintos. Si la distribución de tus datos cambia con el tiempo (por ejemplo, cada vez hay más filas para ciertos valores), el plan que era óptimo al principio deja de serlo, pero SQL Server sigue usando el mismo plan cacheado hasta que algo lo invalida (una actualización de estadísticas, un rebuild de índice, o un reinicio del servicio). Si en tu entorno hay un job programado a primeros de mes (actualización de estadísticas, reorganización de índices, o similar), eso explicaría perfectamente por qué el rendimiento «se resetea» justo ese día.

2. Funciones en el FROM (probablemente funciones escalares o de tabla) rompen la estimación de filas. Confirmas tú mismo que las consultas con funciones en el FROM se comportan de forma inusual: el optimizador de SQL Server no puede estimar bien cuántas filas va a devolver una función definida por el usuario, y esa mala estimación se propaga a todo el plan (elección de tipo de JOIN, orden de acceso a tablas, memoria reservada para el sort...). Es un problema conocido de las funciones escalares/TVF multi-instrucción en SQL Server, no es casualidad que sea justo ahí donde ves el comportamiento raro.

Cosas concretas a probar:

Forzar recompilación del plan en cada ejecución (útil para diagnosticar, no siempre para dejarlo así en producción, tiene su propio coste de CPU):

EXEC tu_procedimiento @parametro = valor WITH RECOMPILE;

Ver si el plan actual cacheado coincide con el que se generaría hoy, comparando sys.dm_exec_query_stats con un OPTION (RECOMPILE) puntual en la consulta problemática.

Si confirmas que son las funciones en el FROM, sustituirlas por una tabla temporal o un CTE suele resolver el problema de raíz, en vez de depender de que las estadísticas se actualicen a tiempo.

David Carrero