Risolvere i problemi relativi alle query a esecuzione lenta in SQL Server

Versione originale del prodotto: SQL Server
Numero KB originale: 243589

Introduzione

Questo articolo descrive come gestire un problema di prestazioni che può verificarsi nelle applicazioni di database quando si usano SQL Server: prestazioni lente di una query o di un gruppo di query specifico. La metodologia seguente consente di limitare la causa del problema delle query lente e di indirizzare l'utente verso la risoluzione.

Trovare query lente

Per stabilire che si verificano problemi di prestazioni delle query nell'istanza di SQL Server, iniziare esaminando le query in base al tempo di esecuzione (tempo trascorso). Controllare se il tempo supera una soglia impostata (in millisecondi) in base a una baseline delle prestazioni stabilita. Ad esempio, in un ambiente di test di stress, è possibile che sia stata stabilita una soglia per il carico di lavoro non superiore a 300 ms ed è possibile usare questa soglia. È quindi possibile identificare tutte le query che superano tale soglia, concentrando l'attenzione su ogni singola query e sulla durata predefinita della baseline delle prestazioni. In definitiva, gli utenti aziendali si preoccupano della durata complessiva delle query di database; pertanto, l'obiettivo principale è la durata dell'esecuzione. Altre metriche, ad esempio il tempo di CPU e le letture logiche, vengono raccolte per semplificare l'analisi.

  • Per le istruzioni attualmente in esecuzione, controllare total_elapsed_time e cpu_time colonne in sys.dm_exec_requests. Eseguire la query seguente per ottenere i dati:

    SELECT 
        req.session_id
        , req.total_elapsed_time AS duration_ms
        , req.cpu_time AS cpu_time_ms
        , req.total_elapsed_time - req.cpu_time AS wait_time
        , req.logical_reads
        , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
           ((CASE statement_end_offset
               WHEN -1
               THEN DATALENGTH(ST.text)  
               ELSE req.statement_end_offset
             END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
          1, 512)  AS statement_text  
    FROM sys.dm_exec_requests AS req
        CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
    ORDER BY total_elapsed_time DESC;
    
  • Per le esecuzioni precedenti della query, controllare last_elapsed_time e last_worker_time colonne in sys.dm_exec_query_stats. Eseguire la query seguente per ottenere i dati:

    SELECT t.text,
         (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
         (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
         ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
         qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
         qs.total_logical_writes / qs.execution_count AS avg_writes,
         (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
    FROM sys.dm_exec_query_stats qs
         CROSS apply sys.Dm_exec_sql_text (sql_handle) t
    WHERE t.text like '<Your Query>%'
    -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
    ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC
    

    Nota

    Se avg_wait_time mostra un valore negativo, si tratta di una query parallela.

  • Se è possibile eseguire la query su richiesta in SQL Server Management Studio (SSMS) o Azure Data Studio, eseguirla con SET STATISTICS TIMEON e SET STATISTICS IOON.

    SET STATISTICS TIME ON
    SET STATISTICS IO ON
    <YourQuery>
    SET STATISTICS IO OFF
    SET STATISTICS TIME OFF
    

    Dai messaggi verranno quindi visualizzati il tempo di CPU, il tempo trascorso e le letture logiche come segue:

      Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
    
      SQL Server Execution Times:
        CPU time = 460 ms,  elapsed time = 470 ms.
    
  • Se è possibile raccogliere un piano di query, controllare i dati delle proprietà del piano di esecuzione.

    1. Eseguire la query con Includi piano di esecuzione effettivo attivato.

    2. Selezionare l'operatore più a sinistra da Piano di esecuzione.

    3. In Proprietà espandere la proprietà QueryTimeStats .

    4. Controllare ElapsedTime e CpuTime.

      Screenshot della finestra delle proprietà del piano di esecuzione SQL Server con la proprietà QueryTimeStats espansa.

Esecuzione e attesa: perché le query sono lente?

Se si trovano query che superano la soglia predefinita, esaminare il motivo per cui potrebbero essere lente. La causa dei problemi di prestazioni può essere raggruppata in due categorie, in esecuzione o in attesa:

  • WAITING: le query possono essere lente perché sono in attesa di un collo di bottiglia per un lungo periodo di tempo. Vedere un elenco dettagliato dei colli di bottiglia nei tipi di attese.

  • RUNNING: le query possono essere lente perché vengono eseguite (in esecuzione) per un lungo periodo di tempo. In altre parole, queste query usano attivamente le risorse della CPU.

Una query può essere eseguita per un certo periodo di tempo e attendere per un certo periodo di tempo (durata). Tuttavia, l'obiettivo è determinare quale sia la categoria dominante che contribuisce al tempo trascorso a lungo. Pertanto, la prima attività consiste nel stabilire in quale categoria rientrano le query. È semplice: se una query non è in esecuzione, è in attesa. Idealmente, una query trascorre la maggior parte del tempo trascorso in uno stato di esecuzione e pochissimo tempo in attesa delle risorse. Inoltre, nello scenario migliore, una query viene eseguita all'interno o al di sotto di una baseline predeterminata. Confrontare il tempo trascorso e il tempo della CPU della query per determinare il tipo di problema.

Tipo 1: associato alla CPU (runner)

Se il tempo della CPU è vicino, uguale o superiore al tempo trascorso, è possibile considerarlo come una query associata alla CPU. Ad esempio, se il tempo trascorso è 3000 millisecondi (ms) e il tempo della CPU è di 2900 ms, significa che la maggior parte del tempo trascorso viene impiegato per la CPU. È quindi possibile dire che si tratta di una query associata alla CPU.

Esempi di query in esecuzione (associate alla CPU):

Tempo trascorso (ms) Tempo CPU (ms) Letture (logiche)
3200 3000 300000
1080 1000 20

Le letture logiche, ovvero la lettura di pagine di dati/indici nella cache, sono più frequentemente i driver dell'utilizzo della CPU in SQL Server. Potrebbero esserci scenari in cui l'uso della CPU proviene da altre origini: un ciclo while (in T-SQL o in altro codice come XProcs o oggetti CRL SQL). Il secondo esempio nella tabella illustra questo scenario, in cui la maggior parte della CPU non proviene dalle letture.

Nota

Se il tempo della CPU è maggiore della durata, indica che viene eseguita una query parallela; più thread usano la CPU contemporaneamente. Per altre informazioni, vedere Query parallele - runner o cameriere.

Tipo 2: in attesa di un collo di bottiglia (cameriere)

Una query è in attesa di un collo di bottiglia se il tempo trascorso è significativamente superiore al tempo della CPU. Il tempo trascorso include il tempo di esecuzione della query sulla CPU (tempo cpu) e il tempo di attesa per il rilascio di una risorsa (tempo di attesa). Ad esempio, se il tempo trascorso è 2000 ms e il tempo di CPU è 300 ms, il tempo di attesa è 1700 ms (2000 - 300 = 1700). Per altre informazioni, vedere Tipi di attese.

Esempi di query in attesa:

Tempo trascorso (ms) Tempo CPU (ms) Letture (logiche)
2000 300 28000
10080 700 80000

Query parallele - strumento di esecuzione o cameriere

Le query parallele possono usare più tempo della CPU rispetto alla durata complessiva. L'obiettivo del parallelismo è consentire a più thread di eseguire contemporaneamente parti di una query. In un secondo dell'ora di clock, una query può usare otto secondi di tempo cpu eseguendo otto thread paralleli. Di conseguenza, è difficile determinare una query associata alla CPU o in attesa in base al tempo trascorso e alla differenza di tempo della CPU. Tuttavia, come regola generale, seguire i principi elencati nelle due sezioni precedenti. Il riepilogo è:

  • Se il tempo trascorso è molto maggiore del tempo della CPU, considerarlo un cameriere.
  • Se il tempo della CPU è molto maggiore del tempo trascorso, considerarlo uno strumento di esecuzione.

Esempi di query parallele:

Tempo trascorso (ms) Tempo CPU (ms) Letture (logiche)
1200 8100 850000
3080 12300 1500000

Rappresentazione visiva di alto livello della metodologia

Lo screenshot mostra una rappresentazione visiva di alto livello della metodologia per la risoluzione dei problemi delle query lente.

Diagnosticare e risolvere le query in attesa

Se si è stabilito che le query di interesse sono camerieri, il passaggio successivo consiste nel concentrarsi sulla risoluzione dei problemi di collo di bottiglia. In caso contrario, passare al passaggio 4: Diagnosticare e risolvere le query in esecuzione.

Per ottimizzare una query in attesa di colli di bottiglia, identificare la durata dell'attesa e la posizione del collo di bottiglia (tipo di attesa). Dopo aver confermato il tipo di attesa , ridurre il tempo di attesa o eliminare completamente l'attesa.

Per calcolare il tempo di attesa approssimativo, sottrarre il tempo di CPU (tempo di lavoro) dal tempo trascorso di una query. In genere, il tempo della CPU è il tempo di esecuzione effettivo e la parte rimanente della durata della query è in attesa.

Esempi di come calcolare la durata approssimativa dell'attesa:

Tempo trascorso (ms) Tempo CPU (ms) Tempo di attesa (ms)
3200 3000 200
7080 1000 6080

Identificare il collo di bottiglia o l'attesa

  • Per identificare le query cronologiche in attesa prolungata(ad esempio, >il 20% del tempo trascorso complessivo è il tempo di attesa), eseguire la query seguente. Questa query usa le statistiche sulle prestazioni per i piani di query memorizzati nella cache dall'inizio di SQL Server.

    SELECT t.text,
             qs.total_elapsed_time / qs.execution_count
             AS avg_elapsed_time,
             qs.total_worker_time / qs.execution_count
             AS avg_cpu_time,
             (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
             AS avg_wait_time,
             qs.total_logical_reads / qs.execution_count
             AS avg_logical_reads,
             qs.total_logical_writes / qs.execution_count
             AS avg_writes,
             qs.total_elapsed_time
             AS cumulative_elapsed_time
    FROM sys.dm_exec_query_stats qs
             CROSS apply sys.Dm_exec_sql_text (sql_handle) t
    WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
             > 0.2
    ORDER BY qs.total_elapsed_time / qs.execution_count DESC
    
  • Per identificare le query attualmente in esecuzione con attese superiori a 500 ms, eseguire la query seguente:

    SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
    FROM sys.dm_exec_requests r 
       JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
    WHERE wait_time > 500
    AND is_user_process = 1
    
  • Se è possibile raccogliere un piano di query, controllare WaitStats dalle proprietà del piano di esecuzione in SSMS:

    1. Eseguire la query con Includi piano di esecuzione effettivo attivato.
    2. Fare clic con il pulsante destro del mouse sull'operatore più a sinistra nella scheda Piano di esecuzione
    3. Selezionare Proprietà e quindi la proprietà WaitStats .
    4. Controllare WaitTimeMs e WaitType.
  • Se si ha familiarità con gli scenari PSSDiag/SQLdiag o SQL LogScout LightPerf/GeneralPerf, è consigliabile usarli per raccogliere statistiche sulle prestazioni e identificare le query in attesa nell'istanza di SQL Server. È possibile importare i file di dati raccolti e analizzare i dati sulle prestazioni con SQL Nexus.

Riferimenti che consentono di eliminare o ridurre le attese

Le cause e le risoluzioni per ogni tipo di attesa variano. Non esiste un metodo generale per risolvere tutti i tipi di attesa. Ecco gli articoli per risolvere i problemi comuni relativi ai tipi di attesa:

Per le descrizioni di molti tipi di attesa e di ciò che indicano, vedere la tabella in Tipi di attese.

Diagnosticare e risolvere le query in esecuzione

Se il tempo della CPU (ruolo di lavoro) è molto vicino alla durata complessiva trascorso, la query trascorre la maggior parte della durata dell'esecuzione. In genere, quando il motore SQL Server determina un utilizzo elevato della CPU, l'utilizzo elevato della CPU proviene da query che determinano un numero elevato di letture logiche (il motivo più comune).

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

Metodi comuni per risolvere query a esecuzione prolungata associate alla CPU