Desaceleración global de SQL Server 2005

Hace dos días nuestro server de producción sufrió una desaceleración masiva donde el síntoma principal fue que un número extraordinariamente alto de requestes estaban sufriendo SQLTimeouts. Describiré rápidamente nuestra configuration, lo que investigué, nuestra solución y luego seguiré con mi pregunta.

Nuestra configuration

Un par de serveres alojan esta twig de nuestra aplicación SAS. Uno de ellos es un server de aplicaciones que ejecuta varias aplicaciones en IIS y el otro que sufrió la desaceleración, es un cuadro de Windows Server 2008 que ejecuta SQL Server 2005. SQL alberga entre 100 y 200 bases de datos.

El problema / investigación

El service molía prácticamente a un alto. Algunas requestes pasan, pero la mayoría sufren times de espera de SQL. La CPU y la RAM de la máquina SQL se ven bien, promediando alnetworkingedor del 25% de la carga de trabajo de la CPU y del 85% de RAM. No pensé en comprobar la actividad del disco en ese momento, ya que fui directamente a 'EXEC sp_who2'

El resultado mostró cientos de tareas bloqueadas por el ID 123, que estaba en sí mismo y con un centenar de otras bloqueadas por el ID 456. La ejecución normal normalmente no tiene tareas de locking en absoluto. Cuando volví a ejecutar sp_who2 después de 15-20 segundos, diferentes identificadores de locking apareció, pero la cantidad de bloqueado / tareas de locking parecía permanecer igual. (no contó los grupos debido al modo de emergencia)

La mayoría de las tareas bloqueaban con declaraciones como "SELECT INTO" o "CREATE INDEX en temptable".

La solución alternativa

Eliminar el process SQL y reiniciarlo para restaurar el service. La desaceleración no volvió a aparecer, pero sabemos que estamos en riesgo.

Mi pregunta

¿Qué puedo hacer para solucionar este problema, preferiblemente antes de que vuelva a ocurrir?

Sub-preguntas:

  • ¿Hay otro path que pueda investigar durante la actividad normal?
  • Si / cuando el problema vuelve a ocurrir, ¿qué información debo recostackr? (Necesita ser rápido de get, ya que significa que estaremos experimentando un corte de service de nuevo)

Lo que hice hasta ahora

De los síntomas, sospechamos que el problema había sido una discusión de algún tipo en tempdb. (Otro síntoma fue que onclick con el button derecho del ratón en tempdb para ver las properties durante el problema se generó un error después de un corto time)

Ningún logging indica que se produjo un evento de crecimiento automático en tempdb, aunque hasta donde yo sepa, los éxitos de crecimiento automático no se registran, solo los errores.

He leído un montón de diferentes fonts de información desde entonces en la contención tempdb, no se limita a, pero incluyendo:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ http://www.sqlservercentral.com/blogs/robert_davis/2010/03/ 05 / Breaking-Down-TempDB-Contención /

Por lo que entiendo, es la mejor práctica tener files tempdb de set-initial-size y tener uno por núcleo, hasta 8 files. Es nuestro plan para poner eso en su lugar (8 núcleos, por lo que 8 files) tan pronto como sea posible, ya que es la mejor práctica. Todos estarían en el mismo disco duro (por ahora), pero creemos que el peor caso no es mejoría, y lo mejor es que ganemos la diferencia entre el cuello de botella de contención lógica y el cuello de botella de E / S de disco.

Sin embargo, no podemos estar seguros de la correlación con el problema que tuvimos. De lo que yo entiendo, la split a múltiples files temporales ayudaría a tipo de espera "PAGELATCH_XX", pero la ejecución de la consulta de Paul S. Randal (ver el primer enlace publicado) durante la actividad normal, ese tipo de espera está ausente. Top 3 Veo durante la actividad normal son:

CXPACKET 68.63%
LATCH_EX 18.46%
PAGEIOLATCH_SH 4,35%

No tengo manera de saber qué tipo de locking estaba ocurriendo durante la desaceleración, ya que no teníamos toda esta información entonces.

One Solution collect form web for “Desaceleración global de SQL Server 2005”

El problema eventualmente se repitió el día después de haber publicado esta pregunta.

Al ejecutar la consulta de Paul S. Randal, rápidamente descubrí un número de PAGELATCH_XX bloqueando esperas pasando, así que con sp_who2 pude encontrar las bases de datos culpables y solo reiniciar los pools de aplicaciones cliente relevantes desde el server web como una solución mucho less dura para service de restauración.

También pudimos seguir el rastro de las operaciones reales que hacen mucho más trabajo de tempdb que hicieron antes, y mirarán fijar eso en un acercamiento diferente del ángulo a este problema.

La solución

Hemos ido adelante con la split del file tempdb en varios files como la mejor práctica sugiere , ya que parece que era el tipo de disputa que estaba ocurriendo para esta solución para solucionar mi problema.

  • ¿Es seguro encoger tempdb.mdf en MS SQL Server?
  • ¿Qué sucede cuando tempdb no puede crecer más?
  • ¿Cómo probar el performance de TempDB?
  • Mantenimiento de TempDB de SQL Server 2005
  • Gran cantidad de escritura de logging de tempdb, ninguna lectura
  • El linux y los temas del servidor de Windows, como ubuntu, centos, apache, nginx, debian y consejos de red.