Udostępnij za pośrednictwem

Zmniejszona wydajność zapytań po uaktualnieniu z programu SQL Server 2012 lub starszego do wersji 2014 lub nowszej

  • Artykuł

Po uaktualnieniu programu SQL Server z wersji 2012 lub starszej do wersji 2014 lub nowszej może wystąpić następujący problem: większość oryginalnych zapytań działa dobrze, ale kilka zapytań działa wolniej niż w poprzedniej wersji. Chociaż istnieje wiele możliwych przyczyn i czynników przyczyniających się, stosunkowo częstą przyczyną jest zmiana modelu szacowania kardynalności (CE) po uaktualnieniu. Wprowadzono znaczące zmiany w modelach CE począwszy od programu SQL Server 2014.

Ten artykuł zawiera kroki rozwiązywania problemów i rozwiązania problemów z wydajnością zapytań występujących podczas korzystania z domyślnego ce, ale nie występują podczas korzystania ze starszej wersji CE.

Uwaga 16.

Jeśli wszystkie zapytania działają wolniej po uaktualnieniu, kroki rozwiązywania problemów wprowadzone w tym artykule prawdopodobnie nie mają zastosowania do Twojej sytuacji.

Rozwiązywanie problemów: zidentyfikuj, czy zmiany CE są problemem, i dowiedz się, dlaczego

Krok 1. Określenie, czy jest używany domyślny ce

  1. Wybierz zapytanie uruchamiane wolniej po uaktualnieniu.
  2. Uruchom zapytanie i zbierz plan wykonania.
  3. W okno Właściwości planu wykonania sprawdź wartość CardinalityEstimationModelVersion. Znajdź wersję modelu CE z okno Właściwości planu wykonania.
  4. Wartość 70 wskazuje starsze ce, a wartość 120 lub wyższa wskazuje użycie domyślnego CE.

Jeśli jest używana starsza wersja CE, zmiany CE nie są przyczyną problemu z wydajnością. Jeśli jest używany domyślny ce, przejdź do następnego kroku.

Krok 2. Określenie, czy optymalizator zapytań może wygenerować lepszy plan przy użyciu starszej wersji CE

Uruchom zapytanie ze starszą wersją CE. Jeśli działa lepiej niż przy użyciu domyślnego CE, przejdź do następnego kroku. Jeśli wydajność nie poprawi się, zmiany CE nie są przyczyną.

Krok 3. Dowiedz się, dlaczego zapytanie działa lepiej dzięki starszej wersji CE

Przetestuj różne wskazówki dotyczące zapytań związane z CE dla zapytania. W przypadku programu SQL Server 2014 użyj odpowiednich flag śledzenia 4137, 9472 i 4139 , aby przetestować zapytanie. Ustal, które wskazówki lub flagi śledzenia pozytywnie wpływają na wydajność na podstawie tych testów.

Rozwiązanie

Aby rozwiązać ten problem, wypróbuj jedną z poniższych metod:

  • Zoptymalizuj zapytanie.

    Co zrozumiałe, nie zawsze jest możliwe ponowne napisanie zapytań, ale zwłaszcza gdy istnieje tylko kilka zapytań, które można przepisać, takie podejście powinno być pierwszym wyborem. Optymalnie napisane zapytania działają lepiej niezależnie od wersji CE.

  • Użyj wskazówek dotyczących zapytań zidentyfikowanych w kroku 3.

    Takie ukierunkowane podejście umożliwia innym obciążeniom korzystanie z domyślnych założeń i ulepszeń CE. Ponadto jest to bardziej niezawodna opcja niż tworzenie przewodnika po planie. I nie wymaga magazynu zapytań (QDS), w przeciwieństwie do wymuszania planu (najbardziej niezawodnej opcji).

  • Wymuś dobry plan.

    Jest to korzystna opcja i może służyć do określania celu określonych zapytań. Wymuszanie planu można wykonać przy użyciu przewodnika planu lub QDS. Usługa QDS jest ogólnie łatwiejsza w użyciu.

  • Użyj konfiguracji o zakresie bazy danych, aby wymusić użycie starszej wersji CE.

    Jest to mniej preferowane podejście, ponieważ jest to ustawienie całej bazy danych i dotyczy wszystkich zapytań względem tej bazy danych. Mimo to czasami konieczne jest, gdy ukierunkowane podejście nie jest możliwe. Z pewnością jest to najłatwiejsza opcja implementacji.

  • Użyj flagi śledzenia 9841, aby wymusić globalne wymuszanie starszej wersji CE. W tym celu użyj polecenia DBCC TRACEON lub ustaw flagę śledzenia jako parametr start-up.

    Jest to podejście najmniej ukierunkowane i powinno być używane tylko jako tymczasowe środki zaradcze, gdy nie można zastosować żadnej z innych opcji.

Opcje włączania starszej wersji CE

Poziom zapytania: użyj opcji Query Hint lub QUERYTRACEON

  • W przypadku programu SQL Server 2016 SP1 i nowszych wersji użyj wskazówki FORCE_LEGACY_CARDINALITY_ESTIMATION dotyczącej zapytania, na przykład:

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

  • Włącz flagę śledzenia 9481, aby wymusić starszy plan CE. Oto przykład:

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (QUERYTRACEON 9481)

Poziom bazy danych: ustawianie konfiguracji o określonym zakresie lub poziomu zgodności

  • W przypadku programu SQL Server 2016 i nowszych wersji zmień konfigurację o określonym zakresie bazy danych:

      --Force a specific database to use legacy CE
  ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

  -- Validate what databases use legacy CE
  SELECT name, value
      FROM sys.database_scoped_configurations 
  WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

  • Zmień poziom zgodności bazy danych. Jest to jedyna opcja na poziomie bazy danych dostępna dla programu SQL Server 2014. Należy pamiętać, że ta zmiana ma wpływ nie tylko na CE. Aby określić wpływ zmian na poziomie zgodności, przejdź do artykułu ALTER DATABASE compatibility level (Transact-SQL) (Poziom zgodności ALTER DATABASE) i sprawdź w nim tabele "Różnice".

    ALTER DATABASE <YourDatabase>
SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level

Uwaga 16.

Ta zmiana będzie mieć wpływ na wszystkie zapytania wykonywane w kontekście bazy danych, dla której konfiguracja zostanie zmieniona, chyba że zostanie użyta zastępowana flaga śledzenia lub wskazówka zapytania. Zapytania, które działają lepiej z powodu domyślnego ce, mogą ulec pogorszeniu.

Poziom serwera: użyj flagi śledzenia

Użyj flagi śledzenia 9481, aby wymusić użycie starszej wersji ce dla całego serwera:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Uwaga 16.

Ta zmiana wpłynie na wszystkie zapytania wykonywane w kontekście wystąpienia programu SQL Server, chyba że zostanie użyta zastępowana flaga śledzenia lub wskazówka zapytania. Zapytania, które działają lepiej z powodu domyślnego ce, mogą ulec pogorszeniu.

Często zadawane pytania

W przypadku wstępnie istniejących baz danych działających na niższych poziomach zgodności zalecany przepływ pracy uaktualniania procesora zapytań do wyższego poziomu zgodności jest szczegółowy w temacie Zmienianie trybu zgodności bazy danych i Używanie scenariuszy użycia magazynu zapytań i magazynu zapytań. Metodologia wprowadzona w artykule dotyczy przejścia do wersji 130 lub nowszej dla programu SQL Server i usługi Azure SQL Database.

Q2: Nie mam czasu, aby przetestować zmiany CE. Co mogę zrobić w tym przypadku?

W przypadku istniejących aplikacji i obciążeń nie zalecamy przejścia do domyślnego modelu CE do czasu przeprowadzenia wystarczającego testowania regresji. Jeśli nadal masz wątpliwości, zalecamy uaktualnienie programu SQL Server i przejście do najnowszego dostępnego poziomu zgodności. Jako środek ostrożności włącz również flagę śledzenia 9481 dla programu SQL Server 2014 lub skonfiguruj konfigurację ON w zakresie bazy danych LEGACY_CARDINALITY_ESTIMATION dla programu SQL Server 2016 i nowszych wersji, dopóki nie będzie można przetestować.

Q3: Czy istnieją jakieś wady korzystania ze starszej wersji CE na stałe?

Przyszłe ulepszenia i poprawki związane z narzędziem do szacowania kardynalności są skoncentrowane wokół nowszych wersji. Wersja 70 jest akceptowalnym stanem pośrednim. Jednak po starannym przetestowaniu zalecamy ostatecznie przejście do nowszej wersji CE, aby skorzystać z najnowszych poprawek CE. Istnieje duże prawdopodobieństwo zmian planu zapytań podczas przechodzenia ze starszej wersji CE, więc przetestuj przed wprowadzeniem zmian w systemach produkcyjnych. Zmiany mogą poprawić wydajność zapytań w wielu przypadkach, ale w niektórych przypadkach wydajność zapytań może ulec pogorszeniu.

Ważne

Domyślny CE jest główną ścieżką kodową, która będzie otrzymywać przyszłe inwestycje i głębsze pokrycie testowe w dłuższej perspektywie, więc nie planuje się korzystać ze starszej wersji CE na czas nieokreślony.

Q4: Mam tysiące baz danych i nie chcę ręcznie włączać LEGACY_CARDINALITY_ESTIMATION dla każdego. Czy istnieje alternatywna metoda?

W przypadku programu SQL Server 2014 włącz flagę śledzenia 9481, aby używać starszej wersji CE dla wszystkich baz danych niezależnie od poziomu zgodności. W przypadku programu SQL Server 2016 i nowszych wersji wykonaj następujące zapytanie, aby iterować bazy danych. To ustawienie zostanie włączone nawet wtedy, gdy baza danych zostanie przywrócona lub dołączona na innym serwerze.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

W przypadku usługi Azure SQL Database możesz utworzyć bilet pomocy technicznej, aby ta flaga śledzenia została włączona na poziomie subskrypcji, ale nie na poziomie serwera.

Pytanie5: Czy działa ze starszym CE uniemożliwi mi uzyskanie dostępu do nowych funkcji?

Nawet w przypadku włączenia LEGACY_CARDINALITY_ESTIMATION nadal będziesz mieć dostęp do najnowszych funkcji zawartych w wersji programu SQL Server i skojarzonego poziomu zgodności bazy danych. Na przykład baza danych z włączoną LEGACY_CARDINALITY_ESTIMATION uruchomiona na poziomie zgodności bazy danych 140 w programie SQL Server 2017 nadal może korzystać z rodziny funkcji adaptacyjnego przetwarzania zapytań.

Q6: Kiedy starsza wersja CE wyjdzie z wsparcia?

Nie mamy planów, aby w tym momencie przestać wspierać dziedzictwo CE. Jednak przyszłe ulepszenia i poprawki związane z kardynalnością są skoncentrowane na nowszych wersjach CE.

Q7: Mam tylko kilka zapytań regresji z domyślnym CE, ale większość wydajności zapytań jest taka sama, a nawet ulepszona. Co mam robić?

Bardziej szczegółową alternatywą dla flagi śledzenia o zakresie serwera 9481 lub LEGACY_CARDINALITY_ESTIMATION konfiguracji o zakresie bazy danych jest użycie konstrukcji FUNKCJI USE HINT o zakresie zapytania. Aby uzyskać więcej informacji, zobacz USE HINT query hint argument w programie SQL Server 2016 i USE HINT.

Uwaga 16.

Istnieje również opcja z flagą QUERYTRACEON śledzenia 9481, ale należy rozważyć użycie USE HINT zamiast tego, ponieważ jest czystsza i nie wymaga specjalnych uprawnień.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION Umożliwia ustawienie modelu CE optymalizatora zapytań na wersję 70, niezależnie od poziomu zgodności bazy danych. Zobacz Poziom zapytania: użyj opcji Wskazówka zapytania lub ZAPYTANIETRACEON.

Alternatywnie, jeśli istnieje tylko jedno zapytanie, które jest problematyczne z domyślnym CE, możesz wymusić starszy plan CE przechowywany w magazynie zapytań lub użyć FORCE_LEGACY_CARDINALITY_ESTIMATION w połączeniu z przewodnikiem planu.

CE jest złożonym problemem, a algorytmy opierają się na mniej niż doskonałych danych dostępnych do oszacowań, takich jak statystyki tabel i indeksów. Nie ma żadnych informacji dotyczących niektórych konstrukcji poza modelem, takich jak funkcje tabela-wartośćd (TVFs) i modele oparte na wielu założeniach (takich jak korelacja lub niezależność predykatów i kolumn, jednolita dystrybucja danych, zawieranie itd.).

Biorąc pod uwagę nieograniczone kombinacje schematu klienta, danych i obciążeń, prawie nie można wybierać modeli, które działają we wszystkich przypadkach. Chociaż niektóre zmiany w domyślnym ce mogą zawierać błędy (takie jak inne oprogramowanie) i można je rozwiązać, inne problemy są spowodowane przez zmianę modelu.

Zmiany w wersjach CE, szczególnie z zakresu od 70 do 120, obejmują wiele różnych opcji dla używanych modeli. Na przykład podczas szacowania filtrów przyjmij pewien poziom korelacji między predykatami, ponieważ w praktyce taka korelacja często istnieje, a model CE 70 nie docenia wyników w takich przypadkach. Chociaż te zmiany zostały przetestowane pod kątem wielu obciążeń i ulepszone wiele zapytań, w przypadku niektórych innych zapytań starsza wersja CE była lepszym dopasowaniem, a tym samym z domyślnym ce, można zaobserwować regresje wydajności.

Niestety, nie jest uważany za usterkę. W takich sytuacjach należy użyć obejścia, takiego jak dostrajanie zapytania, podobnie jak w przypadku starszej wersji CE, jeśli wydajność zapytań nie jest akceptowalna, lub wymuszenie poprzedniego modelu CE lub określonego planu wykonania.

.: Czy istnieje jakiś zasób, aby dowiedzieć się więcej o zmianach kardynalności w domyślnym ce i wpływie na wydajność zapytań?

Aby uzyskać szczegółowe informacje, zobacz Optymalizowanie planów zapytań za pomocą narzędzia do szacowania kardynalności programu SQL Server 2014 i przeczytaj sekcję "What Changed in SQL Server 2014?" (Co się zmieniło w programie SQL Server 2014).