Rozwiązywanie problemów z powolnym uruchamianiem zapytań w programie SQL Server

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 243589

Wprowadzenie

W tym artykule opisano sposób obsługi problemu z wydajnością, który może występować w aplikacjach bazy danych podczas korzystania z programu SQL Server: niska wydajność określonego zapytania lub grupy zapytań. Poniższa metodologia pomoże Ci zawęzić przyczynę problemu z powolnymi zapytaniami i kierować Cię do rozwiązania.

Znajdowanie wolnych zapytań

Aby ustalić, że masz problemy z wydajnością zapytań w wystąpieniu programu SQL Server, zacznij od zbadania zapytań według czasu wykonywania (czasu, który upłynął). Sprawdź, czy czas przekracza ustawiony próg (w milisekundach) na podstawie ustalonego punktu odniesienia wydajności. Na przykład w środowisku testowania przeciążeniowego być może ustalono próg obciążenia, który nie będzie dłuższy niż 300 ms, i można użyć tego progu. Następnie można zidentyfikować wszystkie zapytania, które przekraczają ten próg, koncentrując się na poszczególnych zapytaniach i wstępnie ustalonym czasie trwania punktu odniesienia wydajności. Ostatecznie użytkownicy biznesowi dbają o ogólny czas trwania zapytań bazy danych; dlatego głównym celem jest czas trwania wykonywania. Inne metryki, takie jak czas procesora CPU i odczyty logiczne, są zbierane w celu ułatwienia zawężenia badania.

• W przypadku aktualnie wykonywanych instrukcji sprawdź kolumny total_elapsed_time i cpu_time w sys.dm_exec_requests. Uruchom następujące zapytanie, aby pobrać dane:

  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;
  

• W przypadku poprzednich wykonań zapytania sprawdź kolumny last_elapsed_time i last_worker_time w sys.dm_exec_query_stats. Uruchom następujące zapytanie, aby pobrać dane:

  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
  

  Uwaga 16.

  Jeśli avg_wait_time zostanie wyświetlona wartość ujemna, jest to zapytanie równoległe.

• Jeśli możesz wykonać zapytanie na żądanie w programie SQL Server Management Studio (SSMS) lub Azure Data Studio, uruchom je przy użyciu funkcji SET STATISTICS TIME ON i SET STATISTICS IOON.

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

  Następnie w obszarze Komunikaty zobaczysz czas procesora CPU, upłynął czas i odczyty logiczne w następujący sposób:

    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.
  

• Jeśli możesz zebrać plan zapytania, sprawdź dane we właściwościach planu wykonania.

  1. Uruchom zapytanie z włączonym uwzględnij rzeczywisty plan wykonania.

  2. Wybierz operator najwięcej po lewej stronie z planu wykonania.

  3. W obszarze Właściwości rozwiń właściwość QueryTimeStats .

  4. Sprawdź czas, który upłynął i czas procesora CPUTime.

     

Uruchamianie a oczekiwanie: dlaczego zapytania są powolne?

Jeśli znajdziesz zapytania, które przekraczają wstępnie zdefiniowany próg, sprawdź, dlaczego mogą być powolne. Przyczyną problemów z wydajnością można pogrupować w dwie kategorie, uruchomioną lub oczekującą:

• OCZEKIWANIE: Zapytania mogą być powolne, ponieważ długo czekają na wąskie gardło. Zobacz szczegółową listę wąskich gardeł w typach oczekiwania.

• URUCHOMIONE: Zapytania mogą być powolne, ponieważ są uruchamiane (wykonywane) przez długi czas. Innymi słowy, te zapytania aktywnie korzystają z zasobów procesora CPU.

Zapytanie może być uruchomione przez pewien czas i oczekiwać jakiś czas w okresie istnienia (czas trwania). Jednak twoim celem jest określenie, która kategoria jest dominującą kategorią, która przyczynia się do długiego czasu, który upłynął. Dlatego pierwszym zadaniem jest ustalenie, do której kategorii należy to zapytanie. Jest to proste: jeśli zapytanie nie jest uruchomione, czeka. W idealnym przypadku zapytanie spędza większość czasu, który upłynął w stanie uruchomienia, i bardzo mało czasu na oczekiwanie na zasoby. Ponadto w najlepszym scenariuszu zapytanie jest uruchamiane w ramach lub poniżej wstępnie określonego punktu odniesienia. Porównaj czas, który upłynął i czas procesora CPU zapytania, aby określić typ problemu.

Typ 1: powiązany z procesorem CPU (moduł uruchamiający)

Jeśli czas procesora CPU jest bliski, równy lub wyższy niż upłynął czas, możesz traktować go jako zapytanie powiązane z procesorem CPU. Jeśli na przykład upłynął czas to 3000 milisekund (ms), a czas procesora WYNOSI 2900 ms, oznacza to, że większość czasu, który upłynął, jest poświęcana na procesor CPU. Następnie możemy powiedzieć, że jest to zapytanie powiązane z procesorem CPU.

Przykłady uruchamiania (powiązanych z procesorem CPU) zapytań:

Czas upływu (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
3200	3000	300000
1080	1000	20

Odczyty logiczne — odczytywanie stron danych/indeksów w pamięci podręcznej — są najczęściej sterownikami wykorzystania procesora CPU w programie SQL Server. Mogą istnieć scenariusze, w których użycie procesora CPU pochodzi z innych źródeł: pętla czasowa (w języku T-SQL lub innym kodzie, np. XProcs lub obiektach listy CRL SQL). Drugi przykład w tabeli ilustruje taki scenariusz, w którym większość procesora CPU nie pochodzi z operacji odczytu.

Uwaga 16.

Jeśli czas procesora CPU jest dłuższy niż czas trwania, oznacza to wykonanie zapytania równoległego; wiele wątków używa procesora CPU w tym samym czasie. Aby uzyskać więcej informacji, zobacz Zapytania równoległe — moduł uruchamiający lub kelner.

Typ 2. Oczekiwanie na wąskie gardło (kelner)

Zapytanie oczekuje na wąskie gardło, jeśli czas, który upłynął, jest znacznie większy niż czas procesora CPU. Czas, który upłynął, obejmuje czas wykonywania zapytania na procesorze CPU (czas procesora) i czas oczekiwania na zwolnienie zasobu (czas oczekiwania). Jeśli na przykład upłynął czas to 2000 ms, a czas procesora WYNOSI 300 ms, czas oczekiwania wynosi 1700 ms (2000 – 300 = 1700). Aby uzyskać więcej informacji, zobacz Typy oczekiwania.

Przykłady oczekujących zapytań:

Czas upływu (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
2000	300	28000
10000	700	80000

Zapytania równoległe — moduł uruchamiający lub kelner

Zapytania równoległe mogą używać więcej czasu procesora CPU niż całkowity czas trwania. Celem równoległości jest umożliwienie jednoczesnego uruchamiania części zapytania przez wiele wątków. W jednej sekundzie zegara zapytanie może używać ośmiu sekund czasu procesora, wykonując osiem równoległych wątków. W związku z tym trudno jest określić związane z procesorem CPU lub oczekujące zapytanie na podstawie czasu, który upłynął i różnica czasu procesora CPU. Jednak zgodnie z ogólną zasadą przestrzegaj zasad wymienionych w dwóch powyższych sekcjach. Podsumowanie to:

• Jeśli czas, który upłynął, jest znacznie większy niż czas procesora CPU, rozważ to kelnera.
• Jeśli czas procesora CPU jest znacznie większy niż czas, który upłynął, rozważ jego moduł uruchamiający.

Przykłady zapytań równoległych:

Czas upływu (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
1200	8100	850000
3080	12300	1500000

Wizualna reprezentacja metodologii wysokiego poziomu

Diagnozowanie i rozwiązywanie problemów z oczekującymi zapytaniami

Jeśli ustalono, że zapytania interesujące są kelnerami, następnym krokiem jest skupienie się na rozwiązywaniu problemów z wąskim gardłem. W przeciwnym razie przejdź do kroku 4. Diagnozowanie i rozwiązywanie uruchomionych zapytań.

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:

• Omówienie i rozwiązywanie problemów z blokowaniem (LCK_M_*)
• Omówienie i rozwiązywanie problemów z blokowaniem usługi Azure SQL Database
• Rozwiązywanie problemów z niską wydajnością programu SQL Server spowodowanych problemami we/wy (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
• Rozwiązywanie problemu ze wstawianiem ostatniej strony PAGELATCH_EX w programie SQL Server
• Pamięć udziela wyjaśnień i rozwiązań (RESOURCE_SEMAPHORE)
• Rozwiązywanie problemów z powolnymi zapytaniami, które wynikają z typu oczekiwania ASYNC_NETWORK_IO
• Rozwiązywanie problemów z wysokim typem oczekiwania HADR_SYNC_COMMIT z zawsze włączonymi grupami dostępności
• Jak to działa: CMEMTHREAD i debugowanie
• Wykonywanie równoległości czeka na działanie (CXPACKET i CXCONSUMER)
• OCZEKIWANIE NA PULĘ WĄTKÓW

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

Diagnozowanie problemów z uruchomionymi zapytaniami i rozwiązywanie ich

Jeśli czas procesora CPU (procesu roboczego) jest bardzo zbliżony do ogólnego czasu trwania, zapytanie spędza większość swojego okresu istnienia. Zazwyczaj gdy aparat programu SQL Server napędza wysokie użycie procesora CPU, wysokie użycie procesora CPU pochodzi z zapytań, które napędzają dużą liczbę odczytów logicznych (najczęstszą przyczyną).

Aby zidentyfikować zapytania, które są obecnie odpowiedzialne za działanie wysokiego użycia procesora CPU, uruchom następującą instrukcję:

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

Jeśli zapytania nie napędzają procesora CPU w tej chwili, możesz uruchomić następującą instrukcję, aby wyszukać historyczne zapytania związane z procesorem 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

Typowe metody rozwiązywania problemów z długotrwałymi zapytaniami powiązanych z procesorem CPU

• Badanie planu zapytania zapytania
• Aktualizacja statystyk
• Zidentyfikuj i zastosuj brakujące indeksy. Aby uzyskać więcej instrukcji dotyczących identyfikowania brakujących indeksów, zobacz Dostrajanie indeksów nieklastrowanych z brakującymi sugestiami indeksu
• Przeprojektuj lub ponownie napisz zapytania
• Zidentyfikuj i rozwiąż problemy dotyczące planów z uwzględnieniem parametrów
• Identyfikowanie i rozwiązywanie problemów z możliwościami SARG
• Identyfikowanie i rozwiązywanie problemów z celem wiersza polegających na tym, że długotrwałe zagnieżdżone pętle mogą być spowodowane przez TOP, EXISTS, IN, FAST, SET ROWCOUNT, OPTION (FAST N). Aby uzyskać więcej informacji, zobacz Nieautoryzowane cele wierszy i Ulepszenia showplanu — Row Goal EstimateRowsWithoutRowGoal
• Ocenianie i rozwiązywanie problemów z szacowania kardynalnością. Aby uzyskać więcej informacji, zobacz Zmniejszenie wydajności zapytań po uaktualnieniu z programu SQL Server 2012 lub starszego do wersji 2014 lub nowszej
• Identyfikowanie i rozwiązywanie problemów, które nie wydają się nigdy kompletne, zobacz Rozwiązywanie problemów z zapytaniami, które wydają się nigdy nie kończyć się programem SQL Server
• Identyfikowanie i rozwiązywanie wolnych zapytań, których dotyczy limit czasu optymalizatora
• Identyfikowanie problemów z wysoką wydajnością procesora CPU. Aby uzyskać więcej informacji, zobacz Rozwiązywanie problemów z wysokim użyciem procesora CPU w programie SQL Server
• Rozwiązywanie problemów z zapytaniem, które wykazuje znaczącą różnicę wydajności między dwoma serwerami
• Zwiększenie zasobów obliczeniowych w systemie (procesorów CPU)
• Rozwiązywanie problemów z wydajnością aktualizacji przy użyciu wąskich i szerokich planów

Zalecane zasoby

• Wykrywalne typy wąskich gardeł wydajności zapytań w programie SQL Server i usłudze Azure SQL Managed Instance
• Narzędzia do monitorowania i dostrajania wydajności
• Opcje autodostrajania w programie SQL Server
• Architektura indeksów i wytyczne dotyczące projektowania
• Rozwiązywanie problemów z błędami limitu czasu zapytania
• Rozwiązywanie problemów z wysokim użyciem procesora CPU w programie SQL Server
• Zmniejszona wydajność zapytań po uaktualnieniu z programu SQL Server 2012 lub starszego do wersji 2014 lub nowszej