Rozwiązywanie problemów z zapytaniami, które wydają się nigdy nie kończyć w programie SQL Server

Artykuł
07/30/2024

W tym artykule opisano kroki rozwiązywania problemu polegające na tym, że masz zapytanie, które wydaje się nigdy nie zostać ukończone, lub ukończenie jego ukończenia może potrwać wiele godzin lub dni.

Co to jest niekończące się zapytanie?

Ten dokument koncentruje się na zapytaniach, które nadal są wykonywane lub kompilowane, czyli ich procesor CPU nadal rośnie. Nie ma zastosowania do zapytań, które są zablokowane lub oczekują na jakiś zasób, który nigdy nie jest zwalniany (procesor pozostaje stały lub zmienia się bardzo mało).

Ważne

Jeśli zapytanie pozostanie do zakończenia jego wykonywania, zostanie zakończone. Może to potrwać zaledwie kilka sekund lub może potrwać kilka dni.

Termin niekończący się jest używany do opisywania postrzegania zapytania, które nie kończy się w rzeczywistości, zapytanie zostanie ostatecznie ukończone.

Identyfikowanie niekończącego się zapytania

Aby określić, czy zapytanie jest stale wykonywane, czy zablokowane w wąskim gardło, wykonaj następujące kroki:

Uruchom poniższe zapytanie:

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

Sprawdź przykładowe dane wyjściowe.
- Kroki rozwiązywania problemów w tym artykule mają zastosowanie w szczególności, gdy zauważysz dane wyjściowe podobne do następujących, w których procesor CPU zwiększa się proporcjonalnie wraz z upływem czasu bez znaczących czasów oczekiwania. Należy pamiętać, że w tym przypadku zmiany logical_reads nie są istotne, ponieważ niektóre żądania T-SQL powiązane z procesorem CPU mogą w ogóle nie wykonywać żadnych operacji odczytu logicznego (na przykład wykonywania obliczeń lub WHILE pętli).
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 uruchomiono 7038 101000 0 NULL
  
  56 runnable 12040 301000 0 NULL
  
  56 uruchomiono 17020 523000 0 NULL
- Ten artykuł nie ma zastosowania, jeśli obserwujesz scenariusz oczekiwania podobny do poniższego, w którym procesor CPU nie zmienia się lub zmienia się bardzo nieznacznie, a sesja czeka na zasób.
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 zawieszony 0 3 8312 LCK_M_U
  
  56 zawieszony 0 3 13318 LCK_M_U
  
  56 zawieszony 0 5 18331 LCK_M_U
Aby uzyskać więcej informacji, zobacz Diagnozowanie oczekiwań lub wąskich gardeł.

session_id	status	cpu_time	logical_reads	wait_type
56	uruchomiono	7038	101000	NULL
56	runnable	12040	301000	NULL
56	uruchomiono	17020	523000	NULL

session_id	status	logical_reads	wait_time	wait_type
56	zawieszony	3	8312	LCK_M_U
56	zawieszony	3	13318	LCK_M_U
56	zawieszony	5	18331	LCK_M_U

Długi czas kompilacji

W rzadkich przypadkach można zauważyć, że procesor CPU stale rośnie w czasie, ale nie jest napędzany przez wykonywanie zapytań. Zamiast tego może to być spowodowane nadmiernie długą kompilacją (analizowanie i kompilowanie zapytania). W takich przypadkach sprawdź kolumnę danych wyjściowych transaction_name i poszukaj wartości sqlsource_transform. Ta nazwa transakcji wskazuje kompilację.

Zbieranie danych diagnostycznych

Aby zebrać dane diagnostyczne przy użyciu programu SQL Server Management Studio (SSMS), wykonaj następujące kroki:

Przechwyć szacowany kod XML planu wykonywania zapytania.
Przejrzyj plan zapytania, aby sprawdzić, czy istnieją oczywiste wskazania, skąd może pochodzić spowolnienie. Typowe przykłady:
- Skanowanie tabeli lub indeksu (spójrz na szacowane wiersze).
- Zagnieżdżone pętle sterowane przez ogromny zewnętrzny zestaw danych tabeli.
- Zagnieżdżone pętle z dużą gałęzią w wewnętrznej części pętli.
- tabel.
- Funkcje na SELECT liście, które zajmują dużo czasu na przetworzenie każdego wiersza.
Jeśli zapytanie działa szybko w dowolnym momencie, możesz przechwycić "szybkie" wykonania rzeczywistego planu wykonywania XML do porównania.

Uproszczona infrastruktura profilowania zapytań została wprowadzona w tych wersjach programu SQL Server. Umożliwia ona przechwytywanie rzeczywistych statystyk podczas wykonywania powolnych zapytań. Ta funkcja rozwiązywania problemów umożliwia badanie operatorów zapytań w planie zapytań w czasie wykonywania i zrozumienie, gdzie większość czasu spędzi w zapytaniu.

Aby zidentyfikować powolne kroki zapytania przy użyciu uproszczonej infrastruktury profilowania statystyk wykonywania zapytań w wersji 1, wykonaj następujące kroki:

Uruchom następujące polecenia, aby włączyć środowisko 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

Uruchom zapytanie, którego dotyczy problem, nigdy nie kończące się z poziomu aplikacji.

Uruchom następujące polecenie wielokrotnie co minutę, aby sprawdzić statystyki wykonywania w czasie wykonywania dla operatorów planu zapytania:

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)

Przechwyć trzy lub cztery migawki rozmieszczone na minutę, aby zapewnić wystarczające dane do analizy. W szczególności można porównać row_count liczby dla każdego operatora w czasie i zobaczyć, co pokazuje znaczny wzrost liczby wierszy (milion lub więcej).
W nowym oknie zapytania w programie SSMS przechwyć szacowany plan zapytania dla zapytania problemu, uruchamiając następujące polecenia:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Używając identyfikatora węzła z największą liczbą wierszy zidentyfikowaną przez zapytanie w kroku 3, znajdź ten sam węzeł w szacowanym planie zapytania. Ten krok pomoże zrozumieć, który operator w planie jest główną przyczyną długiego czasu wykonywania.
Zatrzymaj element XEvent, uruchamiając następujące polecenie:
```
ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP
```

Możesz użyć uproszczonej infrastruktury profilowania statystyk wykonywania zapytań w wersji 2 , aby przechwycić plany zapytań na żywo z rzeczywistymi wartościami liczby wierszy. Ta infrastruktura profilowania umożliwia badanie operatorów zapytań w planie zapytań w czasie wykonywania i zrozumienie, gdzie większość czasu jest poświęcana w zapytaniu.

Aby zidentyfikować powolne kroki w zapytaniu, wykonaj następujące kroki:

Aby włączyć uproszczoną infrastrukturę w tych wersjach programu SQL Server, użyj jednej z następujących metod:

Włącz flagę śledzenia 7412, uruchamiając następujące polecenie:
```
DBCC TRACEON (7412, -1)
```

Alternatywnie włącz program query_thread_profile XEvent, uruchamiając następujące polecenia:

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

Uruchom zapytanie, którego dotyczy problem, nigdy nie kończące się z poziomu aplikacji.
Użyj polecenia podobnego do poniższego, aby zidentyfikować Session_id uruchomione nigdy nie kończące się zapytanie:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Uruchom następujące polecenie trzy lub cztery razy oddalone o jedną minutę, aby zbadać plan zapytania i rzeczywiste statystyki w planie. Pamiętaj o zapisaniu planu zapytania za każdym razem, aby można było je porównać i ustalić, który operator zapytania zużywa większość czasu procesora CPU. W szczególności można porównać liczbę wierszy (rzeczywista liczba wierszy) dla każdego operatora w czasie i zobaczyć, które operatory wykazują znaczny wzrost liczby wierszy (milion lub więcej). Zastąp <session_id> element wartością całkowitą znalezioną w poprzednim kroku 3.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Zatrzymaj element XEvent, jeśli został uruchomiony, lub wyłącz flagę śledzenia:

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

Możesz użyć uproszczonej infrastruktury profilowania statystyk wykonywania zapytań w wersji 3 , aby przechwycić plany zapytań na żywo z rzeczywistymi wartościami liczby wierszy. Ta infrastruktura profilowania umożliwia badanie operatorów zapytań w planie zapytań w czasie wykonywania i zrozumienie, gdzie większość czasu jest poświęcana w zapytaniu. Uproszczone profilowanie jest domyślnie włączone w programie SQL Server 2019.

Aby zidentyfikować powolne kroki w zapytaniu, wykonaj następujące kroki:

Uruchom zapytanie, którego dotyczy problem, nie kończące się na aplikacji.
Użyj polecenia podobnego do poniższego, aby zidentyfikować Session_id uruchomione nigdy nie kończące się zapytanie:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Uruchom następujące polecenie trzy lub cztery razy, aby sprawdzić plan zapytania i rzeczywiste statystyki w planie. Pamiętaj o zapisaniu planu zapytania za każdym razem, aby można było je porównać i ustalić, który operator zapytania zużywa większość czasu procesora CPU. Zastąp <session_id> element wartością całkowitą znalezioną w poprzednim kroku 3.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
W szczególności wybierz link XML w kolumnie query_plan . Gdy graficzny plan zapytania zostanie otwarty w nowym oknie, kliknij go prawym przyciskiem myszy i wybierz polecenie Zapisz plan wykonania jako.... Powtórz kroki, aby przechwycić trzy lub cztery migawki rozmieszczone na minutę, aby zapewnić wystarczające dane do analizy. W szczególności można porównać liczbę wierszy (rzeczywistą liczbę wierszy) dla każdego operatora w czasie i zobaczyć, które operatory wykazują znaczny wzrost liczby wierszy (milion lub więcej).
Uwaga 16.

Jeśli nie otrzymujesz żadnych danych wyjściowych z sys.dm_exec_query_statistics_xmlusługi , możesz sprawdzić, czy opcja LAST_QUERY_PLAN_STATS bazy danych została wyłączona, uruchamiając następujące polecenie:
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
Możesz włączyć ostatnie statystyki planu zapytania na poziomie bazy danych, uruchamiając polecenie ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

Metoda przeglądania zebranych planów

W tej sekcji przedstawiono sposób przeglądania zebranych danych. Będzie ona używać wielu planów zapytań XML (przy użyciu rozszerzenia *.sqlplan) zebranych w programie SQL Server 2016 SP1 i nowszych kompilacjach i wersjach.

Wykonaj następujące kroki, aby porównać plany wykonywania:

Otwórz wcześniej zapisany plik planu wykonywania zapytania (.sqlplan).
Kliknij prawym przyciskiem myszy pusty obszar planu wykonania i wybierz pozycję Porównaj showplan.
Wybierz drugi plik planu zapytania, który chcesz porównać.
Poszukaj grubych strzałek, które wskazują dużą liczbę wierszy przepływających między operatorami. Następnie wybierz operator przed strzałką lub po nim i porównaj liczbę rzeczywistych wierszy w dwóch planach.
Porównaj drugie i trzecie plany, aby sprawdzić, czy największy przepływ wierszy występuje w tych samych operatorach.

Oto przykład:

Rozwiązanie

Upewnij się, że statystyki są aktualizowane dla tabel używanych w zapytaniu.
Wyszukaj brakujące zalecenie dotyczące indeksu w planie zapytania i zastosuj dowolne.
Zastąp ponownie zapytanie, aby uprościć je:
- Użyj bardziej selektywnych WHERE predykatów, aby zmniejszyć ilość przetwarzanych danych z góry.
- Rozbić go od siebie.
- Wybierz niektóre części w tabelach tymczasowych i dołącz je później.
- Usuń TOPwartości , EXISTSi FAST (T-SQL) w zapytaniach uruchamianych przez bardzo długi czas ze względu na cel wiersza optymalizatora. Alternatywnie możesz użyć DISABLE_OPTIMIZER_ROWGOAL wskazówki. Aby uzyskać więcej informacji, zobacz Row Goals Gone Rogue.
- Unikaj używania typowych wyrażeń tabel (CTE) w takich przypadkach, jak łączą instrukcje w jednym dużym zapytaniu.
Spróbuj użyć wskazówek dotyczących zapytań , aby utworzyć lepszy plan:
- HASH JOIN lub MERGE JOIN wskazówka
- FORCE ORDER aluzja
- FORCESEEK aluzja
- RECOMPILE
- UŻYJ PLAN N'<xml_plan>' , jeśli masz szybki plan zapytania, który możesz wymusić
Użyj magazynu zapytań (QDS), aby wymusić dobry plan, jeśli taki plan istnieje i czy wersja programu SQL Server obsługuje magazyn zapytań.

Diagnozowanie oczekiwań lub wąskich gardeł

Ta sekcja jest zawarta tutaj jako odwołanie w przypadku, gdy problem nie jest długotrwałym zapytaniem kierującym procesorEM CPU. Można go użyć do rozwiązywania problemów z zapytaniami, które są długotrwałe z powodu oczekiwania.

Aby zoptymalizować zapytanie oczekujące na wąskie gardła, zidentyfikuj czas oczekiwania i miejsce wąskiego gardła (typ oczekiwania). Po potwierdzeniu typu oczekiwania zmniejsz czas oczekiwania lub całkowicie zlikwiduj oczekiwanie.

Aby obliczyć przybliżony czas oczekiwania, odejmij czas procesora CPU (czas procesu roboczego) od czasu, który upłynął w zapytaniu. Zazwyczaj czas procesora CPU to rzeczywisty czas wykonywania, a pozostała część okresu istnienia zapytania oczekuje.

Przykłady obliczania przybliżonego czasu trwania oczekiwania:

Czas upływu (ms)	Czas procesora CPU (ms)	Czas oczekiwania (ms)
3200	3000	200
7080	1000	6080

Identyfikowanie wąskiego gardła lub oczekiwania

Aby zidentyfikować historyczne długotrwałe zapytania (na przykład >20% ogólnego czasu oczekiwania, czyli czas oczekiwania), uruchom następujące zapytanie. To zapytanie używa statystyk wydajności dla buforowanych planów zapytań od początku programu 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

Aby zidentyfikować aktualnie wykonywane zapytania z oczekiwaniami dłuższymi niż 500 ms, uruchom następujące zapytanie:

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

Jeśli możesz zebrać plan zapytania, sprawdź wartości WaitStats z właściwości planu wykonywania w programie SSMS:
1. Uruchom zapytanie z włączonym uwzględnij rzeczywisty plan wykonania .
2. Kliknij prawym przyciskiem myszy operator najbardziej po lewej stronie na karcie Plan wykonania
3. Wybierz pozycję Właściwości , a następnie właściwość WaitStats .
4. Sprawdź wartości WaitTimeMs i WaitType.
Jeśli znasz scenariusze PSSDiag/SQLdiag lub SQL LogScout LightPerf/GeneralPerf, rozważ użycie jednej z nich do zbierania statystyk wydajności i identyfikowania oczekujących zapytań w wystąpieniu programu SQL Server. Zebrane pliki danych można zaimportować i przeanalizować dane wydajności za pomocą narzędzia SQL Nexus.

Odwołania pomagające wyeliminować lub zmniejszyć oczekiwania

Przyczyny i rozwiązania dla każdego typu oczekiwania różnią się. Nie ma jednej ogólnej metody rozpoznawania wszystkich typów oczekiwania. Poniżej przedstawiono artykuły umożliwiające rozwiązywanie typowych problemów z typem oczekiwania:

Opisy wielu typów oczekiwania i wskazywanych przez nie elementów można znaleźć w tabeli Typy oczekiwania.

Udostępnij za pośrednictwem