Udostępnij za pośrednictwem


Przewodnik po weryfikacji i optymalizacji po migracji

Dotyczy:programu SQL Server

Krok po migracji programu SQL Server ma kluczowe znaczenie dla uzgadniania wszelkich dokładności i kompletności danych oraz wykrywania problemów z wydajnością obciążenia.

Typowe scenariusze wydajności

Poniżej przedstawiono niektóre typowe scenariusze wydajności napotkane po migracji na platformę programu SQL Server i sposoby ich rozwiązywania. Obejmują one scenariusze specyficzne dla migracji z SQL Server do SQL Server (starsze wersje do nowszych wersji) oraz migracji z platform obcych (np. Oracle, DB2, MySQL i Sybase) do SQL Server.

Regresje zapytań spowodowane zmianą wersji narzędzia do szacowania kardynalności (CE)

Dotyczy: programu SQL Server do migracji programu SQL Server.

Podczas migracji ze starszej wersji programu SQL Server do programu SQL Server 2014 (12.x) lub nowszej wersji oraz uaktualniania poziomu zgodności bazy danych do najnowszej dostępnej wersji obciążenie może być narażone na ryzyko regresji wydajności.

Jest to spowodowane tym, że począwszy od programu SQL Server 2014 (12.x), wszystkie zmiany optymalizatora zapytań są powiązane z najnowszym poziomem zgodności bazy danych , więc plany nie są zmieniane bezpośrednio przy uaktualnieniu, lecz gdy użytkownik zmienia opcję bazy danych COMPATIBILITY_LEVEL na najnowszą. Ta funkcja w połączeniu z Query Store zapewnia wysoki poziom kontroli nad wydajnością zapytań w procesie uaktualniania.

Aby uzyskać więcej informacji na temat zmian optymalizatora zapytań wprowadzonych w programie SQL Server 2014 (12.x), zobacz Optymalizowanie planów zapytań przy użyciu narzędzia do szacowania kardynalności programu SQL Server 2014.

Aby uzyskać więcej informacji na temat szacowania kardynalności (CE) w SQL Server, zobacz .

Kroki rozwiązywania problemów

Zmień poziom zgodności bazy danych na wersję źródłową i postępuj zgodnie z zalecanym przepływem pracy uaktualniania, jak pokazano na poniższej ilustracji:

Diagram przedstawiający zalecany przepływ pracy aktualizacji.

Aby uzyskać więcej informacji na temat tego artykułu, zobacz Zachowaj stabilność wydajności podczas uaktualniania do nowszego programu SQL Server.

Wrażliwość na wąchanie parametrów

Dotyczy: obcych platform (takich jak Oracle, DB2, MySQL i Sybase) przy migracji do programu SQL Server.

Nota

W przypadku migracji programu SQL Server do programu SQL Server, jeśli ten problem istniał w źródłowym programie SQL Server, migracja do nowszej wersji programu SQL Server as-is nie rozwiąże tego scenariusza.

Program SQL Server kompiluje plany zapytań dotyczące procedur składowanych przy użyciu wąchania parametrów wejściowych podczas pierwszej kompilacji, generując sparametryzowany i wielokrotnego użytku plan zoptymalizowany pod kątem tej dystrybucji danych wejściowych. Nawet jeśli nie są to procedury składowane, większość instrukcji generujących trywialne plany jest sparametryzowana. Po pierwszym zbuforowaniu planu wszystkie kolejne wykonania są przypisywane do wcześniej zbuforowanego planu.

Potencjalny problem występuje, gdy pierwsza kompilacja nie używa najbardziej typowych zestawów parametrów dla zwykłego obciążenia. W przypadku różnych parametrów ten sam plan wykonywania staje się nieefektywny. Aby uzyskać więcej informacji na temat tego artykułu, zobacz czułość parametru.

Kroki rozwiązywania problemów

  1. Użyj wskazówki RECOMPILE. Plan jest obliczany za każdym razem z uwzględnieniem każdej wartości parametru.
  2. Zastąp ponownie procedurę składowaną, aby użyć opcji (OPTIMIZE FOR(<input parameter> = <value>)). Zdecyduj, która wartość ma być używana w większości odpowiednich obciążeń, tworząc i utrzymując jeden plan, który staje się wydajny dla sparametryzowanej wartości.
  3. Ponownie zapisz procedurę składowaną przy użyciu zmiennej lokalnej wewnątrz procedury. Teraz optymalizator używa wektora gęstości do szacowania, co powoduje ten sam plan niezależnie od wartości parametru.
  4. Zastąp ponownie procedurę składowaną, aby użyć opcji (OPTIMIZE FOR UNKNOWN). Taki sam efekt jak w przypadku używania techniki zmiennej lokalnej.
  5. Przepisz zapytanie, aby użyć wskazówki DISABLE_PARAMETER_SNIFFING. Taki sam efekt jak użycie techniki zmiennej lokalnej przez całkowite wyłączenie ustalania parametrów, chyba że używa się OPTION(RECOMPILE), WITH RECOMPILE lub OPTIMIZE FOR <value>.

Napiwek

Użyj funkcji Analiza planu programu Management Studio, aby szybko określić, czy jest to problem. Aby uzyskać więcej informacji, zobacz Nowości w SSMS: Rozwiązywanie problemów z wydajnością zapytań stało się łatwiejsze!.

Brakujące indeksy

Dotyczy: zagranicznych platform (takich jak Oracle, DB2, MySQL i Sybase) oraz migracji z SQL Server na SQL Server.

Nieprawidłowe lub brakujące indeksy powodują dodatkowe operacje we/wy, co prowadzi do marnotrawienia dodatkowej pamięci i zasobów procesora CPU. Może to być spowodowane zmianą profilu obciążenia, takiego jak używanie różnych predykatów, unieważnienie istniejącego projektu indeksu. Dowody na słabą strategię indeksowania lub zmiany w profilu obciążenia obejmują:

  • Poszukaj zduplikowanych, nadmiarowych, rzadko używanych i całkowicie nieużywanych indeksów.
  • Szczególna ostrożność w przypadku nieużywanych indeksów z aktualizacjami.

Kroki rozwiązywania problemów

  1. Użyj graficznego planu wykonywania dla wszystkich brakujących odwołań do indeksu.
  2. Sugestie dotyczące indeksowania generowane przez Doradcę optymalizacji aparatu bazodanowego .
  3. Użyj sys.dm_db_missing_index_details.
  4. Użyj istniejących skryptów, które mogą korzystać z istniejących dynamicznych widoków zarządzania, aby zapewnić wgląd w brakujące, zduplikowane, nadmiarowe, rzadko używane oraz całkowicie nieużywane indeksy, a także sprawdzić, czy jakiekolwiek odwołanie do indeksu jest sugerowane lub zakodowane na stałe w istniejących procedurach i funkcjach w bazie danych.

Napiwek

Przykłady takich wstępnie istniejących skryptów obejmują tworzenie indeksu i informacje o indeksie .

Brak możliwości używania predykatów do filtrowania danych

Dotyczy: obcych platform (takich jak Oracle, DB2, MySQL i Sybase) oraz migracji z SQL Server do SQL Server.

Notatka

W przypadku migracji programu SQL Server do programu SQL Server, jeśli ten problem istniał w źródłowym programie SQL Server, migracja do nowszej wersji programu SQL Server as-is nie rozwiąże tego scenariusza.

Optymalizator zapytań programu SQL Server może uwzględniać tylko informacje znane w czasie kompilacji. Jeśli obciążenie opiera się na predykatach, które mogą być znane tylko w czasie wykonywania, zwiększa się potencjał słabego wyboru planu. W przypadku planu o lepszej jakości predykaty muszą być SARGable, lub Search Argumentable.

Niektóre przykłady predykatów niezwiązanych z SARGable:

  • Niejawne konwersje danych, takie jak varchar do nvarchar, lub int do varchar. Szukaj ostrzeżeń środowiska uruchomieniowego CONVERT_IMPLICIT w rzeczywistych planach wykonawczych. Konwertowanie z jednego typu na inny może również spowodować utratę precyzji.
  • Złożone niezdefiniowane wyrażenia, takie jak WHERE UnitPrice + 1 < 3.975, ale nie WHERE UnitPrice < 320 * 200 * 32.
  • Wyrażenia korzystające z funkcji, takich jak WHERE ABS(ProductID) = 771 lub WHERE UPPER(LastName) = 'Smith'
  • Ciągi z wiodącym symbolem wieloznacznym, takimi jak WHERE LastName LIKE '%Smith', ale nie WHERE LastName LIKE 'Smith%'.

Kroki rozwiązywania problemów

  1. Zawsze deklaruj zmienne/parametry zgodnie z zamierzonym celem typy danych.

    Może to obejmować porównanie dowolnej konstrukcji kodu zdefiniowanej przez użytkownika przechowywanej w bazie danych (na przykład procedur składowanych, funkcji zdefiniowanych przez użytkownika lub widoków) z tabelami systemowymi, które przechowują informacje o typach danych używanych w tabelach bazowych (takich jak sys.columns (Transact-SQL)).

  2. Jeśli nie można przejść przez cały kod do poprzedniego punktu, w tym samym celu zmień typ danych w tabeli tak, aby był zgodny z dowolną deklaracją zmiennej/parametru.

  3. Wyjmij użyteczność następujących konstrukcji:

    • Funkcje używane jako predykaty;
    • Wyszukiwanie symboli wieloznacznych;
    • Wyrażenia złożone oparte na danych kolumnowych — oceniają konieczność utworzenia utrwanych kolumn obliczeniowych, które można indeksować;

Notatka

Wszystkie te kroki można wykonać programowo.

Korzystanie z funkcji zwracających tabele (wielowyrażeniowe kontra jednowierszowe)

Dotyczy: obcych platform (takich jak Oracle, DB2, MySQL i Sybase) oraz migracji z SQL Server do SQL Server.

Notatka

W przypadku migracji programu SQL Server do programu SQL Server, jeśli ten problem istniał w źródłowym programie SQL Server, migracja do nowszej wersji programu SQL Server as-is nie rozwiąże tego scenariusza.

Funkcja table Valued zwraca typ danych tabeli, który może być alternatywą dla widoków. Chociaż widoki są ograniczone do pojedynczej instrukcji SELECT, funkcje zdefiniowane przez użytkownika mogą zawierać dodatkowe instrukcje, które zezwalają na więcej logiki niż jest to możliwe w widokach.

Ważny

Ponieważ tabela wyjściowa funkcji z wieloma instrukcjami (MSTVF) nie jest tworzona w czasie kompilacji, optymalizator zapytań programu SQL Server opiera się na heurystyce, a nie na rzeczywistych statystykach, aby określić oszacowania wierszy. Nawet jeśli indeksy są dodawane do tabel podstawowych, nie pomoże to. W przypadku MSTVFs program SQL Server używa stałego oszacowania 1 dla liczby wierszy, które mają zostać zwrócone przez MSTVF. Począwszy od SQL Server 2014 (12.x) to stałe oszacowanie wynosi 100 wierszy.

Kroki rozwiązywania problemów

  1. Jeśli MSTVF jest tylko pojedynczą instrukcją, przekonwertuj na funkcję tabelową wbudowaną.

    CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
    RETURNS @tblAddress TABLE
    ([Address] VARCHAR(60) NOT NULL)
    AS
    BEGIN
      INSERT INTO @tblAddress ([Address])
      SELECT TOP 1 [AddressLine1]
      FROM [Person].[Address]
      WHERE  AddressID = @ID
      ORDER BY [ModifiedDate] DESC
    RETURN
    END
    

    Przykład formatu wbudowanego zostanie wyświetlony w następnej kolejności.

    CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int)
    RETURNS TABLE
    AS
    RETURN (
      SELECT TOP 1 [AddressLine1] AS [Address]
      FROM [Person].[Address]
      WHERE  AddressID = @ID
      ORDER BY [ModifiedDate] DESC
    )
    
  2. Jeśli bardziej złożone, rozważ użycie wyników pośrednich przechowywanych w tabelach Memory-Optimized lub tabelach tymczasowych.