Saturación del CPU ralentiza el servidor
Este artículo es otro capítulo de la serie sobre las herramientas de supervisión de SQL Server y de los problemas más comunes sobre el rendimiento. Antes de empezar leer este artículo, les recomiendo leer antes, los dos artículos anteriores sobre herramientas de monitoreo para el I/O del disco y del rendimiento de la memoria:
- Las herramientas de supervisión de SQL Server para el rendimiento de e/S de disco
- Las herramientas de monitorización de SQL Server para el rendimiento de la memoria
De todos modos, si solo estás interesado en la saturación del CPU, está bien. Este artículo también se puede leer de forma independiente. Solo que aconsejo leer toda la secuela ya que todo está conectado de alguna manera. Para empezar, siempre me gusta mencionar algunos síntomas que indican un problema en la condición del CPU:
- Un uso repentino alto del CPU – se puede ver un uso alto del CPU en el Administrador de tareas de Windows. Esto es obvio cuando ve que la CPU está conectada, es una buena señal de que algo está sucediendo
- Aumento del I/O – los problemas de la CPU pueden manifestarse a partir de los problemas de I/O y de memoria. Por lo tanto, el I/O también debe verificarse al solucionar problemas de la CPU
- Compilaciones altas – muchas recopilaciones pueden agregar algunos ciclos al CPU
- Consultas complicadas – incluso más que la anterior
Herramientas para monitorizar el rendimiento
Vistas de gestión dinámicas
En primer lugar, tenemos un excelente DMV llamado sys.dm_exec_query_stats que nos devuelve las estadísticas de rendimiento agregadas para los planes de consulta del caché en el SQL Server. Estas DMV se puede utilizar de muchas maneras. Como por ejemplo, se puede usar tanto para encontrar un elevado número de recopilaciones como para consultas complicadas.
Entonces, vamos a ver distintas formas en que esta estadística de consulta se usa como una herramienta de monitoreo de SQL Server. Hay que pegar el siguiente código y ejecútalo:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--High recompiles SELECT plan_generation_num, execution_count, SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, (CASE statement_end_offset WHEN-1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE plan_generation_num > 1 ORDER BY plan_generation_num DESC; |
Ahora puede ver una selección de las estadísticas de la consulta donde la generación de planes es mucho mayor que una. Pues eso es solo un indicador de que una consulta ha hecho más de una recopilación. Si se hace más de uno, es muy factible que continúe haciéndolo. He aquí mi resultado:
En un entorno de producción, lo más probable es que se obtenga un mayor conjunto de datos.
Otra manera de usar las estadísticas de consulta se muestra en el siguiente código. Pero esta vez, estamos encontrando las 10 consultas más complicadas. Tiene que pegar el siguiente código en el editor de consultas y ejecutarlo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--Expensive queries SELECT TOP 10 creation_time, last_execution_time, total_worker_time, total_elapsed_time, SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, (CASE statement_end_offset WHEN-1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time DESC; |
Siéntase libre de editar la parte de TOP e ingrese el número deseado de consultas que desee que se devuelvan:
Se dará cuenta que ambas consultas son idénticas, a excepción de lo que estamos seleccionando y en la segunda consulta ordenamos por tiempo total de trabajo, lo que principalmente devolverá información sobre las consultas de la parte de TOP N clasificadas por tiempo de procesador.
Monitor de rendimiento
En el lado del Monitor de rendimiento, obtenemos algunas de las herramientas de monitoreo del SQL Server, contadores AKA que se pueden utilizar para poder solucionar los problemas de rendimiento de la CPU. Los contadores a continuación son simples y fáciles de usar:
- Procesador % Tiempo de procesador == < 80%
- Procesador % de tiempo de usuario == < 80%
- Procesador % de tiempo privilegiado == < 30%
Comprender el tiempo del procesador no es nada difícil. Por lo general, este valor debe ser inferior al 80 por ciento. Esta misma regla se aplica para el tiempo del usuario y, por último, el tiempo privilegiado debe ser inferior al 30 por ciento. Si alguno de estos está más arriba, es una buena señal de que estamos estresando la CPU. Tengamos en cuenta que, al solucionar los problemas del CPU con cualquier herramienta de monitoreo de SQL Server, siempre tiene que tener en cuenta los siguientes factores externos y no solo los del CPU que está causando ciclos adicionales.
Si nos vamos a ver el Monitor de rendimiento, podemos crear de forma rápida otro conjunto de recopiladores de datos y poder agregar los contadores de rendimiento anteriores. Yo lo mantuve funcionando durante unos 10 minutos solo para poder crear registros de datos de contadores que incluimos anteriormente. Pero esta vez, no ejecuté la herramienta de estrés en segundo plano porque claramente generaría calor en mi equipo y ese no es el objetivo. El objetivo aquí es poder identificar si la CPU está bajo la presión en circunstancias normales. A continuación, están mis resultados:
Al observar este informe, podemos darnos cuenta que el tiempo privilegiado es bastante bueno, lo mismo pasa con el tiempo del procesador y del usuario. Todos ellos están en buena forma. Pero, nuevamente, en mi equipo. Los resultados en este ejemplo: El servidor de producción puede ser radicalmente diferente. Hay que estar atento a estos contadores que no excedan los números especificados anteriormente.
En vez de escribir otro artículo en serie acerca las herramientas de monitoreo de SQL Server, quiero acaparar brevemente la concurrencia que mencioné en el primer artículo en la lista de problemas de rendimiento más comunes. La concurrencia es bastante fácil de solucionar. Esto llega a ser lo contrario a lo que escribí en el artículo inicial, cuando dije que es una tuerca difícil de romper. Las dos afirmaciones son algo ciertas. Todo esto depende de cómo se manifieste la concurrencia.
Algunos buenos síntomas de concurrencia serán:
- Rendimiento lento
- Bloqueo de eventos
- La utilización del CPU/ memoria/ I/O es normal, pero…
El último punto es complicado porque la CPU, la memoria y el I/O pueden estar en orden, pero las personas aún se pueden molestar por los de los problemas en el rendimiento, lo que es un buen indicador de que es muy factible que nosotros tengamos un problema con la concurrencia. A parte de eso, este problema probablemente proviene del cierre o bloqueo.
Tenemos dos DVM para descubrir los bloqueos:
Del lado del Monitor de rendimiento, también tenemos dos contadores:
- SQLServer:Locks\Lock Waits/sec == 0
- SQLServer:Locks\Lock Wait Time (ms) == 0
Estos dos combinados nos van a dar una buena señal de que si hay algún bloqueo y qué tan grave es este. Estoy muy seguro de que no tengo ningún bloqueo en mi máquina local, por lo que ni siquiera intentare ir al Monitor de rendimiento, aunque el objetivo es que esos contadores se pueden usar como una herramienta de supervisión de SQL Server para poder solucionar los problemas de bloqueo.
Tabla de contenido
| Las herramientas de supervisión de SQL Server para el rendimiento de e/S de disco |
| Las herramientas de monitorización de SQL Server para el rendimiento de la memoria |
| Herramienta de supervisión de SQL Server para el rendimiento de la CPU |
He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.
Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.
View all posts by Bojan Petrovic