SQL consume mucha CPU. Solución a problemas de procesador

La semana pasada hablamos sobre el uso excesivo de RAM en SQL Server. Hoy, vamos a abordar otro problema común que puede afectar el rendimiento de nuestros servidores: el consumo elevado de CPU. El consumo excesivo de CPU en SQL Server puede afectar significativamente el rendimiento y la estabilidad del sistema. En este artículo, compartiremos estrategias efectivas para identificar y abordar las causas subyacentes de este problema.

Identificando el problema

Como resumía Don Castelino en uno de los mejores artículos que existen sobre este tipo de incidentes, lo primero que debemos determinar es de donde viene el problema. Si recordáis, en el caso de la RAM, teníamos una cantidad de memoria reservada para SQL y sobre eso, podíamos ver el consumo. En el caso de la CPU la cosa se complica porque la CPU es compartida con el sistema operativo y todos los demás procesos que haya en el servidor. Por este motivo, tendremos muchas más variables que analizar. La siguiente imagen resume la lista de comprobaciones que debemos realizar.

Vamos a ponernos en situación: Estamos ante un problema de rendimiento, nos dirigimos al administrador de tareas de Windows en nuestro servidor y vemos un consumo de CPU del 100% o cercano a esa cifra de manera continuada. Lo primero que debemos hacer será, en el mismo administrador de tareas, determinar si el mayor consumo de CPU viene de SQL Server o de otros procesos. En caso de que el consumo de SQL Server no supere, de manera constante, el 70% del total deberemos ver con los administradores de sistemas que otras cosas están causando el incidente en nuestro servidor. 

Como eso sería muy fácil, y ya habriamos terminado aquí el artículo, vamos a suponer que vemos que el problema de CPU sí que viene de SQL Server. Analicemos los siguientes pasos.

Batch Request por segundo

Una de las medidas que debemos tomar junto con el consumo de CPU es la cantidad de procesos batch por segundo que está ejecutando nuestro servidor SQL Server. Podemos encontrar este dato de forma sencilla en el monitor de rendimiento de nuestro SSMS o en el Performance Monitor de Windows. De esta manera veremos, de forma clara si nuestro SQL está ejecutando muchos procesos de manera simultánea o si, por el contrario, con pocos procesos generamos ese alto consumo de CPU. 

Debemos conocer y familiarizarnos con esta medición pues, no hay un valor estándar para todos los SQL Server. Depende mucho del procesador que tengamos, sus núcleos y su velocidad así como de los procesos que normalmente se ejecutan. Yo he visto, desde servidores donde un valor cercano a 1000 batch por segundo suponían un problema, como servidores con 15 o 20 veces ese valor que funcionaban perfectamente. 

Vista sys.dm_exec_query_stats

Una de las vistas de administración dinámica de SQL Server que nos va a ayudar a solucionar problemas de rendimiento de CPU es sys.dm_exec_query_stats. En ella vamos a poder ver las consultas con mayor consumo de CPU que tenemos en SQL Server. Si además la combinamos con la función sys.dm_exec_sql_text podremos ver el texto de las consultas. Volviendo a la vista, esta nos va a aportar gran cantidad de información entre la que nos vamos a quedar con el conteo de ejecuciones, y los tiempos de ejecución máximo, mínimo y total. Además, os recomiendo ordenar por este último, pues el problema puede ser la ejecución repetida de una consulta ligera y no tanto la ejecución única de una consulta pesada.

SELECT TOP 10
 total_worker_time/1000 AS [Total Worker Time (ms)],
 (total_worker_time/1000)/execution_count AS [Avg Worker Time (ms)],
 last_worker_time/1000 AS [Last Worker Time (ms)],
 max_worker_time/1000 AS [Max Worker Time (ms)],
 min_worker_time/1000 AS [Min Worker Time (ms)],
 execution_count,
 SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
 ((CASE qs.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;

Eventos Extendidos

La vista que hemos visto antes, nos puede dar las consultas recientes más pesadas, sin embargo, no nos será de ayuda ante un problema de consumo de CPU que no sea reciente. Para esto podemos usar otra de las formas de monitorización más potentes que incluye SQL Server, los eventos extendidos. Vamos a usar el evento query_post_execution_showplan que aunque ya dijimos que es pesado es lo más potente para este tipo de situaciones. Además vamos a capturar algunas acciones extra como el identificador del proceso, la base de datos, el usuario y el texto de la consulta. Para terminar, yo he añadido un filtro para solo capturar las consultas de más de 20 segundos de duración (recordad que el valor se muestra siempre en microsegundos). Este sería el script de mi sesión de xEvents:

CREATE EVENT SESSION [ConsumoCPU] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
ACTION(package0.process_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text)
WHERE ([duration]>=(20000000)))
ADD TARGET package0.event_file(SET filename=N'C:\SQL2022\ConsumoCPU.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Conclusión

Espero que a partir de ahora estéis más pereparados para afrontar problemas de rendimiento de CPU. Las herramientas que os he facilitado pueden ser de gran utilidad pero no son las únicas. En este sentido, QueryStore también puede ser un gran aliado, aunque aún no lo hayamos visto en este blog (espero solucionar esto pronto) os recomiendo que busquéis información y probéis la herramienta. 

Los más entendidos, habréis notado que no hemos mencionado en ningún momento las configuraciones de paralelismo de la instancia SQL Server. Esto es porque en este artículo hemos partido de la base de solucionar un problema de rendimiento en un servidor correctamente configurado. Le dedicaremos un artículo a estas configuraciones más adelante.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.