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 TIME
ON
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.
Eseguire la query con Includi piano di esecuzione effettivo attivato.
Selezionare l'operatore più a sinistra da Piano di esecuzione.
In Proprietà espandere la proprietà QueryTimeStats .
Controllare ElapsedTime e CpuTime.
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
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:
- Eseguire la query con Includi piano di esecuzione effettivo attivato.
- Fare clic con il pulsante destro del mouse sull'operatore più a sinistra nella scheda Piano di esecuzione
- Selezionare Proprietà e quindi la proprietà WaitStats .
- 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:
- Comprendere e risolvere i problemi di blocco (LCK_M_*)
- Comprendere e risolvere i problemi di blocco del database Azure SQL
- Risolvere i problemi di rallentamento delle prestazioni SQL Server causati da problemi di I/O (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
- Risolvere l'inserimento dell'ultima pagina PAGELATCH_EX contesa in SQL Server
- La memoria concede spiegazioni e soluzioni (RESOURCE_SEMAPHORE)
- Risolvere i problemi relativi alle query lente risultanti da ASYNC_NETWORK_IO tipo di attesa
- Risoluzione dei problemi relativi al tipo di attesa HADR_SYNC_COMMIT elevato con i gruppi di disponibilità Always On
- Funzionamento: CMEMTHREAD e debug
- Rendere interattive le attese del parallelismo (CXPACKET e CXCONSUMER)
- Attesa THREADPOOL
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
- Esaminare il piano di query della query
- Aggiorna statistiche
- Identificare e applicare indici mancanti. Per altri passaggi su come identificare gli indici mancanti, vedere Ottimizzare gli indici non cluster con suggerimenti sugli indici mancanti
- Riprogettare o riscrivere le query
- Identificare e risolvere i piani sensibili ai parametri
- Identificare e risolvere i problemi di capacità sarg
- Identificare e risolvere i problemi relativi agli obiettivi di riga in cui i cicli annidati a esecuzione prolungata possono essere causati da TOP, EXISTS, IN, FAST, SET ROWCOUNT, OPTION (FAST N). Per altre informazioni, vedere Miglioramenti di Row Goals Gone Rogue e Showplan - Row Goal EstimateRowsWithoutRowGoal
- Valutare e risolvere i problemi di stima della cardinalità . Per altre informazioni, vedere Riduzione delle prestazioni delle query dopo l'aggiornamento da SQL Server 2012 o versioni precedenti a 2014 o versioni successive
- Identificare e risolvere le quries che non sembrano mai complete, vedere Risolvere i problemi delle query che sembrano non finire mai in SQL Server
- Identificare e risolvere le query lente interessate dal timeout di Optimizer
- Identificare i problemi di prestazioni elevate della CPU. Per altre informazioni, vedere Risolvere i problemi di utilizzo elevato della CPU in SQL Server
- Risolvere i problemi di una query che mostra una differenza di prestazioni significativa tra due server
- Aumentare le risorse di calcolo nel sistema (CPU)
- Risolvere i problemi di prestazioni di UPDATE con piani ristretti e estesi
Risorse consigliate
- Tipi rilevabili di colli di bottiglia delle prestazioni delle query in SQL Server e Istanza gestita di SQL di Azure
- Strumenti di monitoraggio e ottimizzazione delle prestazioni
- Opzioni di ottimizzazione automatica in SQL Server
- Architettura dell'indice e linee guida per la progettazione
- Risolvere gli errori di timeout della query
- Risolvere i problemi di utilizzo elevato della CPU in SQL Server
- Riduzione delle prestazioni delle query dopo l'aggiornamento da SQL Server 2012 o versioni precedenti a 2014 o versioni successive
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per