Rozwiązywanie problemów z zapytaniami, które wydają się być uruchamiane bez końca w programie SQL Server

Ten artykuł zawiera wskazówki dotyczące rozwiązywania problemów, w których zapytanie programu Microsoft SQL Server zajmuje zbyt dużo czasu (godziny lub dni).

Objawy

Ten artykuł koncentruje się na zapytaniach, które wydają się być uruchamiane lub kompilowane bez końca. Oznacza to, że ich użycie procesora nadal rośnie. Ten artykuł nie ma zastosowania do zapytań, które są blokowane lub oczekują na zasób, który nigdy nie został wydany. W takich przypadkach użycie procesora CPU pozostaje stałe lub zmienia się tylko nieznacznie.

Ważne

Jeśli zapytanie pozostanie w stanie kontynuować pracę, może zostać ostatecznie zakończone. Ten proces może potrwać zaledwie kilka sekund lub kilka dni. W niektórych sytuacjach zapytanie może być naprawdę nieskończone, na przykład gdy pętla WHILE nie kończy się. Termin "niekończące się" jest używany tutaj do opisania wrażenia, że zapytanie się nie kończy.

Przyczyna

Typowe przyczyny długotrwałych (niekończących się) zapytań obejmują:

• Sprzężenia zagnieżdżonej pętli (NL) w bardzo dużych tabelach: Ze względu na charakter sprzężeń NL, zapytanie, które łączy tabele z dużą liczbą wierszy, może działać przez długi czas. Aby uzyskać więcej informacji, zobacz Joins (Sprzężenia).
  • Jednym z przykładów sprzężenia NL jest użycie elementu TOP, FAST lub EXISTS. Nawet jeśli sprzężenia skrótu lub scalania mogą być szybsze, optymalizator nie może użyć operatora z powodu celu wiersza.
  • Innym przykładem sprzężenia NL jest zastosowanie predykatu sprzężenia z warunkiem nierówności w zapytaniach. Na przykład SELECT .. FROM tab1 AS a JOIN tab 2 AS b ON a.id > b.id. Optymalizator nie może tutaj używać Merge ani Hash joins.
• Nieaktualne statystyki: Zapytania, które wybierają plan na podstawie nieaktualnych statystyk, mogą być nieoptymalne i trwają długo.
• Pętle nieskończone: Zapytania T-SQL korzystające z pętli WHILE mogą być niepoprawnie zapisywane. Wynikowy kod nigdy nie opuszcza pętli i działa bez końca. Te zapytania są naprawdę niekończące się. Uruchamiają się, dopóki nie zostaną zabici ręcznie.
• Złożone zapytania, które mają wiele sprzężeń i dużych tabel: Zapytania obejmujące wiele tabel sprzężonych zwykle mają złożone plany zapytań, które mogą zająć dużo czasu. Ten scenariusz jest typowy w przypadku zapytań analitycznych, które nie filtrują wierszy i obejmują dużą liczbę tabel.
• Brakujące indeksy: Zapytania mogą działać znacznie szybciej, jeśli odpowiednie indeksy są używane w tabelach. Indeksy umożliwiają wybór podzestawu danych w celu zapewnienia szybszego dostępu.

Rozwiązanie

Krok 1. Odnajdywanie niekończących się zapytań

Wyszukaj niekończące się zapytanie, które jest uruchomione w systemie. Musisz określić, czy zapytanie ma długi czas wykonywania, długi czas oczekiwania (zablokowane w wąskim gardle) lub długi czas kompilacji.

1.1 Uruchamianie diagnostyki

Uruchom następujące zapytanie diagnostyczne w instancji programu SQL Server, w której aktywne jest zapytanie niekończące się.

DECLARE @cntr INT = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    CAST(r.cpu_time / (1000 * 60.0) AS DECIMAL(10,2)) AS cpu_time_minutes,
                    CAST(r.total_elapsed_time / (1000 * 60.0) AS DECIMAL(10,2)) AS elapsed_minutes,
                    r.logical_reads,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.reads,
                    r.writes,
                    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

1.2 Sprawdzanie danych wyjściowych

Istnieje kilka scenariuszy, które mogą powodować uruchamianie zapytania przez długi czas: długie wykonywanie, długie oczekiwanie i długa kompilacja. Aby uzyskać więcej informacji o tym, dlaczego zapytanie może działać wolno, zobacz Running vs. Waiting: why are slow query? (Uruchamianie i oczekiwanie: dlaczego zapytania są powolne?

Długi czas wykonywania

Kroki rozwiązywania problemów opisane w tym artykule mają zastosowanie w przypadku otrzymania danych wyjściowych podobnych do poniższych, w których czas procesora CPU wzrasta proporcjonalnie do czasu, który upłynął bez znaczących czasów oczekiwania.

session_id	stan	czas_procesora_minuty	czas_upływu_minuty	logical_reads	czas_oczekiwania_minuty	wait_type
56	uruchomiono	64.40	23.50	0	0.00	NULL

Zapytanie jest stale uruchomione, jeśli ma:

• Zwiększający się czas procesora
• Stan running lub runnable
• Minimalny lub zerowy czas oczekiwania
• Brak wait_type

W takiej sytuacji zapytanie odczytuje wiersze, dołącza, przetwarza wyniki, oblicza lub formatuje. Te działania są akcjami powiązanymi z procesorem CPU.

Uwaga 16.

Zmiany w logical_reads nie są istotne w tym przypadku, ponieważ niektóre żądania T-SQL obciążające CPU, takie jak wykonywanie obliczeń lub pętla WHILE, mogą w ogóle nie wykonywać żadnych operacji logicznego odczytu.

Jeśli wolne zapytanie spełnia te kryteria, skup się na zmniejszeniu jego czasu działania. Zazwyczaj zmniejszenie środowiska uruchomieniowego polega na zmniejszeniu liczby wierszy, które zapytanie musi przetwarzać przez cały czas, stosując indeksy, ponownie zapisując zapytanie lub aktualizując statystyki. Aby uzyskać więcej informacji, zobacz sekcję Rozwiązanie .

Długi czas oczekiwania

Ten artykuł nie ma zastosowania do przypadków długiego oczekiwania. W scenariuszu oczekiwania możesz otrzymać dane wyjściowe podobne do następującego przykładu, w którym użycie procesora CPU nie zmienia się lub zmienia się nieznacznie, ponieważ sesja czeka na zasób:

session_id	stan	czas_procesora_minuty	czas_upływu_minuty	logical_reads	czas_oczekiwania_minuty	wait_type
56	zawieszony	0.03	4.20	50	4.10	LCK_M_U

Typ oczekiwania wskazuje, że sesja czeka na zasób. Długi czas realizacji i długi czas oczekiwania wskazują, że sesja przez większość czasu czeka na ten zasób. Krótki czas pracy procesora wskazuje, że niewiele czasu zostało faktycznie spędzone na przetwarzanie zapytania.

Aby rozwiązać problemy z zapytaniami, które są długie z powodu oczekiwania, zobacz Rozwiązywanie problemów z powolnymi zapytaniami w programie SQL Server.

Długi czas kompilacji

W rzadkich przypadkach można zauważyć, że użycie procesora CPU stale wzrasta w czasie, ale nie jest napędzane przez wykonywanie zapytania. Zamiast tego przyczyną może być zbyt długa kompilacja (analizowanie i kompilowanie zapytania). W takich przypadkach sprawdź kolumnę danych wyjściowych transaction_name pod kątem wartości sqlsource_transform. Ta nazwa transakcji wskazuje kompilację.

Krok 2. Ręczne zbieranie dzienników diagnostycznych

Po ustaleniu, że w systemie istnieje niekończące się zapytanie, możesz zebrać dane planu zapytania w celu dalszego rozwiązywania problemów. Aby zebrać dane, użyj jednej z następujących metod, w zależności od używanej wersji programu SQL Server.

SQL Server 2008 — SQL Server 2014 (starsze niż SP2)

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

1. Przechwyć szacowany kod XML planu wykonywania zapytania.

2. Zapoznaj się z planem zapytania, aby dowiedzieć się, czy dane pokazują oczywiste wskazania tego, co powoduje spowolnienie. Przykłady typowych wskazówek obejmują:

   • Skanowanie tabeli lub indeksu (zwróć uwagę na szacowane wiersze)
   • Zagnieżdżone pętle napędzane przez ogromny zewnętrzny zbiór danych tabeli
   • Zagnieżdżone pętle, które mają dużą gałąź w wewnętrznej części pętli
   • Bufory tabel
   • Funkcje na SELECT liście, które zajmują dużo czasu na przetworzenie każdego wiersza

3. Jeśli zapytanie jest uruchamiane szybciej w dowolnym momencie, możesz przechwycić "szybkie" uruchomienia (rzeczywisty plan wykonywania XML), aby porównać wyniki.

SQL Server 2014 (nowsze niż SP2) i SQL Server 2016 (starsze niż SP1)

Uproszczona infrastruktura profilowania zapytań została wprowadzona w programie Microsoft SQL Server 2014. Umożliwia ona przechwytywanie rzeczywistych statystyk podczas uruchamiania wolnych zapytań. Ta funkcja diagnostyczna umożliwia badanie operatorów zapytań w planie zapytań podczas wykonywania i zrozumienie, gdzie zapytanie zużywa najwięcej czasu.

Aby zidentyfikować powolne kroki w zapytaniu nu, używając infrastruktury profilowania uproszczonej statystyki wykonania zapytań w wersji 1, wykonaj następujące kroki:

1. 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
   

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

3. Uruchom następujące polecenie wiele razy, co około minutę, aby sprawdzić statystyki wykonywania środowiska uruchomieniowego 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)
   

4. Przechwyć trzy lub cztery migawki, co minutę, aby uzyskać wystarczające dane do analizy. W szczególności, można porównać wartości row_count dla każdego operatora w czasie, aby określić, które kwoty wskazują na znaczący wzrost liczby wierszy (o milion lub więcej).

5. 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
   

6. Znajdź ten sam węzeł w szacowanych planach zapytań przy użyciu identyfikatora węzła, który ma najwyższą liczbę wierszy, zgodnie z zapytaniem uruchomionym w kroku 3. Ten krok pomaga określić, który operator w planie jest główną przyczyną długiego czasu wykonywania.

7. Zatrzymaj element XEvent, uruchamiając następujące polecenie:

   ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP
   

SQL Server 2016 (nowsze niż SP1) i SQL Server 2017

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

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

1. Aby włączyć uproszczoną infrastrukturę, 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
     

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

3. Aby zidentyfikować Session_id niekończące się zapytanie, które jest uruchomione, uruchom polecenie podobne do następującego:

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

4. Aby zbadać plan zapytania i rzeczywiste statystyki w planie, uruchom następujące polecenie trzy lub cztery razy, jedną minutę od siebie. Upewnij się, że plan zapytania jest zapisywany za każdym razem, aby można było porównać plany i dowiedzieć się, które operatory zapytań zużywają większość czasu procesora CPU. W szczególności można porównać liczbę wierszy (rzeczywistą liczbę wierszy) dla każdego operatora w czasie, aby zobaczyć, które operatory wykazują znaczny wzrost liczby wierszy (o milion lub więcej). Zastąp <session_id> element wartością całkowitą znalezioną w poprzednim kroku.

   SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
   

5. 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)
   

SQL Server 2019 i nowsze wersje

Możesz użyć uproszczonej infrastruktury profilowania statystyk wykonywania zapytań w wersji 3 , aby przechwycić plany zapytań na żywo, które zawierają rzeczywiste liczby wierszy. Ta infrastruktura profilowania umożliwia badanie operatorów zapytań w planie zapytania podczas jego wykonywania i zrozumienie, gdzie większość czasu jest rzeczywiście wykorzystywana w zapytaniu. Uproszczone profilowanie jest domyślnie włączone w programie Microsoft SQL Server 2019.

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

1. Uruchom zapytanie, którego dotyczy problem, nie kończące się na aplikacji.

2. Aby zidentyfikować Session_id niekończące się zapytanie, które jest uruchomione, uruchom polecenie podobne do następującego:

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

3. Uruchom następujące polecenie, aby sprawdzić plan zapytania i rzeczywiste statystyki w planie. Pamiętaj, aby zapisać plan zapytania za każdym razem, aby można było je porównać i dowiedzieć się, który operator zapytań zużywa większość czasu procesora CPU. Zastąp <session_id> element wartością całkowitą znalezioną w poprzednim kroku.

   SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
   

4. Wybierz link XML w kolumnie query_plan . Po uruchomieniu graficznego planu zapytania w nowym oknie kliknij go prawym przyciskiem myszy, a następnie wybierz polecenie Zapisz plan wykonania jako.... Powtórz te kroki, aby przechwycić trzy lub cztery migawki, co minutę, aby uzyskać wystarczające dane do analizy. W szczególności można porównać liczbę wierszy (rzeczywistą liczbę wierszy) dla każdego operatora w czasie, aby dowiedzieć się, które operatory wykazują znaczny wzrost liczby wierszy (o milion lub więcej).

   Uwaga 16.

   Jeśli nie otrzymujesz żadnych danych wyjściowych z sys.dm_exec_query_statistics_xml, możesz sprawdzić, czy opcja bazy danych LAST_QUERY_PLAN_STATS jest 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.

Używanie funkcji SQL LogScout do przechwytywania niekończących się zapytań

Funkcję SQL LogScout można użyć do przechwytywania dzienników, gdy jest uruchomione niekończące się zapytanie. Użyj scenariusza niekończącego się zapytania za pomocą następującego polecenia:

.\SQL_LogScout.ps1 -Scenario "NeverEndingQuery" -ServerName "SQLInstance"

Uwaga 16.

Ten proces przechwytywania dzienników wymaga, aby długie zapytanie zużywało co najmniej 60 sekund czasu procesora.

Funkcja SQL LogScout przechwytuje co najmniej trzy plany zapytań dla każdego zapytania zużywających wysokie użycie procesora CPU. Można znaleźć nazwy plików, które są podobne do servername_datetime_NeverEnding_statistics_QueryPlansXml_Startup_sessionId_#.sqlplan. Te pliki można użyć w następnym kroku podczas przeglądania planów, aby zidentyfikować przyczynę długiego wykonywania zapytań.

Krok 3. Przeglądanie zebranych planów

W tej sekcji omówiono sposób przeglądania zebranych danych. Używa wielu planów zapytań XML (przy użyciu rozszerzenia .sqlplan), które są zbierane w programie Microsoft SQL Server 2016 SP1 i nowszych kompilacjach i wersjach.

Porównaj plany wykonywania , wykonując następujące kroki:

1. Otwórz wcześniej zapisany plik planu wykonywania zapytania (.sqlplan).

2. Kliknij prawym przyciskiem myszy na pustym obszarze planu wykonania, a następnie wybierz opcję Porównaj Showplan.

3. Wybierz drugi plik planu zapytania, który chcesz porównać.

4. 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.

5. Porównaj drugie i trzecie plany, aby dowiedzieć się, czy największy przepływ wierszy występuje w tych samych operatorach.

   Przykład:

   

Krok 4. Rozwiązanie

1. Upewnij się, że statystyki są aktualizowane dla tabel używanych w zapytaniu.

2. Poszukaj brakujących zaleceń dotyczących indeksu w planie zapytania i zastosuj wszystkie, które znajdziesz.

3. Uprość zapytanie:

   • 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ń TOP, EXISTS i FAST (T-SQL) w zapytaniach, które wykonują się długo z powodu operacji docelowego wiersza w optymalizatorze.
     • Alternatywnie użyj DISABLE_OPTIMIZER_ROWGOALwskazówki. Aby uzyskać więcej informacji, zobacz Row Goals Gone Rogue.
   • Unikaj używania typowych wyrażeń tabel (CTE) w takich przypadkach, ponieważ łączą instrukcje w jednym dużym zapytaniu.

4. 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
   • USE PLAN N'<xml_plan>' (jeśli masz szybki plan zapytania, który możesz wymusić)

5. Użyj magazynu zapytań (QDS), aby wymusić dobry plan, jeśli taki plan istnieje i czy wersja programu SQL Server obsługuje magazyn zapytań.

Treści powiązane

• Rozwiązywanie problemów z zapytaniem, które pokazuje znaczącą różnicę wydajności między dwoma serwerami