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

Dotyczy: SQL Server

Ten artykuł zawiera kroki rozwiązywania problemów z wydajnością, w przypadku których zapytanie działa wolniej na jednym serwerze niż na innym serwerze.

Symptomy

Załóżmy, że istnieją dwa serwery z zainstalowanymi SQL Server. Jedno z wystąpień SQL Server zawiera kopię bazy danych w drugim wystąpieniu SQL Server. Po uruchomieniu zapytania względem baz danych na obu serwerach zapytanie działa wolniej na jednym serwerze niż inne.

Poniższe kroki mogą pomóc w rozwiązaniu tego problemu.

Krok 1. Określanie, czy jest to typowy problem z wieloma zapytaniami

Użyj jednej z następujących dwóch metod, aby porównać wydajność co najmniej dwóch zapytań na dwóch serwerach:

• Ręcznie przetestuj zapytania na obu serwerach:

  1. Wybierz kilka zapytań do testowania z priorytetem umieszczonym w zapytaniach, które są następujące:
     • Znacznie szybciej na jednym serwerze niż na drugim.
     • Ważne dla użytkownika/aplikacji.
     • Często wykonywane lub zaprojektowane w celu odtworzenia problemu na żądanie.
     • Wystarczająco długo, aby przechwycić dane na nim (na przykład zamiast zapytania o długości 5 milisekund wybierz zapytanie 10-sekundowe).
  2. Uruchom zapytania na dwóch serwerach.
  3. Porównaj czas (czas trwania) upłynął na dwóch serwerach dla każdego zapytania.

• Analizowanie danych wydajności za pomocą usługi SQL Nexus.

  1. Zbierz dane PSSDiag/SQLdiag lub SQL LogScout dla zapytań na dwóch serwerach.
  2. Zaimportuj zebrane pliki danych za pomocą usługi SQL Nexus i porównaj zapytania z dwóch serwerów. Aby uzyskać więcej informacji, zobacz Porównanie wydajności między dwiema kolekcjami dzienników (na przykład Powolne i Szybkie).

Scenariusz 1. Tylko jedno zapytanie działa inaczej na dwóch serwerach

Jeśli tylko jedno zapytanie działa inaczej, problem jest bardziej prawdopodobny dla pojedynczego zapytania, a nie dla środowiska. W takim przypadku przejdź do kroku 2. Zbieranie danych i określanie typu problemu z wydajnością.

Scenariusz 2. Wiele zapytań działa inaczej na dwóch serwerach

Jeśli wiele zapytań działa wolniej na jednym serwerze niż inne, najbardziej prawdopodobną przyczyną są różnice w środowisku serwera lub danych. Przejdź do pozycji Diagnozowanie różnic w środowisku i sprawdź, czy porównanie między tymi dwoma serwerami jest prawidłowe.

Krok 2. Zbieranie danych i określanie typu problemu z wydajnością

Zbieranie czasu, czasu procesora CPU i odczytów logicznych

Aby zebrać czas i czas procesora CPU zapytania na obu serwerach, użyj jednej z następujących metod, które najlepiej pasują do Twojej sytuacji:

• 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ź last_elapsed_time i last_worker_time kolumn 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

  Jeśli avg_wait_time wartość ujemna jest wyświetlana, 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 ustawień czasuON statystyki i ustawienia operacji we/ONwy statystyki.

  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, czas, który upłynął, 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 wykonywania.

  1. Uruchom zapytanie z włączonym planem dołączania rzeczywistego wykonania .

  2. Wybierz operator z lewej strony z planu wykonywania.

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

  4. Sprawdź ElapsedTime i CpuTime.

     

Porównaj czas i czas procesora CPU zapytania, aby określić typ problemu dla obu serwerów.

Typ 1: powiązanie procesora 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 3000 milisekund (ms), a czas procesora CPU wynosi 2900 ms, oznacza to, że większość czasu, który upłynął, jest spędzana na procesorze CPU. Następnie możemy powiedzieć, że jest to zapytanie związane z procesorem CPU.

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

Czas upłynął (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 SQL Server. Mogą istnieć scenariusze, w których użycie procesora CPU pochodzi z innych źródeł: pętli while (w języku T-SQL lub innym kodzie, takim jak obiekty XProcs lub SQL CRL). Drugi przykład w tabeli ilustruje taki scenariusz, w którym większość procesora CPU nie pochodzi z operacji odczytu.

Uwaga

Jeśli czas procesora CPU jest dłuższy niż czas trwania, oznacza to, że wykonywane jest zapytanie równoległe; 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 czeka na wąskie gardło, jeśli upłynęło znacznie więcej czasu niż czas procesora CPU. Czas, który upłynął, obejmuje czas wykonywania zapytania na procesorze CPU (czas procesora CPU) oraz czas oczekiwania na zwolnienie zasobu (czas oczekiwania). Jeśli na przykład upłynął czas 2000 ms, a czas procesora CPU 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łynął (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
2000	300	28000
10080	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 wielu wątkom jednoczesnego uruchamiania części zapytania. W ciągu jednej sekundy czasu zegara zapytanie może używać ośmiu sekund czasu procesora CPU, wykonując osiem równoległych wątków. W związku z tym ustalenie powiązanego procesora CPU lub zapytania oczekującego na podstawie czasu i różnicy czasu procesora CPU staje się trudne. Jednak zgodnie z ogólną zasadą należy przestrzegać zasad wymienionych w dwóch powyższych sekcjach. Podsumowanie to:

• Jeśli upłynął czas znacznie większy niż czas procesora CPU, należy uznać go za kelnera.
• Jeśli czas procesora CPU jest znacznie większy niż czas, który upłynął, należy uznać go za moduł uruchamiający.

Przykłady zapytań równoległych:

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

Krok 3. Porównanie danych z obu serwerów, ustalenie scenariusza i rozwiązywanie problemu

Załóżmy, że istnieją dwie maszyny o nazwie Server1 i Server2. Zapytanie działa wolniej na serwerze Server1 niż na serwerze Server2. Porównaj czasy z obu serwerów, a następnie postępuj zgodnie z akcjami scenariusza, który najlepiej odpowiada Twoim z poniższych sekcji.

Scenariusz 1. Zapytanie na serwerze Server1 zużywa więcej czasu procesora CPU, a odczyty logiczne są wyższe na serwerze Server1 niż na serwerze Server2

Jeśli czas procesora CPU na serwerze Server1 jest znacznie większy niż w przypadku serwera Server2, a upłynął czas dokładnie odpowiada czasowi procesora CPU na obu serwerach, nie ma żadnych większych oczekiwań ani wąskich gardeł. Wzrost czasu procesora CPU na serwerze Server1 jest najprawdopodobniej spowodowany wzrostem liczby odczytów logicznych. Istotna zmiana w odczytach logicznych zazwyczaj wskazuje na różnicę w planach zapytań. Przykład:

Serwer	Czas upłynął (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
Serwer1	3100	3000	300000
Serwer 2	1100	1000	90200

Akcja: Sprawdzanie planów i środowisk wykonywania

1. Porównaj plany wykonywania zapytania na obu serwerach. W tym celu użyj jednej z dwóch metod:
   • Wizualnie porównaj plany wykonywania. Aby uzyskać więcej informacji, zobacz Wyświetlanie rzeczywistego planu wykonywania.
   • Zapisz plany wykonywania i porównaj je przy użyciu funkcji porównywania planów SQL Server Management Studio.
2. Porównanie środowisk. Różne środowiska mogą prowadzić do różnic w planie zapytań lub bezpośrednich różnic w użyciu procesora CPU. Środowiska obejmują wersje serwera, ustawienia konfiguracji bazy danych lub serwera, flagi śledzenia, liczbę procesorów CPU lub szybkość zegara oraz maszynę wirtualną i maszynę fizyczną. Aby uzyskać szczegółowe informacje, zobacz Diagnose query plan differences (Diagnozowanie różnic w planie zapytań ).

Scenariusz 2. Zapytanie jest kelnerem na serwerze Server1, ale nie na serwerze Server2

Jeśli czas procesora CPU dla zapytania na obu serwerach jest podobny, ale czas, który upłynął na serwerze Server1, jest znacznie większy niż w przypadku serwera Server2, zapytanie na serwerze Server1 poświęca znacznie więcej czasu na oczekiwanie na wąskie gardło. Przykład:

Serwer	Czas upłynął (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
Serwer1	4500	1000	90200
Serwer 2	1100	1000	90200

• Czas oczekiwania na serwerze Server1: 4500 - 1000 = 3500 ms
• Czas oczekiwania na serwerze Server2: 1100 -1000 = 100 ms

Akcja: sprawdzanie typów oczekiwania na serwerze Server1

Identyfikowanie i eliminowanie wąskiego gardła na serwerze Server1. Przykłady oczekiwań to blokowanie (oczekiwanie na blokadę), oczekiwania na zatrzaśnięcie, oczekiwanie we/wy dysku, oczekiwanie na sieć i oczekiwanie na pamięć. Aby rozwiązać typowe problemy z wąskim gardłem, przejdź do sekcji Diagnozowanie oczekiwania lub wąskich gardeł.

Scenariusz 3. Zapytania na obu serwerach to kelnerzy, ale typy lub godziny oczekiwania są różne

Przykład:

Serwer	Czas upłynął (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
Serwer1	8000	1000	90200
Serwer 2	3000	1000	90200

• Czas oczekiwania na serwerze Server1: 8000 -1000 = 7000 ms
• Czas oczekiwania na serwerze Server2: 3000 -1000 = 2000 ms

W takim przypadku czas procesora CPU jest podobny na obu serwerach, co oznacza, że plany zapytań są prawdopodobnie takie same. Zapytania będą wykonywane jednakowo na obu serwerach, jeśli nie będą czekać na wąskie gardła. Tak więc różnice czasu trwania pochodzą z różnych ilości czasu oczekiwania. Na przykład zapytanie czeka na blokady na serwerze Server1 przez 7000 ms podczas oczekiwania na operacje we/wy na serwerze Server2 przez 2000 ms.

Akcja: sprawdzanie typów oczekiwania na obu serwerach

Zajmij się każdym wąskim gardłem oczekiwania indywidualnie na każdym serwerze i przyspiesz wykonywanie na obu serwerach. Rozwiązywanie tego problemu jest pracochłonne, ponieważ należy wyeliminować wąskie gardła na obu serwerach i sprawić, że wydajność będzie porównywalna. Aby rozwiązać typowe problemy z wąskim gardłem, przejdź do sekcji Diagnozowanie oczekiwania lub wąskich gardeł.

Scenariusz 4. Zapytanie na serwerze Server1 używa więcej czasu procesora NIŻ na serwerze Server2, ale odczyty logiczne są bliskie

Przykład:

Serwer	Czas upłynął (ms)	Czas procesora CPU (ms)	Odczyty (logiczne)
Serwer1	3000	3000	90200
Serwer 2	1000	1000	90200

Jeśli dane są zgodne z następującymi warunkami:

• Czas procesora CPU na serwerze Server1 jest znacznie większy niż w przypadku serwera Server2.
• Upłynął czas zgodny z czasem procesora CPU na każdym serwerze, co oznacza brak oczekiwania.
• Odczyty logiczne, zazwyczaj najwyższy sterownik czasu procesora CPU, są podobne na obu serwerach.

Następnie dodatkowy czas procesora CPU pochodzi z niektórych innych działań związanych z procesorem CPU. Ten scenariusz jest najrzadszy ze wszystkich scenariuszy.

Przyczyny: śledzenie, funkcje zdefiniowane przez użytkownika i integracja środowiska CLR

Ten problem może być spowodowany przez:

• Śledzenie XEvents/SQL Server, szczególnie w przypadku filtrowania kolumn tekstowych (nazwa bazy danych, nazwa logowania, tekst zapytania itd.). Jeśli śledzenie jest włączone na jednym serwerze, ale nie na drugim, może to być przyczyną różnicy.
• Funkcje zdefiniowane przez użytkownika (UDF) lub inny kod T-SQL, który wykonuje operacje związane z procesorem CPU. Zazwyczaj przyczyną są inne warunki na serwerze Server1 i Server2, takie jak rozmiar danych, szybkość zegara procesora CPU lub plan zasilania.
• SQL Server integrację clr lub rozszerzone procedury składowane (XPs), które mogą napędzać procesor CPU, ale nie wykonują odczytów logicznych. Różnice w bibliotekach DLL mogą prowadzić do różnych czasów procesora CPU.
• Różnica w SQL Server funkcjonalności powiązanej z procesorem CPU (np. kod manipulowania ciągami).

Akcja: sprawdzanie śladów i zapytań

1. Sprawdź ślady na obu serwerach, aby uzyskać następujące informacje:

   1. Jeśli na serwerze Server1 jest włączony jakikolwiek ślad, ale nie na serwerze Server2.
   2. Jeśli jakiekolwiek śledzenie jest włączone, wyłącz śledzenie i uruchom zapytanie ponownie na serwerze Server1.
   3. Jeśli zapytanie działa szybciej tym razem, włącz śledzenie wstecz, ale usuń z niego filtry tekstowe, jeśli istnieją.

2. Sprawdź, czy zapytanie korzysta z funkcji zdefiniowanych przez użytkownika, które wykonują manipulacje ciągami, czy wykonuje szeroko zakrojone przetwarzanie kolumn danych na SELECT liście.

3. Sprawdź, czy zapytanie zawiera pętle, rekursje funkcji lub zagnieżdżenia.

Diagnozowanie różnic w środowisku

Sprawdź następujące pytania i określ, czy porównanie obu serwerów jest prawidłowe.

• Czy dwa wystąpienia SQL Server są w tej samej wersji czy kompilacji?

  Jeśli nie, mogą istnieć pewne poprawki, które spowodowały różnice. Uruchom następujące zapytanie, aby uzyskać informacje o wersji na obu serwerach:

  SELECT @@VERSION
  

• Czy ilość pamięci fizycznej jest podobna na obu serwerach?

  Jeśli jeden serwer ma 64 GB pamięci, podczas gdy drugi ma 256 GB pamięci, byłaby to znacząca różnica. Dzięki większej ilości pamięci dostępnej do buforowania stron danych/indeksów i planów zapytań zapytanie można zoptymalizować inaczej w zależności od dostępności zasobów sprzętowych.

• Czy konfiguracje sprzętu związane z procesorem CPU są podobne na obu serwerach? Przykład:

  • Liczba procesorów różni się w zależności od maszyny (24 procesory CPU na jednej maszynie w porównaniu do 96 procesorów z drugiej).

  • Plany zasilania — zrównoważona i wysoka wydajność.

  • Maszyna wirtualna (VM) a maszyna fizyczna (bez systemu operacyjnego).

  • Hyper-V a VMware — różnica w konfiguracji.

  • Różnica prędkości zegara (niższa prędkość zegara w porównaniu do wyższej prędkości zegara). Na przykład 2 GHz w porównaniu do 3,5 GHz może mieć znaczenie. Aby uzyskać szybkość zegara na serwerze, uruchom następujące polecenie programu PowerShell:

    Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
    

  Użyj jednego z następujących dwóch sposobów, aby przetestować szybkość procesora CPU serwerów. Jeśli nie dają porównywalnych wyników, problem wykracza poza SQL Server. Może to być różnica w planie zasilania, mniejsza liczba procesorów CPU, problem z oprogramowaniem maszyny wirtualnej lub różnica prędkości zegara.

  • Uruchom następujący skrypt programu PowerShell na obu serwerach i porównaj dane wyjściowe.

    $bf = [System.DateTime]::Now
    for ($i = 0; $i -le 20000000; $i++) {}
    $af = [System.DateTime]::Now
    Write-Host ($af - $bf).Milliseconds " milliseconds"
    Write-Host ($af - $bf).Seconds " Seconds"
    

  • Uruchom następujący kod Języka Transact-SQL na obu serwerach i porównaj dane wyjściowe.

    SET NOCOUNT ON 
    DECLARE @spins INT = 0
    DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT
    
    WHILE (@spins < 20000000)
    BEGIN
       SET @spins = @spins +1
    END
    
    SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate())
    
    SELECT @spins Spins, @time_millisecond Time_ms,  @spins / @time_millisecond Spins_Per_ms
    

Diagnozowanie oczekiwania lub wąskich gardeł

Aby zoptymalizować zapytanie oczekujące na wąskie gardła, określ, jak długo trwa oczekiwanie i gdzie znajduje się wąskie gardło (typ oczekiwania). Po potwierdzeniu typu oczekiwania zmniejsz czas oczekiwania lub całkowicie wyeliminuj oczekiwanie.

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

Przykłady sposobu obliczania przybliżanego czasu oczekiwania:

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

Identyfikowanie wąskiego gardła lub oczekiwanie

• Aby zidentyfikować historyczne długo oczekujące zapytania (na przykład >20% ogólnego czasu oczekiwania to czas oczekiwania), uruchom następujące zapytanie. To zapytanie używa statystyk wydajności dla planów zapytań buforowanych od początku 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ź właściwości WaitStats z planu wykonywania w programie SSMS:

  1. Uruchom zapytanie z włączonym planem dołączania rzeczywistego wykonania .
  2. Kliknij prawym przyciskiem myszy operator najbardziej po lewej stronie na karcie Plan wykonywania
  3. Wybierz pozycję Właściwości , a następnie właściwość WaitStats .
  4. Sprawdź elementy 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 SQL Server. Możesz zaimportować zebrane pliki danych i przeanalizować dane wydajności za pomocą usługi SQL Nexus.

Odwołania ułatwiające wyeliminowanie lub zmniejszenie liczby oczekujących

Przyczyny i rozwiązania dla każdego typu oczekiwania różnią się. Nie ma jednej ogólnej metody rozwiązywania 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 bazy danych Azure SQL
• Rozwiązywanie problemów z niską wydajnością SQL Server spowodowaną przez problemy z operacjami we/wy (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
• Rozwiązywanie rywalizacji PAGELATCH_EX wstawiania ostatniej strony w SQL Server
• Pamięć udziela wyjaśnień i rozwiązań (RESOURCE_SEMAPHORE)
• Rozwiązywanie problemów z powolnymi zapytaniami wynikającymi z ASYNC_NETWORK_IO typu oczekiwania
• Rozwiązywanie problemów z wysokim typem oczekiwania HADR_SYNC_COMMIT przy użyciu zawsze włączonych grup dostępności
• Jak to działa: CMEMTHREAD i debugowanie ich
• Tworzenie równoległości oczekiwania actionable (CXPACKET i CXCONSUMER)
• OCZEKIWANIE NA PULĘ THREADPOOL

Opisy wielu typów oczekiwania i ich wskazania można znaleźć w tabeli Typy oczekiwania.

Diagnozowanie różnic w planie zapytań

Oto niektóre typowe przyczyny różnic w planach zapytań:

• Różnice rozmiaru danych lub wartości danych

  Czy ta sama baza danych jest używana na obu serwerach — przy użyciu tej samej kopii zapasowej bazy danych? Czy dane zostały zmodyfikowane na jednym serwerze w porównaniu z drugim? Różnice w danych mogą prowadzić do różnych planów zapytań. Na przykład łączenie tabeli T1 (1000 wierszy) z tabelą T2 (2 000 000 wierszy) różni się od łączenia tabeli T1 (100 wierszy) z tabelą T2 (2 000 000 wierszy). Typ i szybkość JOIN operacji mogą się znacznie różnić.

• Różnice w statystykach

  Czy statystyki zostały zaktualizowane w jednej bazie danych, a nie w drugiej? Czy statystyki zostały zaktualizowane o inną częstotliwość próbkowania (na przykład 30% w porównaniu ze 100% pełnym skanowaniem)? Upewnij się, że statystyki są aktualizowane po obu stronach przy użyciu tej samej częstotliwości próbkowania.

• Różnice na poziomie zgodności bazy danych

  Sprawdź, czy poziomy zgodności baz danych różnią się między tymi dwoma serwerami. Aby uzyskać poziom zgodności bazy danych, uruchom następujące zapytanie:

  SELECT name, compatibility_level
  FROM sys.databases
  WHERE name = '<YourDatabase>'
  

• Różnice między wersją/kompilacją serwera

  Czy wersje lub kompilacje SQL Server różnią się między dwoma serwerami? Na przykład czy jeden serwer SQL Server wersji 2014, a drugi SQL Server wersji 2016? Mogą wystąpić zmiany produktu, które mogą prowadzić do zmian w sposobie wybierania planu zapytania. Upewnij się, że porównasz tę samą wersję i kompilację SQL Server.

  SELECT ServerProperty('ProductVersion')
  

• Różnice w wersji narzędzia do szacowania kardynalności (CE)

  Sprawdź, czy starsza wersja narzędzia do szacowania kardynalności jest aktywowana na poziomie bazy danych. Aby uzyskać więcej informacji na temat CE, zobacz Cardinality Estimation (SQL Server) (Szacowanie kardynalności (SQL Server)).

  SELECT name, value, is_value_default
  FROM sys.database_scoped_configurations
  WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'
  

• Poprawki optymalizatora włączone/wyłączone

  Jeśli poprawki optymalizatora zapytań są włączone na jednym serwerze, ale wyłączone na drugim, można wygenerować różne plany zapytań. Aby uzyskać więcej informacji, zobacz SQL Server model obsługi śledzenia poprawek poprawki optymalizatora zapytań 4199.

  Aby uzyskać stan poprawek optymalizatora zapytań, uruchom następujące zapytanie:

  -- Check at server level for TF 4199
  DBCC TRACESTATUS (-1)
  -- Check at database level
  USE <YourDatabase>
  SELECT name, value, is_value_default 
  FROM sys.database_scoped_configurations
  WHERE name = 'QUERY_OPTIMIZER_HOTFIXES'
  

• Różnice flag śledzenia

  Niektóre flagi śledzenia wpływają na wybór planu zapytania. Sprawdź, czy na jednym serwerze są włączone flagi śledzenia, które nie są włączone na drugim. Uruchom następujące zapytanie na obu serwerach i porównaj wyniki:

  -- Check at server level for trace flags
  DBCC TRACESTATUS (-1)
  

• Różnice sprzętowe (liczba procesorów CPU, rozmiar pamięci)

  Aby uzyskać informacje o sprzęcie, uruchom następujące zapytanie:

  SELECT cpu_count, physical_memory_kb/1024/1024 PhysicalMemory_GB 
  FROM sys.dm_os_sys_info
  

• Różnice sprzętowe w zależności od optymalizatora zapytań

  OptimizerHardwareDependentProperties Sprawdź plan zapytania i sprawdź, czy różnice sprzętowe są uważane za istotne w przypadku różnych planów.

  WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
  SELECT
    txt.text,
    t.OptHardw.value('@EstimatedAvailableMemoryGrant', 'INT') AS EstimatedAvailableMemoryGrant , 
    t.OptHardw.value('@EstimatedPagesCached', 'INT') AS EstimatedPagesCached, 
    t.OptHardw.value('@EstimatedAvailableDegreeOfParallelism', 'INT') AS EstimatedAvailDegreeOfParallelism,
    t.OptHardw.value('@MaxCompileMemory', 'INT') AS MaxCompileMemory
  FROM sys.dm_exec_cached_plans AS cp
  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
  CROSS APPLY qp.query_plan.nodes('//OptimizerHardwareDependentProperties') AS t(OptHardw)
  CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) txt
  WHERE text Like '%<Part of Your Query>%'
  

• Przekroczenie limitu czasu optymalizatora

  Czy występuje problem z przekroczeniem limitu czasu optymalizatora ? Optymalizator zapytań może przestać oceniać opcje planu, jeśli wykonywane zapytanie jest zbyt złożone. Po zatrzymaniu plan jest wybierany z najniższym kosztem dostępnym w danym momencie. Może to prowadzić do tego, co wydaje się arbitralnym wyborem planu na jednym serwerze, a innym.

• OPCJE ZESTAWU

  Niektóre opcje SET mają wpływ na plan, na przykład SET ARITHABORT. Aby uzyskać więcej informacji, zobacz OPCJE ZESTAWU.

• Różnice w wskazówkach dotyczących zapytań

  Czy jedno zapytanie używa wskazówek dotyczących zapytań , a drugie nie? Sprawdź tekst zapytania ręcznie, aby ustalić obecność wskazówek zapytania.

• Plany wrażliwe na parametry (problem z wąchaniem parametrów)

  Czy testujesz zapytanie z dokładnie tymi samymi wartościami parametrów? Jeśli nie, możesz tam zacząć. Czy plan został skompilowany wcześniej na jednym serwerze na podstawie innej wartości parametru? Przetestuj te dwa zapytania przy użyciu wskazówki dotyczącej zapytania RECOMPILE, aby upewnić się, że nie ma ponownego użycia planu. Aby uzyskać więcej informacji, zobacz Badanie i rozwiązywanie problemów z parametrami.

• Różne opcje bazy danych/ustawienia konfiguracji o określonym zakresie

  Czy te same opcje bazy danych lub ustawienia konfiguracji o zakresie są używane na obu serwerach? Niektóre opcje bazy danych mogą mieć wpływ na opcje planu. Na przykład zgodność bazy danych, starsza wersja CE i domyślne CE oraz wąchanie parametrów. Uruchom następujące zapytanie z jednego serwera, aby porównać opcje bazy danych używane na dwóch serwerach:

  -- On Server1 add a linked server to Server2 
  EXEC master.dbo.sp_addlinkedserver @server = N'Server2', @srvproduct=N'SQL Server'
  
  -- Run a join between the two servers to compare settings side by side
  SELECT 
     s1.name AS srv1_config_name, 
     s2.name AS srv2_config_name,
     s1.value_in_use AS srv1_value_in_use, 
     s2.value_in_use AS srv2_value_in_use, 
     Variance = CASE WHEN ISNULL(s1.value_in_use, '##') != ISNULL(s2.value_in_use,'##') THEN 'Different' ELSE '' END
  FROM sys.configurations s1 
  FULL OUTER JOIN [server2].master.sys.configurations s2 ON s1.name = s2.name
  
  
  SELECT 
     s1.name AS srv1_config_name,
     s2.name AS srv2_config_name,
     s1.value srv1_value_in_use,
     s2.value srv2_value_in_use,
     s1.is_value_default,
     s2.is_value_default,
     Variance = CASE WHEN ISNULL(s1.value, '##') != ISNULL(s2.value, '##') THEN 'Different' ELSE '' END
  FROM sys.database_scoped_configurations s1
  FULL OUTER JOIN [server2].master.sys.database_scoped_configurations s2 ON s1.name = s2.name
  

• Przewodniki planów

  Czy dla zapytań na jednym serwerze są używane jakiekolwiek przewodniki planów, ale nie na drugim? Uruchom następujące zapytanie, aby ustalić różnice:

  SELECT * FROM sys.plan_guides