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

  • Articolo

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 usa 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 indirizzare l'utente alla 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 di prestazioni stabilita. In un ambiente di test di stress, ad esempio, è 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, concentrandosi su ogni singola query e sulla durata di base delle prestazioni predefinita. 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 tempo cpu e letture logiche, vengono raccolte per limitare 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 TIME ON e SET STATISTICS IO ON.

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

    Quindi, dai messaggi verranno visualizzati il tempo cpu, il tempo trascorso e le letture logiche simili alle seguenti:

      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 dalle proprietà del piano di esecuzione.

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

    2. Selezionare l'operatore più a sinistra dal piano di esecuzione.

    3. In Proprietà espandere la proprietà QueryTimeStats.

    4. Controllare ElapsedTime e CpuTime.

      Screenshot della finestra delle proprietà del piano di esecuzione di 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 molto tempo. Vedere un elenco dettagliato dei colli di bottiglia nei tipi di attese.

  • RUNNING: le query possono essere lente perché sono in esecuzione (in esecuzione) per molto tempo. In altre parole, queste query usano attivamente le risorse della CPU.

Una query, nella sua durata, può essere in esecuzione o in attesa per un certo periodo di tempo; Tuttavia, l'attenzione consiste nel determinare quale sia la categoria dominante che contribuisce al tempo trascorso lungo. Pertanto, la prima attività consiste nello stabilire in quale categoria rientrano le query. È semplice: se una query non è in esecuzione, è in attesa. Idealmente, una query impiega la maggior parte del tempo trascorso in uno stato di esecuzione e molto poco tempo in attesa delle risorse. Nello scenario migliore, inoltre, una query viene eseguita all'interno o al di sotto di una baseline predeterminata. Confrontare il tempo trascorso e il tempo di 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 è di 3000 millisecondi (ms) e il tempo della CPU è 2900 ms, significa che la maggior parte del tempo trascorso viene impiegato sulla CPU. È quindi possibile dire che si tratta di una query associata alla CPU.

Esempi di query con associazione alla CPU:

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

Letture logiche: lettura di pagine di dati/indici nella cache, sono spesso i driver di 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 uno scenario di questo tipo, 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 waiter.

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

Una query è in attesa di un collo di bottiglia se il tempo trascorso è significativamente maggiore del tempo della CPU. Il tempo trascorso include il tempo di esecuzione della query sulla CPU (tempo CPU) e il tempo di attesa del 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 di CPU (ms) Letture (logiche)
2000 300 28000
10080 700 80000

Query parallele - Runner o waiter

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 di tempo, una query può usare otto secondi di tempo della CPU eseguendo otto thread paralleli. Di conseguenza, diventa difficile determinare un limite di CPU o una query 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 di CPU (ms) Letture (logiche)
1200 8100 850000
3080 12300 1500000

Rappresentazione visiva generale della metodologia

Lo screenshot mostra una rappresentazione visiva generale della metodologia per la risoluzione dei problemi relativi alle query lente.

Diagnosticare e risolvere le query in attesa

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

Per ottimizzare una query in attesa di colli di bottiglia, identificare il tempo di attesa e il collo di bottiglia (il tipo di attesa). Una volta 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 cpu (tempo di lavoro) dal tempo trascorso di una query. In genere, il tempo di 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 di 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 di attesa 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.
    2. Fare clic con il pulsante destro del mouse sull'operatore più a sinistra nella scheda Piano di esecuzione
    3. Selezionare Proprietà e quindi 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 per 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 alcuni articoli per risolvere i problemi comuni relativi al tipo 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 (worker) è molto vicino alla durata complessiva trascorsa, la query impiega la maggior parte della durata dell'esecuzione. In genere, quando il motore di 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 che sono responsabili dell'attività a 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 guidano la CPU in questo momento, è possibile eseguire l'istruzione seguente per cercare query cronologiche associate alla CPU:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Metodi comuni per risolvere query con esecuzione prolungata associate alla CPU