SQL Server Parameter Sniffing: error timeout con DataAdapter.Fill en .NET

SQL Server Parameter Sniffing: error timeout con DataAdapter.Fill en .NET

Si estás invocando un procedimiento almacenado en SQL Server al que le pasas uno o varios parámetros y observas que el tiempo de ejecución es mucho mayor que si ejecutas manualmente la query, probablemente estés ante un caso de Parameter Sniffing. Este tipo de problema sucede cuando el gestor de la BD intenta reutilizar un plan de ejecución anterior recuperándolo de la caché aplicando  los nuevos valores de los parámetros recibidos.

El plan de ejecución de un procedimiento almacenado en SQL Server no se genera al compilarlo, si no que se crea la primera vez que se ejecuta el SP y después se reutiliza en las sucesivas llamadas. Esto puede dar lugar a ejecuciones con una demora de tiempo muy elevada, ya que puede ser que el plan de ejecución de la primera consulta no sea tan óptimo para otras. Cuando ejecutamos un SP desde una aplicación .NET nos arroja el error timeout con DataAdapter.Fill.

Solución

La forma de solucionarlo es utilizar variables locales en lugar de los propios parámetros en la query del procedimiento almacenado.  Creamos una nueva variable del mismo tipo y tamaño que cada parámetro, y le asignamos su valor. Por ejemplo:

CREATE PROCEDURE MyStoredProcedure

@fecha Date,
@categoria int

AS

    DECLARE @nuevaFecha Date
    DECLARE @nuevaCategoria int

    SET @nuevaFecha =@fecha
    SET @nuevaCategoria=@categoria

    SELECT […]  FROM […]
    WHERE aud_fecha = @nuevaFecha
    AND catID = @nuevaCategoria
GO

En mi caso me apareció este error desde una aplicación C# desde la que invocando a un procedimiento almacenado y metiéndolo en un DataTable mediante DataAdaptar.Fill me arrojaba un error de TimeOut. La solución rápida y mala de este error desde la aplicación web es asignar varios segundos al parámetro timeout del SQLCommand, y aunque esto evita que salte el error y se detenga el proceso, no soluciona el problema de la demora de tiempo, donde a veces se presentan diferencias de más de una hora respecto a la solución desde T-SQL.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

What is 14 + 13 ?
Please leave these two fields as-is: