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:
- 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).
- Uruchom zapytania na dwóch serwerach.
- Porównaj czas (czas trwania) upłynął na dwóch serwerach dla każdego zapytania.
- Wybierz kilka zapytań do testowania z priorytetem umieszczonym w zapytaniach, które są następujące:
Analizowanie danych wydajności za pomocą usługi SQL Nexus.
- Zbierz dane PSSDiag/SQLdiag lub SQL LogScout dla zapytań na dwóch serwerach.
- 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ń czasu
ON
statystyki i ustawienia operacji we/ON
wy 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.
Uruchom zapytanie z włączonym planem dołączania rzeczywistego wykonania .
Wybierz operator z lewej strony z planu wykonywania.
W obszarze Właściwości rozwiń właściwość QueryTimeStats .
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
- 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.
- 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ń
Sprawdź ślady na obu serwerach, aby uzyskać następujące informacje:
- Jeśli na serwerze Server1 jest włączony jakikolwiek ślad, ale nie na serwerze Server2.
- Jeśli jakiekolwiek śledzenie jest włączone, wyłącz śledzenie i uruchom zapytanie ponownie na serwerze Server1.
- Jeśli zapytanie działa szybciej tym razem, włącz śledzenie wstecz, ale usuń z niego filtry tekstowe, jeśli istnieją.
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.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:
- Uruchom zapytanie z włączonym planem dołączania rzeczywistego wykonania .
- Kliknij prawym przyciskiem myszy operator najbardziej po lewej stronie na karcie Plan wykonywania
- Wybierz pozycję Właściwości , a następnie właściwość WaitStats .
- 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
Opinia
https://aka.ms/ContentUserFeedback.
Dostępne już wkrótce: W 2024 r. będziemy stopniowo wycofywać zgłoszenia z serwisu GitHub jako mechanizm przesyłania opinii na temat zawartości i zastępować go nowym systemem opinii. Aby uzyskać więcej informacji, sprawdź:Prześlij i wyświetl opinię dla