Risolvere i problemi di utilizzo elevato della CPU in SQL Server

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 di indici per le condizioni seguenti:
  • Statistiche non aggiornate
  • Indici mancanti
  • Problemi relativi al piano sensibile ai parametri (PSP)
  • Query progettate in modo non corretto
• 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 utilizzare 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% (%User Time è la somma del tempo del processore in ogni processore, il valore massimo è 100% * (nessuna CPU)), il processo di SQL Server sta causando 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.

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 Server 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 un consumo di CPU più elevato, aggiornare le statistiche delle tabelle usate da tali query. È possibile utilizzare la store 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

Per maggiori informazioni su sp_updatestats, consultare sp_updatestats.

Se SQL Server usa ancora una capacità di CPU eccessiva, passare 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.

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 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").

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 eliminato 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 all'interno di una query viene considerato "SARGable" (Search ARGument-able) quando il motore di SQL Server può utilizzare una ricerca di indice per velocizzare l'esecuzione della query. Molte progettazioni di query impediscono la "SARGability" e comportano analisi di tabelle o di indici e 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, per poter eseguire un confronto è necessario recuperare prima tutte le righe della tabella e quindi applicare la funzione. Il recupero di tutte le righe dalla tabella indica un'analisi 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 qualsiasi funzione o calcolo sulle colonne nel predicato di ricerca in genere rende la query "non-SARgable" e comporta un consumo di CPU più elevato. In genere, le soluzioni implicano 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 eseguita una ricerca in 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 di XEvent che influisce sulle prestazioni di SQL Server e causa un utilizzo elevato della CPU. Ad esempio, l'utilizzo degli eventi seguenti può comportare 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 da conflitti di spinlock

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

SOS_CACHESTORE contesa spinlock

Se nell'istanza di SQL Server si verifica una forte SOS_CACHESTORE contesa 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 di 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 elevato casuale della CPU a causa della contesa di spinlock SOS_BLOCKALLOCPARTIALLIST nei computer con memoria di grandi dimensioni

Se l'istanza di SQL Server riscontra un utilizzo elevato casuale 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 informazioni di riferimento sui bug 2410400 e DBCC DROPCLEANBUFFERS che forniscono una mitigazione temporanea.

Utilizzo elevato della CPU a causa della contesa di spinlock nei XVB_list nei computer di fascia alta

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

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 maggiori informazioni, consultare Risoluzione dei problemi delle prestazioni delle macchine virtuali ESX/ESXi (2001003).

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

Se le singole istanze di query usano 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 (assicurarsi di modificare i valori delle due variabili in modo che corrispondano all'ambiente), utilizzare la query seguente:

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

Vedere anche

• Le concessioni elevate di CPU o memoria possono verificarsi con query che utilizzano un ciclo annidato ottimizzato o un ordinamento batch
• Opzioni di configurazione e aggiornamenti consigliati per SQL Server con carichi di lavoro a prestazioni elevate
• Aggiornamenti e opzioni di configurazione consigliati per SQL Server 2017 e 2016 con carichi di lavoro a prestazioni elevate