.net - configurar - Cómo solucionar problemas de errores intermitentes de SQL Timeout
configurar timeout en sql server 2012 (14)
Ejecute el rastreo de SQL de consultas y deadlocks de larga ejecución. Esto no muestra interbloqueos en el momento de los problemas, y las consultas de larga ejecución coinciden con nuestros errores de tiempo de espera, pero parecen ser un efecto secundario, y no la causa. Las consultas que son muy básicas y que generalmente regresan instantáneamente terminan tomando 30, 60 o 120 segundos para ejecutarse ocasionalmente. Esto sucede durante unos minutos, luego todo se recupera y funciona bien después de eso.
Parece que algunas consultas / transacciones bloquean su base de datos hasta que finalizan. Debe averiguar qué consultas están bloqueando y reescribirlas / ejecutarlas en otro momento para evitar el bloqueo de otros procesos. En este momento, las consultas de espera simplemente exceden el tiempo de espera.
Un punto extra en el que profundizar es el tamaño de incremento automático de su registro de transacciones y base de datos. Establecerlos en un tamaño fijo en lugar de un porcentaje de los archivos actuales. Si los archivos son cada vez más grandes, el tiempo necesario para asignar suficiente espacio eventualmente será más largo que el tiempo de espera de la transacción. Y tu DB se detiene.
Hemos tenido algunas instancias por día en las que recibimos una gran cantidad de errores SQL Timeout de varias aplicaciones (System.Data.SqlClient.SqlException: el tiempo de espera expiró. El tiempo de espera transcurrido antes de la finalización de la operación o el servidor no responde .) Tenemos más de 100 aplicaciones diferentes en nuestra red, tanto en aplicaciones web como de escritorio. Todo, desde VB6 y Classic ASP hasta .NET 4. Puedo encontrar todo tipo de datos que muestran los efectos secundarios pero no puedo identificar qué está causando esto. Nuestro DBA dice que nada está mal con el servidor SQL, y dice que no hay nada malo con los servidores web o la red, así que por supuesto me quedé en el medio tratando de solucionarlo.
Realmente estoy buscando sugerencias sobre qué otra solución de problemas puedo hacer para intentar rastrear esto.
Estamos ejecutando SQL Server 2008 R2 en un clúster. Hay un puñado de servidores diferentes que se conectan a él, que van desde Windows Server 2003 a 2008 de diferentes variedades.
Esto es lo que hice hasta ahora:
- Ejecute el rastreo de SQL de consultas y deadlocks de larga ejecución. Esto no muestra interbloqueos en el momento de los problemas, y las consultas de larga ejecución coinciden con nuestros errores de tiempo de espera, pero parecen ser un efecto secundario, y no la causa. Las consultas que son muy básicas y que generalmente regresan instantáneamente terminan tomando 30, 60 o 120 segundos para ejecutarse ocasionalmente. Esto sucede durante unos minutos, luego todo se recupera y funciona bien después de eso.
- Use el monitor de rendimiento para rastrear las conexiones del grupo de conexiones. A veces, esto muestra algunos picos en la cantidad de conexiones cercanas a los tiempos de los tiempos de espera, pero aún no llega a la mitad del límite predeterminado de 100 conexiones. Nuevamente, nada aquí que parezca apuntar a una causa.
- Separe las aplicaciones web en diferentes grupos de aplicaciones. Intentamos restringir las aplicaciones que creíamos que podían ser el problema principal (más hablador, etc.) y ponerlas en pools de aplicaciones separados, pero eso no parece afectar nada ni nos ayuda a reducir nada.
- Monitorear el uso del disco en SQL Server. Hemos hecho un poco de monitoreo en el servidor SQL y no vemos picos ni signos de problemas cuando ocurren estos tiempos de espera.
- TempDB verificado no fue la causa del problema.
Volveré y añadiré más si pienso en qué más hemos intentado. Por favor, hágame saber algunas ideas sobre qué solucionar después.
¿Están estos servidores virtualizados? En otra publicación que he leído acerca de un servidor SQL que se ejecuta a veces muy lentamente debido a la falta de memoria suficiente. Esto a su vez fue causado por un llamado globo de memoria que el virtualizador usó para limitar la cantidad de memoria utilizada por ese servidor virtual. Fue difícil de encontrar porque la presión sobre la memoria física no tenía nada que ver con el servidor SQL en sí.
Otra causa común de una degradación de rendimiento temporal podría ser un escáner de virus. Cuando se instala una nueva definición de virus, todos los demás procesos sufrirán y se ejecutarán muy lentamente. Verifique cualquier otro proceso de actualización automática, esto también podría tomar muchos recursos de manera inesperada. ¡Suerte con ello!
Como hago la solución de problemas todos los días como parte de mi trabajo, esto es lo que me gustaría hacer:
Como es SQL Server 2008 R2, puede ejecutar SQLDiag, que forma parte del producto. Puede recomendar libros en línea para más detalles. En resumen, capture el rastreo del lado del servidor y el script del bloqueador.
Una vez que se haya capturado, busque el evento "Atención". Ese sería el spid que ha recibido el error. Si filtra por SPID, verá RPC: evento completado antes de "Atención". Verifique el tiempo allí. ¿Es ese el tiempo de 30 segundos? En caso afirmativo, el cliente esperó 30 segundos para obtener la respuesta de SQL y obtuvo el "tiempo de espera agotado" [Esta es la configuración del cliente, ya que SQL nunca se detendría y la conexión]
Ahora, verifique si la consulta que se estaba ejecutando realmente debería tomar 30 segundos.
En caso afirmativo, ajuste la consulta o aumente la configuración de tiempo de espera del cliente.
Si no, entonces esta consulta debe estar esperando algunos recursos (bloqueados)
En este punto regrese a Blocker Script y verifique el marco de tiempo cuando llegó la "Atención"
¡Arriba está asumiendo que el problema es con SQL Server no relacionado con la red!
Como han sugerido los otros carteles, parece que tienes un problema de bloqueo de bloqueo. Nos enfrentamos a un problema similar hace unas semanas; sin embargo, el nuestro fue mucho más intermitente, y a menudo se aclaró antes de que pudiéramos obtener un DBA en el servidor para ejecutar sp_who2 para rastrear el problema.
Lo que terminamos haciendo fue implementar una notificación por correo electrónico si un bloqueo excedía cierto umbral. Una vez que implementamos esto, pudimos identificar los procesos que se estaban bloqueando, y cambiar el nivel de aislamiento para que no sea leído cuando sea apropiado para solucionar el problema.
Si el bloqueo resulta ser el problema, y si aún no lo hace, le sugiero que consulte la configuración de niveles de aislamiento basados en el control de versiones de filas .
Estás en el camino correcto con tu rastreo y perfil. lo que debe hacer es buscar cuáles son las consultas que tienen en común el tiempo de espera; es probable que todas afecten a un pequeño subconjunto de tablas o índices. Sospecho que alguna aplicación tiene una actualización / inserción de larga duración que afecta a las consultas en tablas que usan índices afectados por las actualizaciones / inserciones.
Tienes que trabajar un poco hacia atrás, dado el subconjunto de tablas que ves que se agota el tiempo de espera, mira qué índices están en esas tablas. Busque otras consultas que se estén ejecutando a la vez que toquen esas tablas / índices. Apuesto a que encontrarás un pequeño conjunto de actualizaciones / inserciones haciendo esto.
Entonces tienes que tomar algunas decisiones. Una opción es cambiar las sugerencias de bloqueo en las consultas que están expirando. Pero eso es en general una mala práctica porque enmascarará el problema real por un tiempo. Mientras ve que los tiempos de espera se van por un tiempo, dependiendo de la sugerencia que elija, puede terminar con lecturas sucias y luego datos falsos provenientes de esas consultas. Eso podría ser peor que los tiempos de espera, es difícil de decir.
La mejor apuesta es descubrir cuáles de sus aplicaciones envían las actualizaciones / inserciones que encontró y profundizar para descubrir por qué tardan tanto.
Experimentamos esto con SQL Server 2012 / SP3, al ejecutar una consulta a través de un objeto SqlCommand desde una aplicación C #. El comando era una invocación simple de un procedimiento almacenado que tiene un parámetro de tabla; estábamos pasando una lista de aproximadamente 300 enteros. El procedimiento a su vez llamó a tres funciones definidas por el usuario y pasó la tabla como un parámetro para cada una de ellas. CommandTimeout se estableció en 90 segundos.
Al ejecutar exactamente el mismo proceso almacenado con el mismo argumento desde SQL Server Management Studio, la consulta se ejecutó en 15 segundos. Pero al ejecutarlo desde nuestra aplicación utilizando la configuración anterior, se agotó el tiempo de espera de SqlCommand. El mismo SqlCommand (con datos diferentes pero comparables) se ha estado ejecutando con éxito durante semanas, pero ahora falló con cualquier argumento de tabla que contenga más de 20 enteros. Hicimos un rastreo y descubrimos que cuando se ejecutaba desde el objeto SqlCommand, la base de datos pasaba los 90 segundos completos adquiriendo bloqueos e invocaba el procedimiento solo en el momento del tiempo de espera. Cambiamos el tiempo de CommandTimeout, y no importa el tiempo en que seleccionamos el proceso almacenado, solo se invocaría al final de ese período. Así que suponemos que SQL Server adquirió indefinidamente los mismos bloqueos una y otra vez, y que solo el tiempo de espera del objeto Command hizo que SQL Server detuviera su bucle infinito y comenzara a ejecutar la consulta, momento en el que ya era demasiado tarde para tener éxito. Una simulación de este mismo proceso en un servidor similar utilizando datos similares no presentó tal problema. Nuestra solución fue reiniciar todo el servidor de la base de datos, después de lo cual el problema desapareció.
Así que parece que hay algún problema en SQL Server en el que algunos recursos se consumen acumulativamente y nunca se liberan. Finalmente, cuando se conecta a través de un SqlConnection y ejecuta un SqlCommand que implica un parámetro de tabla, SQL Server entra en un ciclo infinito que adquiere bloqueos. El ciclo finaliza con el tiempo de espera del objeto SqlCommand. La solución es reiniciar, aparentemente restaurando la cordura (¿temporal?) A SQL Server.
He visto problemas similares si se instaló un antivirus en el servidor SQL. Las características de actualización automática del AV registraban el servidor y no permitían suficiente CPU para SQL Server.
Además, ¿ha puesto una pequeña aplicación en el servidor SQL que verifica que se pueden hacer conexiones o ejecuta SQL muy básico como "SELECT GETDATE ();"? Esto eliminaría las posibilidades de red.
Le sugiero que tenga una mirada profunda a la característica de Vistas de administración dinámica de SQL Server súper genial:
Las vistas y funciones de gestión dinámica devuelven información de estado del servidor que se puede utilizar para supervisar el estado de una instancia del servidor, diagnosticar problemas y ajustar el rendimiento.
Este artículo es un buen comienzo con los DMV, aunque fue escrito para SQL 2005 (los DMV aparecen por primera vez): Solución de problemas de rendimiento en SQL Server 2005 , especialmente los capítulos de ''bloqueo''.
Los problemas de rendimiento se reducen a contención de CPU, E / S o Bloqueo. Parece que has descartado IO. Supongo que la CPU no es un problema, ya que es una base de datos, no un cruncher numérico. Entonces, eso deja la contención de bloqueo.
Si puede ejecutar un sp_who2 mientras se agota el tiempo de espera de las consultas, puede usar la columna BlkBy para rastrear el momento en que se mantiene el bloqueo que todos los demás esperan. Como esto solo ocurre unas pocas veces al día, puede tener problemas para capturar suficientes datos si está ejecutando esto manualmente, por lo que le sugiero que prepare un sistema automatizado para volcar esta salida de forma regular, o tal vez para ser activado por el excepciones de tiempo de espera de la aplicación. También puede usar el Monitor de actividad para ver la degradación de la capacidad de respuesta de la consulta en tiempo real, según lo sugerido por el par.
Una vez que encuentre la consulta de larga duración y la aplicación que la ejecuta, puede resolver inmediatamente el dominó de tiempos de espera reduciendo el tiempo de espera para esa aplicación única debajo de todas las demás (ahora mismo, debe ser más larga). Luego, debe inspeccionar el código para determinar una mejor solución. Podría reducir el tiempo de retención de la cerradura al comprometer la transacción antes en un proceso, o reducir el bloqueo requerido por la consulta de lectura con sugerencias como NOLOCK o UPDLOCK.
Aquí hay algo más de lectura en sp_who2: http://sqlserverplanet.com/dba/using-sp_who2/
Y sugerencias de consulta: http://msdn.microsoft.com/en-us/library/ms181714.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx
Mi experiencia con estos problemas (aunque no en SQL Server) es que la multitarea exagerada suele ser la causa del problema. Si hay muchas tablas de datos / tablas similares / conectadas consultadas (casi) al mismo tiempo, el DBMS puede tener problemas para mantener todo el aislamiento bajo control. Esto no es una gran cuestión de uso del disco en cuanto a hacer que algunas conexiones esperen a que otras personas las hagan. La sincronización es muy costosa en términos de uso de la CPU.
Las 100 conexiones son demasiado en mi opinión. (En mi experiencia otra vez) incluso 20 conexiones solicitadas por una máquina pueden ser demasiado optimistas.
Parece que ya tiene su respuesta, pero en caso de que necesite un lugar más para buscar, es posible que desee verificar el tamaño y la actividad de su DB temporal. Tuvimos un problema como este una vez en el sitio de un cliente donde algunas veces al día su rendimiento se degradaba horriblemente y ocasionalmente se agotaba el tiempo de espera. El problema resultó ser una aplicación separada que estaba afectando a la base de datos temporal tanto que afectaba el rendimiento general del servidor.
Buena suerte con la solución de problemas continua!
Poco probable, pero en un laboratorio hace un tiempo, tuvimos una situación en la que un SQL Server parecía no responder, no porque hubiéramos disparado la CPU o cualquier cosa que pudiéramos rastrear dentro de SQL Server, parecía operativo para todas las pruebas pero las conexiones fallaban bajo alguna carga
El problema se debió al volumen de tráfico contra el servidor, lo que significaba que estábamos activando las ventanas integradas Syn Attack Flood Protection dentro de Windows. Anormalmente cuando aciertas esto, no hay ningún mensaje registrado dentro del servidor de Windows, o dentro de SQL - solo ves los symtpoms que son conexiones que no se pueden hacer - esto es porque Windows se ralentiza al aceptar los mensajes y vamos a crear una cola. Desde el punto de vista de la conexión, el servidor parece no responder cuando debería (ni siquiera confirma que llegó el mensaje)
http://msdn.microsoft.com/en-us/library/ee377084(v=bts.10).aspx
Desplácese hacia abajo hasta SynAttackProtect y verá que el valor predeterminado en windows server 2003 sp1 en adelante era habilitar esta característica de forma predeterminada. Es un mecanismo de protección DDOS en efecto, y la falta de registro que está desencadenando hace que sea increíblemente difícil detectar cuándo su servidor hace esto.
Tomó 3 días en el laboratorio de MS antes de que se descubriera.
Usted mencionó 100 conexiones, teníamos una aplicación que se conectaba constantemente, ejecutaba consultas y luego se desconectaba, no mantenía las conexiones abiertas. Esto significaba que teníamos varios hilos en cada conexión de máquina haciendo esto, 10 máquinas, múltiples hilos por máquina, y se consideraba que se estaban haciendo / soltando suficientes conexiones diferentes para activar la defensa.
Si usted está en ese nivel (ya que no es un umbral claramente definido por MS) es difícil de decir.
Tuve un problema similar y descubrí que se debía a una configuración predeterminada de .Net Framework
Sqlcommand.Timeout
El valor predeterminado es de 30 segundos, como saciado en la URL anterior por Microsoft, intente configurar esto a una mayor cantidad de segundos o tal vez -1 antes de abrir la conexión para ver si esto resuelve el problema.
Puede ser una configuración en sus archivos web.config o app.config o en su aplicación / archivos de configuración del servidor web.
El problema se debe a una mala consulta; el tiempo de ejecución de la consulta lleva más de 60 segundos o un bloqueo en la tabla.
El problema parece ser que se está produciendo un punto muerto; tenemos consultas que bloquean las consultas para completarlas a tiempo. El tiempo de espera predeterminado para una consulta es de 60 segundos y más allá de eso tendremos SQLException para el tiempo de espera.
Por favor revise los registros de SQL Server en busca de puntos muertos. La otra forma de resolver el problema es aumentar el tiempo de espera en el objeto de comando (solución de temperatura).