Solucionar problemas de uso elevado de CPU en SQL Server

Artículo
02/08/2024

Se aplica a: SQL Server

En este artículo se proporcionan procedimientos para diagnosticar y corregir problemas causados por un uso elevado de CPU en un equipo que ejecuta Microsoft SQL Server. Aunque hay muchas causas posibles del uso elevado de CPU que se produce en SQL Server, las siguientes son las causas más comunes:

Lecturas lógicas elevadas causadas por análisis de tablas o índices debido a las condiciones siguientes:
- Estadísticas desactualizadas
- Índices que faltan
- Problemas del plan confidencial de parámetros (PSP)
- Consultas mal diseñadas
Aumento de la carga de trabajo

Puede usar los pasos siguientes para solucionar problemas de uso elevado de CPU en SQL Server.

Paso 1: comprobar que SQL Server está causando un uso elevado de CPU

Use una de las siguientes herramientas para comprobar si el proceso de SQL Server está contribuyendo realmente al uso elevado de CPU:

Administrador de tareas: en la pestaña Proceso, compruebe si el valor de la columna CPU para SQL Server Windows NT-64 Bit está cerca del 100 por ciento.
Monitor de rendimiento y recursos (Perfmon)
- Contador: Process/%User Time, % Privileged Time
- Instancia: sqlservr

Puede usar el siguiente script de PowerShell para recopilar los datos del contador durante un intervalo de 60 segundos:

$serverName = $env:COMPUTERNAME
$Counters = @(
    ("\\$serverName" + "\Process(sqlservr*)\% User Time"), ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time")
)
Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
    $_.CounterSamples | ForEach {
        [pscustomobject]@{
            TimeStamp = $_.TimeStamp
            Path = $_.Path
            Value = ([Math]::Round($_.CookedValue, 3))
        }
        Start-Sleep -s 2
    }
}

Si % User Time es constantemente mayor que el 90 % (% de tiempo de usuario es la suma del tiempo de procesador en cada procesador, su valor máximo es 100 % * (sin CPU)), el proceso de SQL Server está causando un uso elevado de la CPU. Sin embargo, si % Privileged time es sistemáticamente mayor que el 90 por ciento, el software antivirus, otros controladores u otro componente del sistema operativo del equipo está contribuyendo a un uso elevado de CPU. Debe trabajar con el administrador del sistema para analizar la causa principal de este comportamiento.

Paso 2: Identificar las consultas que contribuyen al uso de CPU

Si el proceso Sqlservr.exe está causando un uso elevado de CPU, con diferencia, la razón más común es la existencia de consultas de SQL Server que realizan análisis de tablas o índices, además de operaciones de ordenación, operaciones de hash y bucles (operador de bucle anidado o WHILE (T-SQL)). Para hacerse una idea de la cantidad de CPU que usan actualmente las consultas, aparte de la capacidad total de CPU, ejecute la siguiente instrucción:

DECLARE @init_sum_cpu_time int,
        @utilizedCpuCount int 
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' 
--calculate the CPU usage by queries OVER a 5 sec interval 
SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests
WAITFOR DELAY '00:00:05'
SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] 
FROM sys.dm_exec_requests

Para identificar las consultas responsables de la actividad alta de CPU actual, ejecute la siguiente instrucción:

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Si las consultas no impulsan la CPU en este momento, puede ejecutar la siguiente instrucción para buscar consultas históricas enlazadas a la CPU:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    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,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Paso 3: actualizar estadísticas

Después de identificar las consultas que tienen el mayor consumo de CPU, actualice las estadísticas de las tablas que usan estas consultas. Puede usar el procedimiento almacenado por el sistema sp_updatestats para actualizar las estadísticas de todas las tablas internas y definidas por el usuario de la base de datos actual. Por ejemplo:

exec sp_updatestats

Nota:

El procedimiento almacenado por el sistema sp_updatestats se ejecuta UPDATE STATISTICS en todas las tablas internas y definidas por el usuario de la base de datos actual. Para asegurar un mantenimiento regular, asegúrese de que el mantenimiento regular programado mantiene actualizadas las estadísticas. Use soluciones como Desfragmentación adaptable del índice para administrar automáticamente la desfragmentación de índices y las actualizaciones de estadísticas para una o varias bases de datos. Este procedimiento elige automáticamente si se va a volver a generar o reorganizar un índice según su nivel de fragmentación, entre otros parámetros, y actualizar las estadísticas con un umbral lineal.

Para obtener más información sobre sp_updatestats, consulte sp_updatestats.

Si SQL Server sigue usando una capacidad de CPU excesiva, vaya al paso siguiente.

Paso 4: añadir índices que faltan

La falta de índices puede provocar una ejecución más lenta de las consultas y un uso elevado de la CPU. Puede identificar los índices que faltan y crearlos para ayudar a paliar este impacto en el rendimiento.

Ejecute la consulta siguiente para identificar las consultas que provocan un uso elevado de la CPU y que contienen al menos un índice que falta en el plan de consulta:

-- Captures the Total CPU time spent by a query along with the query plan and total executions
SELECT
    qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
    q.[text],
    p.query_plan,
    qs_cpu.execution_count,
    q.dbid,
    q.objectid,
    q.encrypted AS text_encrypted
FROM
    (SELECT TOP 500 qs.plan_handle,
     qs.total_worker_time,
     qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE p.query_plan.exist('declare namespace 
        qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        //qplan:MissingIndexes')=1

Revise los planes de ejecución de las consultas que se identifican y ajuste la consulta con los cambios necesarios. En la captura de pantalla siguiente se muestra un ejemplo en el que SQL Server señalará un índice que falta para la consulta. Haga clic con el botón derecho en la parte Índice que falta del plan de consulta y seleccione Faltan detalles del índice para crear el índice en otra ventana de SQL Server Management Studio.

Use la consulta siguiente para comprobar si faltan índices y aplicar los recomendados que tengan valores de medida de gran mejora. Comience con las 5 o 10 recomendaciones principales de la salida que tienen el valor de improvement_measure más alto. Esos índices tienen el efecto positivo más significativo en el rendimiento. Decida si desea aplicarlos y asegúrese de que se ejecutan pruebas de rendimiento para la aplicación. Entonces, siga aplicando recomendaciones de índice que falta hasta que obtenga los resultados de rendimiento deseados en la aplicación. Para obtener más información sobre este tema, vea Ajuste de índices no agrupados con sugerencias de índice que falta.

SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,
    mig.index_group_handle,
    mid.index_handle,
    CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,
        '') + CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns,
        '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')',
        '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (DECIMAL (28, 1),
               migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Paso 5: investigar y resolver problemas relacionados con los parámetros

Puede usar el comando DBCC FREEPROCCACHE para liberar la caché del plan y comprobar si esto resuelve el problema de uso elevado de CPU. Si se ha corregido, indica un problema de sensibilidad a los parámetros (PSP, también conocido como "problema de detección de parámetros").

Nota:

El uso de DBCC FREEPROCCACHE sin parámetros quita todos los planes compilados de la caché del plan. Esto hará que las nuevas ejecuciones de consultas se compilen de nuevo, lo que dará lugar a una duración más prolongada para cada nueva consulta. El mejor enfoque es usar DBCC FREEPROCCACHE ( plan_handle | sql_handle ) para identificar qué consulta puede estar causando el problema y, después, abordar esa consulta individual o consultas.

Para mitigar problemas de sensibilidad a los parámetros, utilice los siguientes métodos. Cada uno tiene sus ventajas e inconvenientes.

Use la sugerencia de consulta RECOMPILE. Puede añadir una sugerencia de consulta RECOMPILE a una o varias de las consultas de CPU elevada que se identifican en el paso 2. Esta ayuda a equilibrar el ligero aumento del uso de la CPU de compilación con un rendimiento más óptimo para cada ejecución de consultas. Para obtener más información, vea Parámetros y reutilización del plan de ejecución, Sensibilidad de parámetros y Sugerencia de consulta RECOMPILE.

Este es un ejemplo de cómo puede aplicar esta sugerencia a la consulta.
```
SELECT * FROM Person.Person 
WHERE LastName = 'Wood'
OPTION (RECOMPILE)
```
Use la sugerencia de consulta OPTIMIZE FOR para invalidar el valor del parámetro real con un valor de parámetro más típico que cubra la mayoría de los valores de los datos. Esta opción requiere una comprensión completa de los valores de parámetro óptimos y las características del plan asociadas. Este es un ejemplo de cómo usar esta sugerencia en la consulta.
```
DECLARE @LastName Name = 'Frintu'
SELECT FirstName, LastName FROM Person.Person 
WHERE LastName = @LastName
OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
```
Use la sugerencia de consulta OPTIMIZE FOR UNKNOWN para invalidar el valor del parámetro real con la media del vector de densidad. Para ello, también puede capturar los valores de parámetro entrantes en variables locales y, luego, usar estas dentro de los predicados en lugar de los propios parámetros. Para esta corrección, la densidad media puede ser suficiente para proporcionar un rendimiento aceptable.
Use la sugerencia de consulta DISABLE_PARAMETER_SNIFFING para deshabilitar completamente la detección de parámetros. Este es un ejemplo de su uso en una consulta:
```
SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
```
Use la sugerencia de consulta KEEPFIXED PLAN para evitar recompilaciones en la caché. Esta solución alternativa supone que el plan común "suficientemente bueno" es el que ya está en la caché. También puede deshabilitar las actualizaciones automáticas de estadísticas para reducir las posibilidades de que se expulse el plan adecuado y se compile un nuevo plan incorrecto.
Use el comando DBCC FREEPROCCACHE como solución temporal hasta que se corrija el código de la aplicación. Puede emplear el comando DBCC FREEPROCCACHE (plan_handle) para quitar solo el plan que está causando el problema. Por ejemplo, si quiere buscar planes de consulta que hagan referencia a la tabla Person.Person en AdventureWorks, puede utilizar esta consulta para buscar el identificador de controlador. Después, puede liberar el plan de consulta específico de la caché mediante el DBCC FREEPROCCACHE (plan_handle) que se genera en la segunda columna de los resultados de la consulta.
```
SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%person.person%'
```

Paso 6: investigar y resolver problemas de SARGabilidad

Un predicado de una consulta se considera SARGable (Search ARGument-able) cuando el motor de SQL Server puede usar una búsqueda de índice para acelerar la ejecución de la consulta. Muchos diseños de consulta impiden la SARGabilidad y conducen a exámenes de tabla o índice y un uso elevado de la CPU. Tenga en cuenta la siguiente consulta en la base de datos AdventureWorks, donde se deben recuperar ProductNumber y la función SUBSTRING() que se aplica, antes de que se compare con un valor literal de cadena. Como puede ver, primero debe capturar todas las filas de la tabla y, después, aplicar la función para poder establecer una comparación. Capturar todas las filas de la tabla significa un examen de tabla o índice, lo que conduce a un mayor uso de CPU.

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE SUBSTRING(ProductNumber, 0, 4) =  'HN-'

La aplicación de cualquier función o cálculo en las columnas del predicado de búsqueda suele provocar que la consulta no sea SARGable y conduce a un mayor consumo de CPU. Las soluciones suelen implicar la reescritura de las consultas de forma creativa para que sean SARGables. Una posible solución para este ejemplo es esta reescritura, en la que se elimina la función del predicado de la consulta, se busca en otra columna y se logran los mismos resultados:

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE Name LIKE  'Hex%'

Este es otro ejemplo, en que un administrador de ventas puede querer dar un 10 % de comisión de ventas en pedidos grandes y desea ver qué pedidos tendrán una comisión superior a 300 USD. Esta es la manera lógica, pero no SARGable de hacerlo.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

Esta es una posible reescritura de la consulta menos intuitiva, pero SARGable, en la que el cálculo se mueve al otro lado del predicado.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice > 300/0.10

La SARGabilidad no solo se aplica a las cláusulas WHERE, sino también a JOINs, HAVING, GROUP BY y ORDER BY. Los casos más frecuentes de prevención de la SARGabilidad en las consultas implican el uso de las funciones CONVERT(), CAST(), ISNULL() y COALESCE() en las cláusulas WHERE o JOIN, lo que conduce al examen de las columnas. En los casos de conversión de tipo de datos (CONVERT o CAST), la solución puede ser asegurarse de que está comparando los mismos tipos de datos. Este es un ejemplo en el que la columna T1.ProdID se convierte de forma explícita al tipo de datos INT de JOIN. La conversión anula el uso de un índice en la columna de combinación. El mismo problema se produce con la conversión implícita, en que los tipos de datos son diferentes y SQL Server convierte uno de ellos para llevar a cabo la combinación.

SELECT T1.ProdID, T1.ProdDesc
FROM T1 JOIN T2 
ON CONVERT(int, T1.ProdID) = T2.ProductID
WHERE t2.ProductID BETWEEN 200 AND 300

Para evitar un examen de la tabla T1, puede cambiar el tipo de datos subyacente de la columna ProdID tras planearlo y diseñarlo correctamente y, después, combinar las dos columnas sin usar la función de conversión ON T1.ProdID = T2.ProductID.

Otra solución consiste en crear una columna calculada en T1 que use la misma función CONVERT() y, luego, crear un índice en ella. Esto permitirá que el optimizador de consultas emplee ese índice sin necesidad de cambiar la consulta.

ALTER TABLE dbo.T1  ADD IntProdID AS CONVERT (INT, ProdID);
CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID);

En algunos casos, las consultas no se pueden reescribir fácilmente para permitir la SARGabilidad. En esos casos, vea si la columna calculada con un índice puede ayudar o, de lo contrario, mantenga la consulta tal y como estaba, a sabiendas de que puede aumentar el uso de la CPU.

Paso 7: deshabilitar el seguimiento intensivo

Compruebe si son el Seguimiento de SQL o el seguimiento XEvent los que afectan al rendimiento de SQL Server y provocan un uso elevado de CPU. Por ejemplo, el uso de los siguientes eventos puede provocar un uso elevado de CPU si se realiza un seguimiento de la actividad pesada de SQL Server:

Eventos XML del plan de consultas (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile, query_post_execution_showplan, query_pre_execution_showplan)
Eventos de nivel de informe (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
Eventos de inicio y cierre de sesión (login, process_login_finish, login_event, logout)
Eventos de bloqueo (lock_acquired, lock_cancel, lock_released)
Eventos de espera (wait_info, wait_info_external)
Eventos de auditoría SQL (dependiendo del grupo auditado y de la actividad SQL Server de ese grupo)

Ejecute las consultas siguientes para identificar trazas activos de XEvent o Server:

PRINT '--Profiler trace summary--'
SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM::fn_trace_getinfo(
    default)
GO
PRINT '--Trace event details--'
SELECT trace_id,
    status,
    CASE WHEN row_number = 1 THEN path ELSE NULL end AS path,
    CASE WHEN row_number = 1 THEN max_size ELSE NULL end AS max_size,
    CASE WHEN row_number = 1 THEN start_time ELSE NULL end AS start_time,
    CASE WHEN row_number = 1 THEN stop_time ELSE NULL end AS stop_time,
    max_files,
    is_rowset,
    is_rollover,
    is_shutdown,
    is_default,
    buffer_count,
    buffer_size,
    last_event_time,
    event_count,
    trace_event_id,
    trace_event_name,
    trace_column_id,
    trace_column_name,
    expensive_event
FROM
    (SELECT t.id AS trace_id,
     row_number() over(PARTITION BY t.id order by te.trace_event_id, tc.trace_column_id) AS row_number,
     t.status,
     t.path,
     t.max_size,
     t.start_time,
     t.stop_time,
     t.max_files,
     t.is_rowset,
     t.is_rollover,
     t.is_shutdown,
     t.is_default,
     t.buffer_count,
     t.buffer_size,
     t.last_event_time,
     t.event_count,
     te.trace_event_id,
     te.name AS trace_event_name,
     tc.trace_column_id,
     tc.name AS trace_column_name,
     CASE WHEN te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN CAST(1 as bit) ELSE CAST(0 AS BIT) END AS expensive_event FROM sys.traces t CROSS APPLY::fn_trace_geteventinfo(t.id) AS e JOIN sys.trace_events te ON te.trace_event_id = e.eventid JOIN sys.trace_columns tc ON e.columnid = trace_column_id) AS x
GO
PRINT '--XEvent Session Details--'
SELECT sess.NAME 'session_name', event_name, xe_event_name, trace_event_id,
    CASE WHEN xemap.trace_event_id IN(23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) 
    THEN Cast(1 AS BIT)
ELSE Cast(0 AS BIT)
END AS expensive_event
FROM sys.dm_xe_sessions sess
JOIN sys.dm_xe_session_events evt
ON sess.address = evt.event_session_address
INNER JOIN sys.trace_xe_event_map xemap
ON evt.event_name = xemap.xe_event_name
GO

Paso 8: Corrección del uso elevado de CPU causado por la contención de bloqueo de número

Para resolver el uso elevado común de CPU causado por la contención de bloqueos de número, consulte las secciones siguientes.

SOS_CACHESTORE contención de bloqueo de giro

Si la instancia de SQL Server experimenta una fuerte SOS_CACHESTORE contención de bloqueo de número o observa que los planes de consulta a menudo se quitan en cargas de trabajo de consultas no planeadas, consulte el siguiente artículo y habilite la marca T174 de seguimiento mediante el DBCC TRACEON (174, -1) comando :

CORRECCIÓN: la contención de bloqueo por subproceso de SOS_CACHESTORE en la caché de plan de SQL Server ad hoc causa un uso elevado de CPU en SQL Server.

Si la condición de CPU elevada se resuelve mediante T174, habilite esta opción como un parámetro de inicio empleando el Administrador de configuración de SQL Server.

Uso elevado aleatorio de CPU debido a SOS_BLOCKALLOCPARTIALLIST contención de bloqueo de número en máquinas de memoria grande

Si la instancia de SQL Server experimenta un uso elevado aleatorio de CPU debido a la SOS_BLOCKALLOCPARTIALLIST contención de bloqueo de número, se recomienda aplicar la actualización acumulativa 21 para SQL Server 2019. Para obtener más información sobre cómo resolver el problema, consulte la referencia de errores 2410400 y DBCC DROPCLEANBUFFERS que proporciona mitigación temporal.

Uso elevado de CPU debido a la contención de bloqueo de número en XVB_list en máquinas de gama alta

Si la instancia de SQL Server experimenta un escenario elevado de CPU causado por la contención de bloqueos spinlock en las XVB_LIST máquinas de configuración alta (sistemas de gama alta con un gran número de procesadores de última generación (CPU)), habilite la marca de seguimiento TF8102 junto con TF8101.

Nota:

Un uso elevado de la CPU puede ser el resultado de la contención de bloqueos spinlock en muchos otros tipos de bloqueo de número. Para obtener más información sobre los bloqueos de número, consulte Diagnóstico y resolución de la contención de bloqueos spinlock en SQL Server.

Paso 9: configurar su máquina virtual

Si usa una máquina virtual, asegúrese de que no está sobreaprovisionando las CPU y de que están configuradas correctamente. Para obtener más información, consulte Solucionar los problemas de rendimiento de las máquinas virtuales ESX/ESXi (2001003).

Paso 10: sistema de ampliación para usar más CPU

Si las instancias de consulta individuales usan poca capacidad de CPU, pero la carga de trabajo general de todas las consultas juntas causa un consumo elevado de CPU, considere la posibilidad de ampliar su equipo añadiendo más CPU. Use la consulta siguiente para buscar el número de consultas que han superado un umbral determinado de consumo medio y máximo de CPU por ejecución y que se han ejecutado muchas veces en el sistema (asegúrese de modificar los valores de las dos variables para que coincidan con su entorno):

-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
       qs.max_worker_time/1000 max_cpu_time_ms,
       (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
       qs.execution_count,
       q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
        OR qs.max_worker_time > @cputime_threshold_microsec )
        AND execution_count > @execution_count
ORDER BY  qs.total_worker_time DESC

Compartir a través de

Solucionar problemas de uso elevado de CPU en SQL Server

Paso 1: comprobar que SQL Server está causando un uso elevado de CPU

Paso 2: Identificar las consultas que contribuyen al uso de CPU

Paso 3: actualizar estadísticas

Paso 4: añadir índices que faltan

Paso 5: investigar y resolver problemas relacionados con los parámetros

Paso 6: investigar y resolver problemas de SARGabilidad

Paso 7: deshabilitar el seguimiento intensivo

Paso 8: Corrección del uso elevado de CPU causado por la contención de bloqueo de número

SOS_CACHESTORE contención de bloqueo de giro

Uso elevado aleatorio de CPU debido a SOS_BLOCKALLOCPARTIALLIST contención de bloqueo de número en máquinas de memoria grande

Uso elevado de CPU debido a la contención de bloqueo de número en XVB_list en máquinas de gama alta

Paso 9: configurar su máquina virtual

Paso 10: sistema de ampliación para usar más CPU

Consulte también

Comentarios

Recursos adicionales