Risolvere i problemi relativi alle query che sembrano non terminare mai in SQL Server

Articolo
07/30/2024

Questo articolo descrive i passaggi per la risoluzione dei problemi relativi al problema in cui si dispone di una query che sembra non completare mai o che il completamento potrebbe richiedere molte ore o giorni.

Che cos'è una query senza fine?

Questo documento è incentrato sulle query che continuano a essere eseguite o compilate, ovvero la cpu continua ad aumentare. Non si applica alle query bloccate o in attesa di una risorsa che non viene mai rilasciata (la CPU rimane costante o cambia molto poco).

Importante

Se una query viene lasciata per completare l'esecuzione, alla fine verrà completata. Potrebbero essere necessari solo alcuni secondi o potrebbero essere necessari alcuni giorni.

Il termine never-ending viene usato per descrivere la percezione di una query che non viene completata quando, infatti, la query verrà completata.

Identificare una query senza fine

Per identificare se una query è in esecuzione continua o bloccata in un collo di bottiglia, seguire questa procedura:

Eseguire la query riportata di seguito:

DECLARE @cntr int = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    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,
                    atrn.name as transaction_name,
                    atrn.transaction_id,
                    atrn.transaction_state
        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
        LEFT JOIN (sys.dm_tran_session_transactions AS stran 
             JOIN sys.dm_tran_active_transactions AS atrn
                ON stran.transaction_id = atrn.transaction_id)
        ON stran.session_id =s.session_id
        WHERE r.session_id != @@SPID
        ORDER BY r.cpu_time DESC

    SET @cntr = @cntr + 1
WAITFOR DELAY '00:00:05'
END

Controllare l'output di esempio.
- I passaggi per la risoluzione dei problemi descritti in questo articolo sono applicabili specificamente quando si nota un output simile a quello seguente in cui la CPU aumenta proporzionalmente con il tempo trascorso, senza tempi di attesa significativi. È importante notare che le modifiche apportate in logical_reads non sono rilevanti in questo caso perché alcune richieste T-SQL associate alla CPU potrebbero non eseguire operazioni logiche di lettura ( ad esempio l'esecuzione di calcoli o un WHILE ciclo).
  
  session_id stato cpu_time logical_reads wait_time wait_type
  
  56 in esecuzione 7038 101000 0 NULL
  
  56 runnable 12040 301000 0 NULL
  
  56 in esecuzione 17020 523000 0 NULL
- Questo articolo non è applicabile se si osserva uno scenario di attesa simile a quello seguente in cui la CPU non cambia o cambia molto leggermente e la sessione è in attesa di una risorsa.
  
  session_id stato cpu_time logical_reads wait_time wait_type
  
  56 suspended 0 3 8312 LCK_M_U
  
  56 suspended 0 3 13318 LCK_M_U
  
  56 suspended 0 5 18331 LCK_M_U
Per altre informazioni, vedere Diagnosticare attese o colli di bottiglia.

session_id	stato	cpu_time	logical_reads	wait_type
56	in esecuzione	7038	101000	NULL
56	runnable	12040	301000	NULL
56	in esecuzione	17020	523000	NULL

session_id	stato	logical_reads	wait_time	wait_type
56	suspended	3	8312	LCK_M_U
56	suspended	3	13318	LCK_M_U
56	suspended	5	18331	LCK_M_U

Tempo di compilazione lungo

In rari casi, è possibile osservare che la CPU aumenta continuamente nel tempo, ma non dipende dall'esecuzione delle query. Potrebbe invece essere guidato da una compilazione eccessivamente lunga (l'analisi e la compilazione di una query). In questi casi, controllare la colonna di output transaction_name e cercare il valore sqlsource_transform. Questo nome di transazione indica una compilazione.

Raccogliere dati di diagnostica

Per raccogliere dati di diagnostica tramite SQL Server Management Studio (SSMS), seguire questa procedura:

Acquisire il codice XML del piano di esecuzione delle query stimato.
Esaminare il piano di query per verificare se sono presenti indicazioni evidenti sulla provenienza della lentezza. Esempi tipici includono:
- Analisi di tabelle o indici (esaminare le righe stimate).
- Cicli annidati basati su un set di dati di tabella esterno enorme.
- Cicli annidati con un ramo di grandi dimensioni nel lato interno del ciclo.
- Pool di tabelle.
- Funzioni nell'elenco SELECT che richiedono molto tempo per elaborare ogni riga.
Se la query viene eseguita rapidamente in qualsiasi momento, è possibile acquisire il piano di esecuzione XML effettivo "veloce" da confrontare.

L'infrastruttura leggera di profilatura query è stata introdotta in queste versioni di SQL Server. Consente di acquisire statistiche effettive durante l'esecuzione di una query lenta. Questa funzionalità di risoluzione dei problemi consente di esaminare gli operatori di query in un piano di query in fase di esecuzione e di comprendere dove viene impiegato la maggior parte del tempo in una query.

Per identificare i passaggi lenti della query usando l'infrastruttura di profilatura lightweight delle statistiche di esecuzione delle query v1, seguire questa procedura:

Eseguire i comandi seguenti per abilitare L'evento query_thread_profile XEvent:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START

Avviare la query non finale interessata dall'applicazione.

Eseguire il comando seguente più volte al minuto o così via per controllare le statistiche di esecuzione in fase di esecuzione per gli operatori del piano di query:

SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
            qp.session_id,
            convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
            qp.row_count,
            qp.estimate_row_count,
            qp.node_id,
            req.cpu_time,
            req.total_elapsed_time,
            substring
            (REPLACE
            (REPLACE
                (SUBSTRING
                (SQLText.text
                , (req.statement_start_offset/2) + 1
                , (
                    (CASE statement_END_offset
                        WHEN -1
                        THEN DATALENGTH(SQLText.text)  
                        ELSE req.statement_END_offset
                        END
                        - req.statement_start_offset)/2) + 1)
            , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
FROM sys.dm_exec_query_profiles qp 
RIGHT OUTER JOIN sys.dm_exec_requests req
    ON qp.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_sessions sess
    on req.session_id = sess.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
WHERE req.session_id <> @@SPID 
    AND sess.is_user_process = 1 
ORDER BY qp.session_id asc, row_count desc 
--this is to prevent massive grants
OPTION (max_grant_percent = 3, MAXDOP 1)

Acquisire tre o quattro snapshot distanziati un minuto per fornire dati sufficienti per l'analisi. In particolare, è possibile confrontare i row_count numeri per ogni operatore nel tempo e vedere che mostra un aumento significativo del numero di righe (milioni o più).
In una nuova finestra di query in SSMS acquisire un piano di query stimato per la query di problema eseguendo i comandi seguenti:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Usando l'ID nodo con il numero di righe più alto identificato dalla query nel passaggio 3, trovare lo stesso nodo nel piano di query stimato. Questo passaggio consentirà di comprendere quale operatore nel piano è la causa principale del tempo di esecuzione lungo.

Arrestare XEvent eseguendo il comando seguente:

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

È possibile usare l'infrastruttura di profilatura delle statistiche di esecuzione di query lightweight v2 per acquisire piani di query in tempo reale con valori effettivi per il conteggio delle righe. Questa infrastruttura di profilatura consente di esaminare gli operatori di query in un piano di query in fase di esecuzione e di comprendere dove viene impiegato la maggior parte del tempo in una query.

Per identificare i passaggi lenti nella query, seguire questa procedura:

Per abilitare l'infrastruttura leggera in queste versioni di SQL Server, usare uno dei metodi seguenti:

Abilitare il flag di traccia 7412 eseguendo il comando seguente:
```
DBCC TRACEON (7412, -1)
```

In alternativa, abilitare XEvent query_thread_profile eseguendo i comandi seguenti:

CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
 ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
   sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
   sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
   sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=OFF);

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START

Avviare la query non finale interessata dall'applicazione.

Usare un comando simile al seguente per identificare l'oggetto Session_id della query senza fine in esecuzione:

SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t

Eseguire il comando seguente tre o quattro volte a distanza di un minuto per esaminare il piano di query e le statistiche effettive nel piano. Assicurarsi di salvare ogni volta il piano di query, in modo da poterli confrontare e stabilire quale operatore di query richiede la maggior parte del tempo di CPU. In particolare, è possibile confrontare il numero di righe (numero effettivo di righe) per ogni operatore nel tempo e vedere quale degli operatori mostra un aumento significativo del numero di righe (milione o più). Sostituire <session_id> con il valore intero trovato nel passaggio 3 precedente.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Arrestare l'evento XEvent se è stato avviato o disabilitare il flag di traccia:

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)

È possibile usare l'infrastruttura di profilatura delle statistiche di esecuzione di query lightweight v3 per acquisire piani di query in tempo reale con valori effettivi per il conteggio delle righe. Questa infrastruttura di profilatura consente di esaminare gli operatori di query in un piano di query in fase di esecuzione e di comprendere dove viene impiegato la maggior parte del tempo in una query. La profilatura leggera è abilitata per impostazione predefinita in SQL Server 2019.

Per identificare i passaggi lenti nella query, seguire questa procedura:

Avviare la query non finale interessata dall'applicazione.

Usare un comando simile al seguente per identificare l'oggetto Session_id della query senza fine in esecuzione:

SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t

Eseguire il comando seguente tre o quattro volte per esaminare il piano di query e le statistiche effettive nel piano. Assicurarsi di salvare ogni volta il piano di query, in modo da poterli confrontare e stabilire quale operatore di query richiede la maggior parte del tempo di CPU. Sostituire <session_id> con il valore intero trovato nel passaggio 3 precedente.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
In particolare, selezionare il collegamento XML nella colonna query_plan . Una volta aperto il piano di query grafico in una nuova finestra, fare clic con il pulsante destro del mouse su di esso e selezionare Salva piano di esecuzione con nome. Ripetere i passaggi per acquisire tre o quattro snapshot distanziati di un minuto per fornire dati sufficienti per l'analisi. In particolare, è possibile confrontare il numero di righe (numero effettivo di righe) per ogni operatore nel tempo e vedere quale degli operatori mostra un aumento significativo del numero di righe (milione o più).
Nota

Se non si riceve alcun output da sys.dm_exec_query_statistics_xml, è possibile verificare se l'opzione LAST_QUERY_PLAN_STATS di database è stata disabilitata eseguendo il comando seguente:
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
È possibile abilitare le statistiche dell'ultimo piano di query a livello di database eseguendo ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

Metodo per esaminare i piani raccolti

Questa sezione illustra come esaminare i dati raccolti. Userà più piani di query XML (usando l'estensione *.sqlplan) raccolti in SQL Server 2016 SP1 e versioni successive.

Seguire questa procedura per confrontare i piani di esecuzione:

Aprire un file del piano di esecuzione delle query salvato in precedenza (con estensione sqlplan).
Fare clic con il pulsante destro del mouse in un'area vuota del piano di esecuzione e scegliere Confronta Showplan.
Scegliere il secondo file del piano di query da confrontare.
Cercare frecce spesse che indicano un numero elevato di righe che passano tra gli operatori. Selezionare quindi l'operatore prima o dopo la freccia e confrontare il numero di righe effettive tra due piani.
Confrontare il secondo e il terzo piano per verificare se il flusso più grande di righe si verifica negli stessi operatori.

Ecco un esempio:

Risoluzione

Assicurarsi che le statistiche vengano aggiornate per le tabelle usate nella query.
Cercare una raccomandazione sull'indice mancante nel piano di query e applicarla.
Riscrivere la query con l'obiettivo di semplificarla:
- Usare predicati più selettivi WHERE per ridurre i dati elaborati in anticipo.
- Scomponerlo.
- Selezionare alcune parti nelle tabelle temporanee e aggiungerle in un secondo momento.
- Rimuovere TOP, EXISTSe FAST (T-SQL) nelle query eseguite per molto tempo a causa dell'obiettivo di riga dell'utilità di ottimizzazione. In alternativa, è possibile usare l'hint DISABLE_OPTIMIZER_ROWGOAL . Per altre informazioni, vedere Obiettivi di riga non autorizzati.
- Evitare di usare espressioni di tabella comuni (CTEs) in questi casi, in quanto combinano istruzioni in una singola query di grandi dimensioni.
Provare a usare hint per la query per produrre un piano migliore:
- HASH JOIN o MERGE JOIN hint
- Hint FORCE ORDER
- Hint FORCESEEK
- RECOMPILE
- USARE PLAN N'<xml_plan>' se si dispone di un piano di query rapido che è possibile forzare
Usare Query Store (QDS) per forzare un piano noto se tale piano esiste e se la versione di SQL Server supporta Query Store.

Diagnosticare attese o colli di bottiglia

Questa sezione è inclusa qui come riferimento nel caso in cui il problema non sia una query di guida della CPU a esecuzione prolungata. È possibile usarlo per risolvere i problemi relativi alle query che sono lunghe a causa di attese.

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.

Condividi tramite