Risolvere i problemi di utilizzo elevato della CPU in SQL Server

Si applica a: SQL Server

Sommario

Un utilizzo elevato della CPU in Microsoft SQL Server (il sqlservr.exe processo che richiede un tempo eccessivo del processore) deriva in genere da query inefficienti, indici mancanti, statistiche obsolete, piani sensibili ai parametri o un aumento del carico di lavoro. Questo articolo illustra in modo dettagliato una procedura di risoluzione dei problemi ordinata per verificare prima che SQL Server sia la causa del carico sulla CPU e quindi identificare le prime query che utilizzano la CPU. Illustra quindi correzioni mirate, ad esempio l'aggiornamento delle statistiche, l'aggiunta di indici mancanti, la risoluzione dei problemi di parameter sniffing e SARGability, la disabilitazione della traccia pesante e la mitigazione della contesa di spinlock. Infine, tratta l'ottimizzazione del sistema operativo e delle macchine virtuali, incluse le combinazioni per il risparmio energia di Windows e indicazioni su quando passare a un numero maggiore di CPU. Usare questi passaggi per ridurre l'utilizzo costante della CPU, ripristinare le prestazioni delle query e decidere quando è necessario potenziare l'hardware.

Cause comuni dell'utilizzo elevato della CPU in SQL Server

Sebbene esistano molte possibili cause per un utilizzo elevato della CPU in SQL Server, le cause seguenti sono le più comuni:

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

Usare la procedura seguente per risolvere i problemi di utilizzo elevato della CPU in SQL Server.

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 Processi, 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 su ogni processore, il valore massimo è 100% * (numero di 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>Dashboard prestazioni.

  Il dashboard illustra un grafico denominato Utilizzo della CPU del 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 rotondo o F5 per visualizzare l'utilizzo aggiornato.

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

Se il Sqlservr.exe processo causa un utilizzo elevato della CPU, senza dubbio il motivo più comune è rappresentato dalle query di SQL Server che eseguono scansioni di tabella o di indice, seguite successivamente da operazioni di ordinamento e hash e cicli (operatore ciclo annidato o WHILE (T-SQL)). Per avere un'idea di quanta CPU le query stiano attualmente utilizzando, rispetto alla capacità complessiva della CPU, eseguire l'istruzione seguente:

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 attualmente responsabili dell'attività a utilizzo elevato della CPU, 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 stanno impegnando la CPU in questo momento, per cercare le query storiche limitate dalla 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: Aggiorna le statistiche

Dopo aver identificato le query che utilizzano la maggior parte della CPU, aggiornare le statistiche per le tabelle usate da queste query. Utilizzare la sp_updatestats stored procedure di sistema per aggiornare le statistiche per tutte le tabelle interne e definite dall'utente nel database corrente. Ad esempio:

exec sp_updatestats

Per maggiori informazioni su sp_updatestats, consultare sp_updatestats.

Se SQL Server continua a utilizzare una quantità eccessiva di CPU, 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. È possibile identificare gli indici mancanti e crearli per contribuire a migliorare le prestazioni.

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

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

   

3. 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 clusterizzati 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, è indice di un problema sensibile ai parametri (PSP, noto anche come "problema relativo al parameter sniffing").

Per ridurre i problemi dovuti alla sensibilità 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 buono" 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 l'abbonamento che causa il problema. Ad esempio, per trovare piani di query che fanno riferimento alla tabella Person.Person in AdventureWorks, è possibile usare questa query per trovare l'handle della query. È quindi possibile rimuovere 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: esaminare 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 nell'indice per velocizzare l'esecuzione della query. Molte strutture delle 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 letterale di stringa. 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 renderle 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 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 perdita della SARGabilità nelle query coinvolgono le funzioni CONVERT(), CAST(), ISNULL(), COALESCE() utilizzate nelle clausole WHERE o JOIN che comportano la scansione 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 JOIN. La conversione impedisce l'uso di un indice nella colonna di 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 convert 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: Disabilita il tracciamento dettagliato

Verificare la presenza di SQL Trace o 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 statement (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
• Eventi di accesso e disconnessione (login, process_login_finish, login_event, logout)
• Eventi di blocco (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 contenzione dello spinlock

Se l'istanza di SQL Server riscontra SOS_CACHESTORE conflitti di spinlock pesanti o si noti che i piani di query vengono spesso rimossi nei carichi di lavoro di query non pianificati, vedere l'articolo seguente e abilitare il flag T174di 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 contesa dello spinlock SOS_BLOCKALLOCPARTIALLIST in computer con memoria di grandi dimensioni

Nei computer con memoria di grandi dimensioni, la SOS_BLOCKALLOCPARTIALLIST contesa di spinlock può produrre picchi casuali nell'utilizzo della CPU. Usare la procedura seguente per ridurre la contesa nel singolo elenco di allocazione parziale a livello di server partizionandolo tra più elenchi.

1. Confermare l'asserzione. Verificare sys.dm_os_spinlock_stats valori collisions e spins elevati sullo spinlock SOS_BLOCKALLOCPARTIALLIST. Se la contenzione è correlata ai picchi di CPU, continuare con i passaggi seguenti.

2. Attenuare temporaneamente il picco della CPU. L'esecuzione DBCC DROPCLEANBUFFERS rilascia l'elenco di allocazione parziale dei blocchi e fornisce una mitigazione temporanea mentre si pianifica la correzione permanente.

3. Assicurarsi che la build di SQL Server includa la correzione. Il comportamento di partizionamento viene recapitato tramite flag di traccia 8142 e 8145 ed è stato introdotto per la prima volta nell'aggiornamento cumulativo 21 SQL Server 2019 per risolvere il bug 2410400. Se si usa una build precedente, applicare l'aggiornamento cumulativo più recente per la versione prima di abilitare i flag di traccia.

4. Abilitare il flag di traccia 8142. Abilitare il flag di traccia 8142 come parametro di avvio globale. Questo flag di traccia partiziona l'elenco protetto da spinlock per CPU, fino a 64 partizioni, che in genere è sufficiente per eliminare la contesa.

5. Se la contenzione persiste, abilitare il flag di traccia 8145. Nei sistemi in cui 64 partizioni cpu non sono sufficienti, abilitare anche il flag di traccia 8145 come parametro di avvio globale. Il flag di traccia 8145 modifica il partizionamento abilitato dal flag di traccia 8142 in modo che sia per nodo soft-NUMA, anziché per CPU. Non ha alcun effetto a meno che non sia anch’esso abilitato il flag di trace 8142.

6. Riavvia il servizio SQL Server in modo che i flag di traccia di avvio abbiano effetto, quindi ricontrolla sys.dm_os_spinlock_stats per confermare che la contesa si sia ridotta e che l'utilizzo della CPU si sia stabilizzato.

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

Se l'istanza di SQL Server riscontra uno scenario di utilizzo elevato della CPU causato da contesa sullo spinlock XVB_LIST su macchine con configurazioni elevate (sistemi di fascia alta con un numero elevato di processori (CPU) di generazione più recente), abilitare il flag di traccia TF8102 insieme a TF8101.

Passaggio 9: Controllare le impostazioni del risparmio energia a livello di sistema operativo

I carichi di lavoro di SQL Server potrebbero riscontrare prestazioni ridotte e causare un utilizzo elevato della CPU nel sistema quando Windows è configurato con il risparmio di energia bilanciato predefinito. L'impostazione risparmio energia bilanciata potrebbe ridurre la velocità di clock della CPU per risparmiare energia. Ad esempio, un processore valutato a 3,00 GHz potrebbe ridurre la frequenza fino a 1,2 GHz. Di conseguenza, i carichi di lavoro che in genere utilizzano circa il 30% della CPU potrebbero raggiungere il 100% di utilizzo a causa della ridotta velocità di clock. Per mantenere prestazioni coerenti e ottimali per carichi di lavoro a elevato utilizzo di calcolo SQL Server, configurare il sistema per l'uso della combinazione di risparmio High Performance. Questa impostazione garantisce che la CPU funzioni a piena velocità nominale, aiutando a evitare colli di bottiglia delle prestazioni. Per altre informazioni, vedere Rallentamento delle prestazioni in Windows Server quando si usa la combinazione di risparmio energia bilanciata.

Passaggio 10: 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 11: Aumentare le prestazioni del sistema per usare più CPU

Se le singole istanze di query usano una capacità cpu minima, ma il carico di lavoro complessivo di tutte le query causa un utilizzo elevato della CPU, è consigliabile aumentare le prestazioni del computer aggiungendo altre CPU. Usare la query seguente per trovare il numero di query che superano una determinata soglia di utilizzo medio e massimo della CPU per esecuzione e vengono eseguite molte volte nel sistema. 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 

Contenuti correlati

• Allocazioni elevate di CPU o memoria possono verificarsi con query che utilizzano un ciclo annidato ottimizzato o un ordinamento batch
• Aggiornamenti e opzioni di configurazione consigliati per SQL Server 2017 e 2016 con carichi di lavoro a prestazioni elevate
• Monitoraggio e ottimizzazione delle prestazioni
• Procedure consigliate con il Query Store