Udostępnij przez

Zmniejszono wydajność zapytań po uaktualnieniu z SQL Server 2012 r. lub starszych do 2014 r. lub nowszych

  • Artykuł

Po uaktualnieniu SQL Server z 2012 r. lub starszej wersji 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ę, jedną ze stosunkowo częstych przyczyn są zmiany w modelu szacowania kardynalności (CE) po uaktualnieniu. Od SQL Server 2014 r. wprowadzono istotne zmiany w modelach CE.

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

Uwaga

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

Rozwiązywanie problemów: określ, czy zmiany CE są problemem, i dowiedz się przyczyny

Krok 1. Określenie, czy jest używana domyślna wersja CE

  1. Wybierz zapytanie, które działa wolniej po uaktualnieniu.
  2. Uruchom zapytanie i zbierz plan wykonywania.
  3. W okno Właściwości planu wykonywania sprawdź pozycję CardinalityEstimationModelVersion. Znajdź wersję modelu CE z planu wykonywania okno Właściwości.
  4. Wartość 70 wskazuje starszą wartość 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żywana domyślna 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 przy użyciu starszej wersji 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 w przypadku starszej wersji CE

Przetestuj różne wskazówki dotyczące zapytań związanych z CE dla zapytania. W przypadku SQL Server 2014 r. użyj odpowiednich flag śledzenia 4137, 9472 i 4139, aby przetestować zapytanie. Określ, 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 następujących metod:

  • Zoptymalizuj zapytanie.

    Co zrozumiałe, nie zawsze można ponownie zapisywać zapytania, ale szczególnie jeśli istnieje tylko kilka zapytań, które można ponownie napisać, 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.

    To ukierunkowane podejście umożliwia innym obciążeniam 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 niezawodna opcja).

  • Wymuś dobry plan.

    Jest to korzystna opcja i może służyć do kierowania określonych zapytań. Wymuszanie planu można wykonać za pomocą przewodnika po planie lub usług QDS. Usługa QDS jest ogólnie łatwiejsza w użyciu.

  • Użyj konfiguracji o zakresie bazy danych , aby wymusić starszą CE.

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

  • Użyj flagi śledzenia 9841, aby wymusić globalną starszą CE. W tym celu użyj funkcji DBCC TRACEON lub ustaw flagę śledzenia jako parametr uruchamiania.

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

Opcje włączania starszej wersji CE

Poziom zapytania: użyj opcji Query Hint lub QUERYTRACEON

  • W przypadku SQL Server wersji 2016 z dodatkiem SP1 i nowszym użyj wskazówek FORCE_LEGACY_CARDINALITY_ESTIMATION dotyczących 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 SQL Server 2016 r. i nowszych wersji zmień konfigurację bazy danych w zakresie:

      --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 SQL Server 2014 roku. Należy pamiętać, że ta zmiana ma wpływ nie tylko na CE. Aby określić wpływ zmian poziomu zgodności, przejdź do pozycji ALTER DATABASE compatibility level (Transact-SQL) i sprawdź w nim tabele "Różnice".

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

Uwaga

Ta zmiana wpłynie na wszystkie zapytania wykonywane w kontekście bazy danych, dla której konfiguracja została zmieniona, chyba że zostanie użyta nadrzędna flaga śledzenia lub wskazówka zapytania. Zapytania, które działają lepiej z powodu domyślnego CE, mogą się cofać.

Poziom serwera: użyj flagi śledzenia

Użyj flagi śledzenia 9481, aby wymusić starszą CE dla całego serwera:

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

Uwaga

Ta zmiana wpłynie na wszystkie zapytania wykonywane w kontekście wystąpienia SQL Server, chyba że zostanie użyta nadrzędna flaga śledzenia lub wskazówka zapytania. Zapytania, które działają lepiej z powodu domyślnego CE, mogą się cofać.

Często zadawane pytania

W przypadku istniejących wcześniej baz danych działających na niższych poziomach zgodności zalecany przepływ pracy w celu uaktualnienia procesora zapytań do wyższego poziomu zgodności został szczegółowo opisany w temacie Zmienianie trybu zgodności bazy danych i używanie scenariuszy użycia magazynu zapytań i magazynu zapytań. Metodologia wprowadzona w artykule dotyczy przenoszenia do wersji 130 lub nowszej dla SQL Server i Azure SQL Database.

P2: Nie mam czasu na testowanie zmian CE. Co mogę zrobić w tym przypadku?

W przypadku istniejących aplikacji i obciążeń nie zalecamy przechodzenia do domyślnej wersji CE, dopóki nie zostaną wykonane wystarczające testy regresji. Jeśli nadal masz wątpliwości, zalecamy dalsze uaktualnianie 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 SQL Server 2014 r. lub skonfiguruj konfigurację ON bazy danych o zakresie LEGACY_CARDINALITY_ESTIMATION dla wersji SQL Server 2016 i nowszych, dopóki nie będziesz mieć możliwości przetestowania.

P3: Czy istnieją jakiekolwiek wady trwałego używania starszej wersji CE?

Przyszłe ulepszenia i poprawki związane z narzędziem do szacowania kardynalności są skoncentrowane na nowszych wersjach. Wersja 70 jest akceptowalnym stanem pośrednim. Jednak po dokładnym przetestowaniu zalecamy 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, dlatego 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żna

Domyślną ścieżką kodu CE jest główna ścieżka kodu, która będzie otrzymywać przyszłe inwestycje i dokładniejsze pokrycie testami w perspektywie długoterminowej, więc nie planuj używania starszej wersji CE przez czas nieokreślony.

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

W SQL Server 2014 r. włącz flagę śledzenia 9481, aby używać starszej wersji CE dla wszystkich baz danych niezależnie od poziomu zgodności. W przypadku SQL Server wersji 2016 i nowszej wykonaj następujące zapytanie, aby iterować bazy danych. 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 Azure SQL Database możesz utworzyć bilet pomocy technicznej, aby ta flaga śledzenia była włączona na poziomie subskrypcji, ale nie na poziomie serwera.

P5: Czy uruchomienie starszej wersji CE uniemożliwi mi uzyskanie dostępu do nowych funkcji?

Nawet po włączeniu LEGACY_CARDINALITY_ESTIMATION nadal będziesz mieć dostęp do najnowszych funkcji dołączonych do wersji 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 SQL Server 2017 r. nadal może korzystać z rodziny funkcji adaptacyjnego przetwarzania zapytań.

P6: Kiedy starsza wersja CE przestanie być obsługiwana?

Nie mamy planów, aby przestać wspierać starszą CE w tym momencie. Jednak przyszłe ulepszenia i poprawki związane z narzędziem do szacowania kardynalności są skoncentrowane wokół nowszych wersji CE.

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

Bardziej szczegółową alternatywą dla flagi śledzenia o zakresie serwera 9481 lub LEGACY_CARDINALITY_ESTIMATION konfiguracji o zakresie bazy danych jest użycie konstrukcji USE HINT o zakresie zapytania. Aby uzyskać więcej informacji, zobacz USE HINT query hint argument in SQL Server 2016 and USE HINT (UŻYWANIE argumentu wskazówki dotyczącej zapytania w SQL Server 2016 r.) i USE HINT (UŻYJ WSKAZÓWKI).

Uwaga

Istnieje również opcja z flagą QUERYTRACEON śledzenia 9481, ale należy rozważyć użycie USE HINT zamiast tego, ponieważ jest ona czystsza semantycznie 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 Query Hint lub QUERYTRACEON.

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ć go FORCE_LEGACY_CARDINALITY_ESTIMATION w połączeniu z przewodnikiem po planie.

CE jest złożonym problemem, a algorytmy polegają na mniej niż doskonałych danych dostępnych dla oszacowań, takich jak statystyki tabel i indeksów. Brak informacji dotyczących niektórych konstrukcji poza modelem, takich jak funkcje o wartości tabeli (TVF) i modele oparte na wielu założeniach (takich jak korelacja lub niezależność predykatów i kolumn, jednolity rozkład danych, hermetyzowanie itd.).

Biorąc pod uwagę nieograniczone kombinacje schematu klienta, danych i obciążeń, prawie niemożliwe jest wybranie modeli, które działają we wszystkich przypadkach. Niektóre zmiany w domyślnej wersji CE mogą zawierać usterki (jak każde inne oprogramowanie) i można je rozwiązać, ale inne problemy są spowodowane zmianą modelu.

Zmiany w wersjach CE, zwłaszcza z zakresu od 70 do 120, obejmują wiele różnych opcji 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. Mimo że te zmiany były testowane pod kątem wielu obciążeń i ulepszono wiele zapytań, w przypadku niektórych innych zapytań starsza wersja CE była lepsza i dlatego w przypadku domyślnego CE można zaobserwować regresje wydajności.

Niestety, nie jest to uważane za usterkę. W takich sytuacjach użyj obejścia, takiego jak dostrajanie zapytania, podobnie jak w przypadku starszej wersji CE, jeśli wydajność zapytań jest nie do przyjęcia, lub wymuszanie poprzedniego modelu CE lub określonego planu wykonywania.

P9: Czy istnieje zasób umożliwiający poznanie szczegółowych informacji o zmianach kardynalności w domyślnym interfejsie CE i wpływie na wydajność zapytania?

Aby uzyskać szczegółowe informacje, zobacz Optymalizowanie planów zapytań za pomocą narzędzia do szacowania kardynalności SQL Server 2014 r., a następnie przeczytaj sekcję "Co się zmieniło w SQL Server 2014?".