sql-server - tabla - procedimiento almacenado para mostrar datos
ADO.NET al llamar al procedimiento almacenado T-SQL causa una excepción SqlTimeoutException (3)
Tengo un procedimiento almacenado T-SQL con la firma
CREATE PROCEDURE MyProc
@recordCount INT OUTPUT
@param1 INT
...
Cuando se ejecuta directamente en el servidor Sql, el procedimiento se ejecuta en menos de 5 segundos, devolviendo unos pocos conjuntos de resultados que ascienden a aproximadamente 100 filas en total.
Al llamar a este procedimiento utilizando el método ADO.NET SqlDataAdapter.Fill para completar un Dataset genera una SqlTimeoutException en SqlCommand después de 3 minutos (el intervalo de tiempo especificado).
Cambiar el procedimiento almacenado para que ya no tenga un parámetro de salida, y que el valor de salida requerido se devuelva como el último conjunto de resultados, resuelva el problema y todo se ejecute en menos de 5 segundos como se esperaba.
¿Pero por qué?
No quiero revisar mi base de código y modificar todas las instancias de este tipo de comportamiento sin comprender si realmente he resuelto el problema.
Otra cosa a tener en cuenta es que esto solo es aparente en un servidor en particular, que ciertamente tiene un conjunto de datos más grande que otras bases de datos similares que ejecutamos. Seguramente no es un servidor de configuración SQL?
ACTUALIZAR
Al entrar en la fuente del marco, el problema parece estar en la recuperación de metadatos. El método ConsumeMetaData del objeto SqlDataReader cuelga indefinidamente. Sin embargo, ejecuté pruebas en otras bases de datos y no puedo reproducir, por lo que es un problema específico de la base de datos cuando se llama a este procedimiento aunque ADO.NET ... Genial.
ACTUALIZACIÓN II
Se ha confirmado que el problema sigue ocurriendo si cambio el código para utilizar el OleDbDataAdapter con los tipos de proveedor SQLOLEDB o SQLNCLI. Definitivamente que ver con la conexión.
En resumen: solucioné mi problema al forzar a SQL Server a usar el índice más apropiado para limitar las lecturas lógicas cuando no podía resolverlo por sí solo.
En largo
Me topé con este problema y lo resolví de una manera diferente después de probar todas las otras respuestas sugeridas. En SSMS, la consulta se ejecutaba en ~ 3s, pero se agotaba el tiempo de espera cuando se llamaba desde una aplicación web .Net MVC.
La salida de IO de estadísticas en SSMS me decía que había más de 195,500,000 lecturas lógicas de lob en una tabla (tabla de 20M filas con un índice de almacén de columnas agrupado y también tiene índices de fila, pero no tiene columnas "LOB"). Noté en el plan de ejecución que una mayor parte de la carga (76%) provenía de una búsqueda de índice en uno de los índices de fila. Utilicé lo siguiente:
from [table] with (index([clustered columnstore index name]))
en mi consulta para forzar el uso del índice de almacén de columnas agrupado y mi consulta se redujo a <1s y las lecturas lógicas de lob se redujeron a <6k desde> 195M, y cuando se llama al SP desde la aplicación web ahora, es un disparo de ida y vuelta 1.3s.
Intenté la recompilación de opciones, puse arithabort on, sniffing de parámetros y, al final, SQL Server simplemente no supe qué índice utilizar. Este es un caso de borde demasiado por cierto, y la única vez que he tenido que forzar un índice en esta base de datos.
Estoy corregido, sí, PUEDES tener ambos, un parámetro de SALIDA y un conjunto de filas que se devuelven. Tu aprendes algo nuevo cada dia :-)
En cuanto a por qué ocurre un tiempo de espera, hmm ... es difícil decirlo. Una pequeña muestra rápida funciona bien para mí. ¿Puedes publicar tu proc almacenado (al menos bits relevantes de él)?
¿De cuántas filas estamos hablando, que se devuelven aquí?
¿En qué punto de su proceso almacenado está calculando la cantidad de filas que necesita para regresar como parámetro de SALIDA?
¿Qué sucede si trata de agregar otro parámetro MaxRows a su único SProc como prueba y realiza un SELECT TOP (@MaxRows)....... en sus datos? ¿Eso vuelve rápidamente?
Bagazo
Una vez que determiné que es la conexión ADO.NET en la raíz del problema, este thread me llevó a la respuesta.
Básicamente, las conexiones a través de Sql Server Management Studio (SSMS) de forma predeterminada tienen SET ARITHABORT ON . Las conexiones ADO.NET no lo hacen.
Configurar ARITHABORT OFF y ejecutar la consulta directamente a través de SSMS me da el mismo tiempo de respuesta lento.
La principal diferencia cuando se ejecuta con o sin esta configuración es que se crea un plan de consulta diferente para las dos llamadas. Cuando ARITHABORT estaba OFF , el comando SSMS usaría el plan de consulta en caché precompilado que usaba la conexión ADO.NET y, por lo tanto, el tiempo de espera.
Al ejecutar los siguientes comandos como administrador en la base de datos, todas las consultas se ejecutan como se espera, independientemente de la configuración de ARITHABORT .
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Solo puedo asumir que un plan de consulta compilado se corrompió o no fue válido.
Iré con esto como la solución (he votado la respuesta) en el otro thread
Gracias.