Risolvere i problemi di utilizzo elevato della CPU in SQL Server

Articolo
10/23/2024

Si applica a: SQL Server

Questo articolo illustra le procedure per diagnosticare e risolvere i problemi causati dall'utilizzo elevato della CPU in un computer che esegue Microsoft SQL Server. Anche se esistono molte possibili cause relative all'utilizzo elevato della CPU che si verificano in SQL Server, le cause più comuni sono le seguenti:

Letture logiche elevate causate da analisi di tabelle o indici a causa delle condizioni seguenti:
- Statistiche non aggiornate
- Indici mancanti
- Problemi relativi al piano sensibile ai parametri (PSP)
- Query progettate in modo non idoneo
Aumento del carico di lavoro

Per risolvere i problemi di utilizzo elevato della CPU in SQL Server è possibile utilizzare la procedura seguente.

Passaggio 1: verificare che SQL Server stia causando un utilizzo elevato della CPU

Per verificare se il processo di SQL Server contribuisce effettivamente all'utilizzo elevato della CPU, utilizzare uno degli strumenti seguenti:

Gestione attività: nella scheda Processo, verificare se il valore della colonna CPU per SQL Server Windows NT-64 Bit si avvicina al 100%.
Monitoraggio prestazioni e risorse (perfmon)
- Contatore: Process/%User Time, % Privileged Time
- Istanza: sqlservr
Per raccogliere i dati del contatore in un intervallo di 60 secondi, è possibile usare lo script di PowerShell seguente:
```
$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
    }
}
```
Se % User Time è costantemente maggiore del 90% (% Tempo utente è la somma del tempo del processore in ogni processore, il valore massimo è 100% * (nessuna CPU)), il processo di SQL Server causa un utilizzo elevato della CPU. Tuttavia, se % Privileged time è costantemente superiore al 90%, il software antivirus, altri driver o un altro componente del sistema operativo nel computer contribuisce all'utilizzo elevato della CPU. È consigliabile collaborare con l'amministratore di sistema per analizzare la causa radice di questo comportamento.
Dashboard prestazioni: in SQL Server Management Studio fare clic con il pulsante destro del mouse su< SQLServerInstance> e selezionare Report report>>standard Prestazioni dashboard.

Il dashboard illustra un grafico denominato Utilizzo CPU di sistema con un grafico a barre. Il colore più scuro indica l'utilizzo della CPU del motore di SQL Server, mentre il colore più chiaro rappresenta l'utilizzo complessivo della CPU del sistema operativo (vedere la legenda nel grafico per riferimento). Selezionare il pulsante di aggiornamento circolare o F5 per visualizzare l'utilizzo aggiornato.

Passaggio 2: identificare le query che contribuiscono all'utilizzo della CPU

Se il processo Sqlservr.exe causa un utilizzo elevato della CPU, senza dubbio il motivo più comune sono le query di SQL Serve che eseguono analisi di tabella o di indice, seguite da ordinamento, operazioni hash e cicli (operatore ciclo nidificato o WHILE (T-SQL)). Per avere un'idea della quantità di CPU attualmente in uso per le query, eseguire l'istruzione seguente in base alla capacità complessiva della CPU:

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

Per identificare le query responsabili dell'attività corrente con CPU elevata, eseguire l'istruzione seguente:

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

Se le query non portano all'utilizzo della CPU in questo momento, per cercare le query cronologiche associate alla CPU è possibile eseguire l'istruzione seguente:

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

Passaggio 3: aggiornare le statistiche

Dopo aver identificato le query che comportano il consumo di CPU più elevato, aggiornare le statistiche delle tabelle utilizzate da tali query. È possibile utilizzare la stored procedure di sistema sp_updatestats per aggiornare le statistiche di tutte le tabelle interne e definite dall'utente nel database corrente. Ad esempio:

exec sp_updatestats

Note

La stored procedure di sistema sp_updatestats esegue UPDATE STATISTICS su tutte le tabelle interne e definite dall'utente nel database corrente. Per una manutenzione regolare, assicurarsi che la manutenzione programmata regolarmente mantenga aggiornate le statistiche. Usare le soluzioni come la deframmentazione dell'indice adattativo per gestire automaticamente la deframmentazione dell'indice e gli aggiornamenti delle statistiche per uno o più database. Questa procedura sceglie automaticamente se ricompilare o riorganizzare un indice in base al relativo livello di frammentazione, tra gli altri parametri, e aggiornare le statistiche con una soglia lineare.

Per maggiori informazioni su sp_updatestats, consultare sp_updatestats.

Se SQL Server utilizza ancora una capacità di CPU eccessiva, andare al passaggio successivo.

Passaggio 4: aggiungere gli indici mancanti

Gli indici mancanti possono comportare un rallentamento dell'esecuzione delle query e un utilizzo elevato della CPU. Per migliorare tale impatto sulle prestazioni, è possibile identificare gli indici mancanti e crearli.

Eseguire la query seguente per identificare le query che causano un utilizzo elevato della CPU e che contengono almeno un indice mancante nel piano di query:

-- 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

Esaminare i piani di esecuzione per le query identificate e ottimizzare la query apportando le modifiche necessarie. Lo screenshot seguente mostra un esempio in cui SQL Server segnalerà un indice mancante per la query. Fare clic con il pulsante destro del mouse sulla parte Indice mancante del piano di query e quindi selezionare Dettagli indice mancante per creare l'indice in un'altra finestra in SQL Server Management Studio.

Per verificare la presenza di indici mancanti e applicare tutti gli indici consigliati con valori di misura con miglioramento elevato, utilizzare la query seguente. Iniziare con le prime 5 o 10 raccomandazioni dell'output con il valore improvement_measure più alto. Tali indici hanno l'effetto positivo più significativo sulle prestazioni. Decidere se applicare questi indici e assicurarsi che vengano eseguiti test delle prestazioni per l'applicazione. Continuare quindi ad applicare le raccomandazioni sugli indici mancanti fino a ottenere i risultati delle prestazioni dell'applicazione desiderati. Per maggiori informazioni su questo argomento, consultare Ottimizzare gli indici non cluster con suggerimenti sugli indici mancanti.

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

Passaggio 5: analizzare e risolvere i problemi sensibili ai parametri

Per liberare la cache dei piani e verificare se questo risolve il problema relativo all'utilizzo elevato della CPU, è possibile utilizzare il comando DBCC FREEPROCCACHE. Se il problema è stato risolto, si tratta di un'indicazione di un problema sensibile ai parametri (PSP, noto anche come "problema relativo al parameter sniffing").

Note

L'utilizzo di DBCC FREEPROCCACHE senza parametri rimuove tutti i piani compilati dalla cache dei piani. In questo modo le nuove esecuzioni di query verranno ricompilate, con conseguente durata una tantum più lunga per ogni nuova query. L'approccio migliore consiste nell'utilizzare DBCC FREEPROCCACHE ( plan_handle | sql_handle ) per identificare quale query potrebbe causare il problema e quindi risolvere la singola query o le query.

Per ridurre i problemi sensibili ai parametri, utilizzare i metodi seguenti. Ogni metodo presenta compromessi e aspetti negativi associati.

Utilizzare il suggerimento per la query RECOMPILE. È possibile aggiungere un suggerimento per la query RECOMPILE a una o più query con CPU elevata che sono identificate nel passaggio 2. Questo suggerimento consente di bilanciare il leggero aumento dell'utilizzo della CPU nella compilazione con prestazioni ottimali per ogni esecuzione di query. Per maggiori informazioni, consultare Parametri e Riutilizzo del piano di esecuzione, Sensibilità dei parametri e Suggerimento RECOMPILE per la query.

Ecco un esempio di come applicare questo suggerimento alla query.
```
SELECT * FROM Person.Person 
WHERE LastName = 'Wood'
OPTION (RECOMPILE)
```
Utilizzare il suggerimento per la query OPTIMIZE FOR per eseguire l'override del valore effettivo del parametro con un valore di parametro più tipico che comprende la maggior parte dei valori nei dati. Questa opzione richiede una conoscenza completa dei valori ottimali dei parametri e delle caratteristiche del piano associate. Ecco un esempio di come usare questo suggerimento nella query.
```
DECLARE @LastName Name = 'Frintu'
SELECT FirstName, LastName FROM Person.Person 
WHERE LastName = @LastName
OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
```
Utilizzare il suggerimento per la query OPTIMIZE FOR UNKNOWN per eseguire l'override del valore effettivo del parametro con la media del vettore di densità. A tale scopo, è anche possibile acquisire i valori dei parametri in ingresso nelle variabili locali e quindi utilizzare tali variabili all'interno dei predicati anziché utilizzare i parametri stessi. Per questa correzione, la densità media può essere sufficiente per fornire prestazioni accettabili.
Usare il suggerimento per la query DISABLE_PARAMETER_SNIFFING per disabilitare completamente lo sniffing dei parametri. Ecco un esempio di come utilizzarlo in una query:
```
SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
```
Utilizzare il suggerimento per la query KEEPFIXED PLAN per evitare ricompilazioni nella cache. Questa soluzione alternativa presuppone che il piano comune "abbastanza valido" sia quello già presente nella cache. È anche possibile disabilitare gli aggiornamenti automatici delle statistiche per ridurre le probabilità che il piano valido venga rimosso e che venga compilato un nuovo piano non valido.
Utilizzare il comando DBCC FREEPROCCACHE come soluzione temporanea fino a quando il codice dell'applicazione non viene corretto. È possibile utilizzare il comando DBCC FREEPROCCACHE (plan_handle) per rimuovere solo il piano che causa il problema. Ad esempio, per trovare piani di query che fanno riferimento alla tabella Person.Person in AdventureWorks, per trovare la gestione delle query, è possibile usare questa query. È quindi possibile rilasciare il piano di query specifico dalla cache utilizzando DBCC FREEPROCCACHE (plan_handle) che viene prodotto nella seconda colonna dei risultati della query.
```
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%'
```

Passaggio 6: analizzare e risolvere i problemi di "SARGability"

Un predicato in una query è considerato SARGable (Search ARGument-able) quando il motore di SQL Server può usare una ricerca di indici per velocizzare l'esecuzione della query. Molte progettazioni di query impediscono la SARGability e portano ad analisi di tabelle o indici e a un utilizzo elevato della CPU. Prendiamo in considerazione la query seguente sul database AdventureWorks in cui ogni ProductNumber deve essere recuperato e la funzione SUBSTRING() applicata, prima che venga confrontato con un valore di stringa letterale. Come si può vedere, è necessario prima recuperare tutte le righe della tabella e poi applicare la funzione per poter fare un confronto. Il recupero di tutte le righe dalla tabella implica una scansione della tabella o dell'indice, che comporta un utilizzo più elevato della CPU.

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

L'applicazione di una funzione o di un calcolo nelle colonne del predicato di ricerca rende in genere la query non-sargable e comporta un utilizzo più elevato della CPU. Le soluzioni in genere comportano la riscrittura delle query in modo creativo per rendere il SARGable. Una possibile soluzione a questo esempio è la riscrittura in cui la funzione viene rimossa dal predicato della query, viene effettuata la ricerca di un'altra colonna e vengono ottenuti gli stessi risultati:

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

Ecco un altro esempio in cui un responsabile delle vendite può voler assegnare una commissione di vendita del 10% per ordini di grandi dimensioni e vuole vedere quali ordini avranno una commissione superiore a $ 300. Ecco il modo logico, ma non "SARgable" per farlo.

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

Ecco una possibile riscrittura della query, meno intuitiva ma "SARGable", in cui il calcolo viene spostato sull'altro lato del predicato.

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

La "SARGability" non solo si applica alle clausole WHERE, ma anche alle clausole JOINs, HAVING, GROUP BY e ORDER BY . Le occorrenze frequenti di prevenzione della "SARgability" nelle query coinvolgono le funzioni CONVERT(), CAST(), ISNULL(), COALESCE() utilizzate nelle clausole WHERE o JOIN che comportano l'analisi delle colonne. Nei casi di conversione del tipo di dati (CONVERT o CAST), la soluzione può essere quella di assicurarsi di confrontare gli stessi tipi di dati. Ecco un esempio in cui la colonna T1.ProdID viene convertita in modo esplicito nel tipo di dati INT in un oggetto JOIN. La conversione elimina l'utilizzo di un indice nella colonna join. Lo stesso problema si verifica con la conversione implicita in cui i tipi di dati sono diversi e SQL Server converte uno di essi per eseguire il join.

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

Per evitare un'analisi della tabella T1, è possibile modificare il tipo di dati sottostante della colonna ProdID dopo una pianificazione e una progettazione appropriate e quindi unire le due colonne senza utilizzare la funzione Converti ON T1.ProdID = T2.ProductID.

Un'altra soluzione consiste nel creare una colonna calcolata in T1 che utilizza la stessa funzione CONVERT() e quindi crearvi un indice. In questo modo Query Optimizer potrà utilizzare tale indice senza dover modificare la query.

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

In alcuni casi, le query non possono essere riscritte facilmente per consentire la "SARGability". In questi casi, verificare se la colonna calcolata con un indice su di essa può essere utile oppure mantenere la query così come era con la consapevolezza che questo può comportare un utilizzo della CPU più elevato.

Passaggio 7: disabilitare la tracciatura pesante

Verificare la presenza di Traccia SQL oppure la traccia XEvent che influisce sulle prestazioni di SQL Server e causa un utilizzo elevato della CPU. Ad esempio, l'uso degli eventi seguenti potrebbe causare un utilizzo elevato della CPU se si traccia un'attività di SQL Server pesante:

Eventi XML del piano di query (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile, query_post_execution_showplan, query_pre_execution_showplan)
Eventi a livello di istruzione (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
Eventi di accesso e disconnessione (login, process_login_finish, login_event, logout)
Blocco di eventi (lock_acquired, lock_cancel, lock_released)
Eventi in attesa (wait_info, wait_info_external)
Eventi di SQL Audit (a seconda del gruppo controllato e dell'attività SQL Server in tale gruppo)

Per identificare le tracce di XEvent o di Server attive, eseguire le query seguenti:

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

Passaggio 8: Correggere l'utilizzo elevato della CPU causato dalla contesa di spinlock

Per risolvere il comune utilizzo elevato della CPU causato dalla contesa di spinlock, vedere le sezioni seguenti.

SOS_CACHESTORE contesa di spinlock

Se l'istanza di SQL Server riscontra un elevato SOS_CACHESTORE conflitto di spinlock o si nota che i piani di query vengono spesso rimossi nei carichi di lavoro di query non pianificati, vedere l'articolo seguente e abilitare il flag T174 di traccia usando il DBCC TRACEON (174, -1) comando :

CORREZIONE: conflitto dello spinlock SOS_CACHESTORE nella cache del piano SQL Server ad hoc causa un utilizzo elevato della CPU in SQL Server.

Se la condizione della CPU elevata viene risolta tramite T174, abilitarla come parametro di avvio utilizzando Gestione configurazione di SQL Server.

Utilizzo casuale elevato della CPU a causa di SOS_BLOCKALLOCPARTIALLIST contesa di spinlock in computer con memoria di grandi dimensioni

Se l'istanza di SQL Server riscontra un utilizzo casuale elevato della CPU a causa di SOS_BLOCKALLOCPARTIALLIST conflitti di spinlock, è consigliabile applicare l'aggiornamento cumulativo 21 per SQL Server 2019. Per altre informazioni su come risolvere il problema, vedere le informazioni di riferimento sui bug 2410400 e DBCC DROPCLEANBUFFERS che forniscono una mitigazione temporanea.

Utilizzo elevato della CPU dovuto alla contesa di spinlock nei XVB_list nei computer di fascia alta

Se l'istanza di SQL Server riscontra uno scenario elevato di CPU causato da contesa di spinlock nei XVB_LIST computer a configurazione elevata (sistemi di fascia alta con un numero elevato di processori di generazione più recenti), abilitare il flag di traccia TF8102 insieme a TF8101.

Note

Un utilizzo elevato della CPU può causare conflitti di spinlock in molti altri tipi di spinlock. Per altre informazioni sugli spinlock, vedere Diagnosticare e risolvere conflitti di spinlock in SQL Server.

Passaggio 9: configurare la macchina virtuale

Se si utilizza una macchina virtuale, assicurarsi di non eseguire il provisioning eccessivo delle CPU e che queste siano configurate correttamente. Per altre informazioni, consultare Risoluzione dei problemi di prestazioni delle macchine virtuali ESX/ESXi (2001003).

Passaggio 10: incrementare le prestazioni del sistema per utilizzare altre CPU

Se le singole istanze di query utilizzano una capacità di CPU insufficiente, ma il carico di lavoro complessivo di tutte le query comporta un consumo elevato di CPU, è consigliabile aumentare il numero di CPU aggiungendone altre. Per trovare il numero di query che hanno superato una determinata soglia di utilizzo medio e massimo della CPU per esecuzione e che sono state eseguite molte volte nel sistema, utilizzare la query seguente (assicurarsi di modificare i valori delle due variabili in modo che corrispondano all'ambiente):

-- 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

Condividi tramite

Risolvere i problemi di utilizzo elevato della CPU in SQL Server

Passaggio 1: verificare che SQL Server stia causando un utilizzo elevato della CPU

Passaggio 2: identificare le query che contribuiscono all'utilizzo della CPU

Passaggio 3: aggiornare le statistiche

Passaggio 4: aggiungere gli indici mancanti

Passaggio 5: analizzare e risolvere i problemi sensibili ai parametri

Passaggio 6: analizzare e risolvere i problemi di "SARGability"

Passaggio 7: disabilitare la tracciatura pesante

Passaggio 8: Correggere l'utilizzo elevato della CPU causato dalla contesa di spinlock

SOS_CACHESTORE contesa di spinlock

Utilizzo casuale elevato della CPU a causa di SOS_BLOCKALLOCPARTIALLIST contesa di spinlock in computer con memoria di grandi dimensioni

Utilizzo elevato della CPU dovuto alla contesa di spinlock nei XVB_list nei computer di fascia alta

Passaggio 9: configurare la macchina virtuale

Passaggio 10: incrementare le prestazioni del sistema per utilizzare altre CPU

Vedi anche

Commenti e suggerimenti

Risorse aggiuntive