Como detectar consultas de SQL Server que hacen un gran consumo de CPU

En la anterior entrada os expliqué como podíais detectar problemas de rendimientos derivados de falta de memoria en SQL Server, en esta ocasión vamos a ver como detectar aquellas consultas que hacen un gran consumo de CPU y por tanto pueden causar problemas de rendimiento en nuestro entorno.

Los principales motivos por los que SQL Server realiza una alto consumo de la CPU es la ejecución de sentencias no optimizadas debido a falta de índices, fragmentación de los mismos, información desactualizada de las estadísticas de las tablas, etc…

Obtener consultas más costosas

Para detectar las sentencias más costosas ejecutadas por SQL podemos utilizar el siguiente script que nos dará datos muy útiles como pueden ser el tiempo de CPU, ejecuciones de la sentencia y el tiempo medio de ejecución.

SELECT    
                qs.total_worker_time AS [Total CPU Time],
                qs.execution_count AS [Ejecuciones],
                qs.total_worker_time/qs.execution_count as [Tiempo Medio CPU],
                SUBSTRING(qt.text,qs.statement_start_offset/2,
                (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
                else qs.statement_end_offset end -qs.statement_start_offset)/2) AS sentencia                
FROM 
                sys.dm_exec_query_stats qs
                CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY 
                [Tiempo Medio CPU] DESC

Una vez tenemos un listado con las sentencias más pesadas tendremos que analizarlas para ver donde está el problema.

Causas comunes lentitud sentencias SQL Server

Falta de índices

Es importante que dispongamos de índices en las tablas por los campos que normalmente solemos utilizar a la hora de montar nuestras consultas.

La creación de índices es uno de los métodos más importantes para lograr un mejor rendimiento de las consultas. Los índices ayudan a encontrar los datos con menos operaciones de E/S de disco y un menor uso de los recursos del sistema.

Dicho esto sino tienes índices ya estás tardando en crearlos 🙂

Fragmentación de los índices

Con el tiempo los índices se van fragmentando y esto hace que el acceso al dato sea más lento al encontrarse los bloques físicos que contienen los datos muy dispersos.

Para localizar aquellos índices que estén fragmentados podéis utilizar este script el cual recorrerá todos los índices de la base de datos seleccionadas y nos creará los script necesarios en función de si se necesita realizar una reorganización o bien una reconstrucción,

USE nombre_base_de_datos
GO
with indices (bd, indicetipo, fragmentacion, indice, tabla)
as (
   SELECT 
      dbs.name basededatos, ps.index_type_desc, ps.avg_fragmentation_in_percent,
      ind.name indice, tab.name tabla
   FROM
      sys.dm_db_index_physical_stats (db_id(), null, null, null, null) ps
      inner join sys.databases dbs
      on ps.database_id = dbs.database_id
      inner join sys.indexes ind
      on ps.object_id = ind.object_id
      inner join sys.tables tab
      on tab.object_id = ind.object_id
   WHERE 
      ind.name is not null and ps.index_id = ind.index_id
      and ps.avg_fragmentation_in_percent > 0)
SELECT 
   distinct 
   case
   when fragmentacion > 5 and fragmentacion <= 30 then 'alter index ' + indice + ' on ' + tabla + ' reorganize'
  when fragmentacion > 30 then 'alter index ' + indice + ' on ' + tabla + ' rebuild'
 end query, fragmentacion, bd, indice, tabla
FROM 
   (select fragmentacion, indice, tabla, bd from indices
  WHERE fragmentacion > 5) a
ORDER BY 
   fragmentacion desc

Una vez hayamos ejecutado el script y tengamos las consultas simplemente tendremos que copiarlas y pegarlas (primera columna) y ejecutarlas para corregir los problemas de fragmentación.

Información desactualizada de los índices de las tablas

Las estadísticas son un conjunto de conteos que se realizan sobre una tabla o índice para conocer el estado del mismo, tales como número de filas, bloques usados, nivel de profundidad de los índices, etc, y es usado por los motores de bases de datos para establecer el mejor plan de ejecución posible cuando se accede a las tablas en una consulta. Cuando esa información se encuentra desactualizada el motor no es capaz de establecer el mejor plan de ejecución haciendo que las consultas vayan más lentas.

Para actualizar las estadísticas de una base de datos ejecutaremos el siguiente comando.

USE nombre_base_de_datos
GO
EXEC sp_updatestats

Espero os haya sido de utilidad.

Entradas relacionadas

• SQL Server: Como detectar problemas de rendimiento causados por falta de memoria
• SQL Server: Habilitar xp_cmdshell
• SQL Server: Permitir conexiones remotas
• SQL Server 2016: Realizar un Backup mediante un Plan de Mantenimiento
• SQL Server: Plan de mantenimiento para la limpieza de backup