asp.net - parametros - Procedimiento almacenado lento cuando se llama desde la web, rápido desde Management Studio

ejecutar procedimiento almacenado mvc 5

Question

He almacenado un procedimiento que increíblemente expira cada vez que se llama desde la aplicación web.

Inicié el Sql Profiler, rastreé las llamadas y finalmente descubrí estas cosas:

1. Cuando ejecuté las instrucciones desde MS SQL Management Studio, con los mismos argumentos (de hecho, copié la llamada de procedimiento desde el rastreo de perfil sql y lo ejecuté): termina en 5 ~ 6 segundos promedio.
2. Pero cuando se llama desde la aplicación web, toma más de 30 segundos (en traza) por lo que mi página web realmente se agota para entonces.

Aparte del hecho de que mi aplicación web tiene su propio usuario, cada cosa es la misma (misma base de datos, conexión, servidor, etc.) También intenté ejecutar la consulta directamente en el estudio con el usuario de la aplicación web y no toma más de 6 segundo.

¿Cómo averiguo qué está pasando?

Supongo que no tiene nada que ver con el hecho de que usemos capas BLL> DAL o adaptadores de tabla, ya que el trazado muestra claramente que la demora está en el procedimiento real. Eso es todo lo que puedo pensar.

EDITAR Descubrí en este enlace que ADO.NET establece ARITHABORT en verdadero, lo cual es bueno la mayor parte del tiempo, pero en algún momento esto sucede, y la ARITHABORT sugerida es agregar with recompile opción de with recompile al proceso almacenado. En mi caso, no está funcionando, pero sospecho que es algo muy similar a esto. ¿Alguien sabe qué más ADO.NET hace o dónde puedo encontrar la especificación?

Answer 1

¿Podría ser que algunas otras llamadas a DB realizadas antes de que la aplicación web llamara al SP mantenga abierta una transacción? Esa podría ser una razón para que este SP espere cuando lo solicite la aplicación web. Digo aislar la llamada en la aplicación web (ponerla en una nueva página) para asegurar que alguna acción previa en la aplicación web esté causando este problema.

Answer 2

He tenido un problema similar en el pasado, por lo que estoy ansioso por ver una resolución a esta pregunta. El comentario de Aaron Bertrand sobre el OP llevó a Query fuera de servicio cuando se ejecuta desde la web, pero es superrápido cuando se ejecuta desde SSMS , y aunque la pregunta no es un duplicado, la respuesta puede aplicarse a su situación.

En esencia, parece que SQL Server puede tener un plan de ejecución en caché corrupto. Estás accediendo al mal plan con tu servidor web, pero SSMS aterriza en un plan diferente ya que hay una configuración diferente en el indicador ARITHABORT (que de otro modo no tendría ningún impacto en tu consulta particular / proceso almacenado).

Ver ADO.NET llamando a T-SQL Stored Procedure causa una SqlTimeoutException para otro ejemplo, con una explicación y resolución más completa.

Answer 3

Para no enviar spam, pero como una solución útil para otros, nuestro sistema vio un alto grado de tiempos de espera.

Intenté configurar el procedimiento almacenado para recompilarlo utilizando sp_recompile y esto resolvió el problema para el único SP.

En última instancia, había una mayor cantidad de SP que estaban DBBC FREEPROCCACHE el tiempo, muchos de los cuales nunca lo habían hecho antes, al usar DBCC DROPCLEANBUFFERS y DBBC FREEPROCCACHE la tasa de tiempo de espera de los tiempos de espera se ha desplomado significativamente, todavía hay casos aislados, algunos donde sospecho que la regeneración del plan lleva algo de tiempo, y en algunos casos los SP tienen un rendimiento realmente insuficiente y necesitan una reevaluación.

Answer 4

Simplemente recompilar el procedimiento almacenado (función de tabla en mi caso) funcionó para mí

Answer 5

También tengo la experiencia de que las consultas se ejecutan lentamente desde la web y rápidamente en SSMS y finalmente descubrí que el problema era algo que se llamaba olfateo de parámetros.

La solución para mí fue cambiar todos los parámetros que se utilizan en el sproc a las variables locales.

p.ej. cambio:

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
SELECT * FROM Table WHERE ID = @param1 

a:

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
DECLARE @param1a int
SET @param1a = @param1
SELECT * FROM Table WHERE ID = @param1a

Parece extraño, pero solucionó mi problema.

Answer 6

como @Zane dijo que podría deberse a la detección de parámetros. Experimenté el mismo comportamiento y eché un vistazo al plan de ejecución del procedimiento y a todas las declaraciones de sp en una fila (copié todas las declaraciones del procedimiento, declare los parámetros como variables y asigné los mismos valores para la variable como los parámetros tenían). Sin embargo, el plan de ejecución parecía completamente diferente. La ejecución de sp tomó 3-4 segundos y las declaraciones en una fila con los mismos valores exactos se devolvieron al instante.

Después de buscar en Google, encontré una lectura interesante sobre ese comportamiento: ¿ lento en la aplicación, rápido en SSMS?

Al compilar el procedimiento, SQL Server no sabe que el valor de @fromdate cambia, pero compila el procedimiento bajo la suposición de que @fromdate tiene el valor NULL. Como todas las comparaciones con NULL arrojan UNKNOWN, la consulta no puede devolver ninguna fila, si @fromdate todavía tiene este valor en tiempo de ejecución. Si SQL Server tomaría el valor de entrada como la verdad final, podría construir un plan con solo un escaneo constante que no acceda a la tabla (ejecute la consulta SELECT * FROM Orders WHERE OrderDate> NULL para ver un ejemplo de esto) . Pero SQL Server debe generar un plan que devuelva el resultado correcto sin importar el valor que @fromdate tenga en tiempo de ejecución. Por otro lado, no hay obligación de construir un plan que sea el mejor para todos los valores. Por lo tanto, dado que se supone que no se devolverán filas, SQL Server se conforma con Index Search.

El problema era que tenía parámetros que podían dejarse nulos y si se pasaban como nulos, se inicializaba con un valor predeterminado.

create procedure dbo.procedure
    @dateTo datetime = null
begin
    if (@dateTo is null)
    begin
        select @dateTo  = GETUTCDATE()
    end

    select foo
    from dbo.table
    where createdDate < @dateTo
end

Después de que lo cambié a

create procedure dbo.procedure
    @dateTo datetime = null
begin
    declare @to datetime = coalesce(@dateTo, getutcdate())

    select foo
    from dbo.table
    where createdDate < @to
end 

funcionó como un encanto de nuevo.