Szacowanie kardynalności (SQL Server)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric (wersja zapoznawcza)

Optymalizator zapytań programu SQL Server jest optymalizatorem zapytań opartym na kosztach. Oznacza to, że wybiera plany zapytań, które mają najniższy szacowany koszt przetwarzania do wykonania. Optymalizator zapytań określa koszt wykonywania planu zapytania na podstawie dwóch głównych czynników:

• Łączna liczba wierszy przetworzonych na każdym poziomie planu zapytania, nazywana kardynalnością planu.
• Model kosztów algorytmu dyktowanego przez operatory używane w zapytaniu.

Pierwszy czynnik, kardynalność, jest używany jako parametr wejściowy drugiego czynnika, modelu kosztów. W związku z tym ulepszona kardynalność prowadzi do lepszych szacowanych kosztów i z kolei szybszych planów wykonywania.

Szacowanie kardynalności (CE) w programie SQL Server pochodzi głównie z histogramów tworzonych podczas tworzenia indeksów lub statystyk ręcznie lub automatycznie. Czasami program SQL Server używa również logicznych przekształceń zapytań i informacji o ograniczeniach w celu określenia kardynalności.

W następujących przypadkach program SQL Server nie może dokładnie obliczyć kardynalności. Powoduje to niedokładne obliczenia kosztów, które mogą powodować nieoptymalne plany zapytań. Unikanie tych konstrukcji w zapytaniach może poprawić wydajność zapytań. Czasami możliwe są alternatywne formuły zapytań lub inne miary i są one wskazane:

• Zapytania z predykatami używającymi operatorów porównania między różnymi kolumnami tej samej tabeli.
• Zapytania z predykatami używającymi operatorów, a każda z następujących wartości jest prawdziwa:
  • Nie ma żadnych statystyk dotyczących kolumn występujących po obu stronach operatorów.
  • Rozkład wartości w statystykach nie jest jednolity, ale zapytanie szuka wysoce selektywnego zestawu wartości. Taka sytuacja może być szczególnie prawdziwa, jeśli operator jest inny niż operator równości (=).
  • Predykat używa operatora porównania nie równy (!=) lub operatora logicznego NOT .
• Zapytania korzystające z dowolnych wbudowanych funkcji programu SQL Server lub funkcji zdefiniowanej przez użytkownika, której argument nie jest stałą wartością.
• Zapytania obejmujące łączenie kolumn za pomocą operatorów arytmetycznych lub łączenia ciągów.
• Zapytania, które porównują zmienne, których wartości nie są znane podczas kompilowania i optymalizowania zapytania.

W tym artykule pokazano, jak można ocenić i wybrać najlepszą konfigurację CE dla systemu. Większość systemów korzysta z najnowszego CE, ponieważ jest to najbardziej dokładne. Ce przewiduje, ile wierszy zapytanie prawdopodobnie zwróci. Przewidywanie kardynalności jest używane przez optymalizator zapytań do generowania optymalnego planu zapytania. Dzięki bardziej dokładnym oszacowaniom optymalizator zapytań zazwyczaj może wykonać lepsze zadanie tworzenia bardziej optymalnego planu zapytania.

System aplikacji może potencjalnie mieć ważne zapytanie, którego plan został zmieniony na wolniejszy plan z powodu zmian w CE w różnych wersjach. Masz techniki i narzędzia do identyfikowania zapytania, które działa wolniej z powodu problemów z CE. Dostępne są również opcje rozwiązywania pojawiających się problemów z wydajnością.

Wersje oznaczenia CE

W 1998 r. główna aktualizacja CE była częścią programu SQL Server 7.0, dla którego poziom zgodności wynosił 70. Ta wersja modelu CE jest ustawiona na cztery podstawowe założenia:

• Niezależność: Zakłada się, że rozkłady danych w różnych kolumnach są niezależne od siebie, chyba że informacje korelacji są dostępne i użyteczne.

• Jednolitość: Unikatowe wartości są równomiernie rozmieszczone i mają taką samą częstotliwość. Dokładniej mówiąc, w każdym kroku histogramu różne wartości są równomiernie rozłożone, a każda wartość ma taką samą częstotliwość.

• Zawieranie (proste): Użytkownicy wysyłają zapytania o dane, które istnieją. Na przykład w przypadku sprzężenia równości między dwiema tabelami, uwzględnij selektywność predykatów¹ w każdym histogramie wejściowym przed połączeniem histogramów do oszacowania selektywności sprzężenia.

• Inkluzja: W przypadku predykatów filtrów, w których Column = Constantzakłada się, że stała rzeczywiście istnieje dla skojarzonej kolumny. Jeśli odpowiadający mu krok histogramu jest niepusty, przyjmuje się, że jedna z unikatowych wartości kroku jest zgodna z wartością z predykatu.

  ¹ Liczba wierszy, która spełnia predykat.

Kolejne aktualizacje zaczęły się od programu SQL Server 2014 (12.x), co oznacza poziom zgodności 120 lub nowszy. Aktualizacje CE dla poziomów 120 i nowszych obejmują zaktualizowane założenia i algorytmy, które działają dobrze w zakresie nowoczesnego magazynowania danych i obciążeń OLTP. Z założenia CE 70, następujące założenia modelu zostały zmienione począwszy od CE 120:

• Niezależność staje się korelacją: kombinacja różnych wartości kolumn nie musi być niezależna. Może to przypominać bardziej rzeczywiste wykonywanie zapytań dotyczących danych.
• Podstawowe zawieranie zmienia się w Bazowe zawieranie: użytkownicy mogą wykonywać zapytania dotyczące danych, które nie istnieją. Na przykład w przypadku łączenia równoważnego między dwiema tabelami używamy histogramów tabel bazowych do oszacowania selektywności sprzężenia, a następnie uwzględniamy selektywność predykatów.

Użyj Query Store do oceny wersji CE

Począwszy od programu SQL Server 2016 (13.x), magazyn zapytań jest przydatnym narzędziem do badania wydajności zapytań. Po włączeniu Query Store rozpocznie się monitorowanie wydajności zapytań w trakcie ich wykonywania, nawet jeśli zmienią się plany wykonawcze. Monitoruj magazyn zapytań pod kątem wysokich kosztów lub regresji wydajności zapytań. Aby uzyskać więcej informacji, zobacz Monitorowanie wydajności przy użyciu magazynu zapytań.

W przypadku przygotowania do uaktualnienia do programu SQL Server lub podwyższenia poziomu zgodności bazy danych na dowolnej platformie programu SQL Server rozważ uaktualnienie baz danych przy użyciu Asystenta dostrajania zapytań, co może pomóc w porównaniu wydajności zapytań na dwóch różnych poziomach zgodności.

Używanie zdarzeń rozszerzonych do oceny wersji CE

Inną opcją śledzenia procesu szacowania kardynalności jest użycie zdarzenia rozszerzonego o nazwie query_optimizer_estimate_cardinality. Poniższy przykład kodu Transact-SQL działa w programie SQL Server. Zapisuje plik xel do C:\Temp\ (chociaż można zmienić ścieżkę). Po otwarciu pliku xel w programie Management Studio jego szczegółowe informacje są wyświetlane w przyjazny dla użytkownika sposób.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

Aby uzyskać informacje o zdarzeniach rozszerzonych dostosowanych do usługi SQL Database, zobacz Zdarzenia rozszerzone w usłudze SQL Database.

Kroki oceny wersji CE

Poniżej przedstawiono kroki, których można użyć do oceny, czy którekolwiek z najważniejszych zapytań działa gorzej w ramach najnowszej wersji CE. Niektóre kroki są wykonywane przez uruchomienie przykładu kodu przedstawionego w poprzedniej sekcji.

1. Otwórz program SQL Server Management Studio (SSMS). Upewnij się, że baza danych programu SQL Server jest ustawiona na najwyższy dostępny poziom zgodności.

2. Wykonaj następujące wstępne kroki:

   1. Otwórz program SQL Server Management Studio (SSMS).

   2. Uruchom Transact-SQL, aby upewnić się, że baza danych programu SQL Server jest ustawiona na najwyższy dostępny poziom zgodności.

   3. Upewnij się, że twoja baza danych ma wyłączoną LEGACY_CARDINALITY_ESTIMATION konfigurację.

   4. Wyczyść magazyn zapytań. W bazie danych upewnij się, że magazyn zapytań jest włączony.

   5. Uruchom instrukcję: SET NOCOUNT OFF;

3. Uruchom instrukcję: SET STATISTICS XML ON;

4. Uruchom ważne zapytanie.

5. W okienku wyników na karcie Komunikaty zanotuj rzeczywistą liczbę wierszy, których dotyczy problem.

6. W okienku wyników na karcie Wyniki kliknij dwukrotnie komórkę zawierającą statystyki w formacie XML. Zostanie wyświetlony graficzny plan zapytania.

7. Kliknij prawym przyciskiem myszy pierwsze pole w planie zapytania graficznego, a następnie wybierz polecenie Właściwości.

8. W przypadku późniejszego porównania z inną konfiguracją zanotuj wartości następujących właściwości:

   • CardinalityEstimationModelVersion.

   • Szacowana liczba wierszy.

   • Szacowany koszt operacji we/wy i kilka podobnych Szacowanych właściwości, które obejmują rzeczywistą wydajność, a nie przewidywania liczby wierszy.

   • Operacja logiczna i operacja fizyczna. Równoległość jest dobrą wartością.

   • Rzeczywisty tryb wykonywania. Batch jest dobrą wartością, lepszą niż Row.

9. Porównaj szacowaną liczbę wierszy z rzeczywistą liczbą wierszy. Czy dokładność CE jest niedokładna o 1% (zawyżona lub zaniżona), czy o 10%?

10. Uruchom: SET STATISTICS XML OFF;

11. Uruchom Transact-SQL, aby zmniejszyć poziom zgodności bazy danych o jeden poziom (np. z 130 do 120).

12. Uruchom ponownie wszystkie kroki niezwiązane ze wstępnymi krokami.

13. Porównaj wartości właściwości CE z dwóch uruchomień.

    • Czy odsetek niedokładności w ramach najnowszego CE jest mniejszy niż w ramach starszego CE?

14. Na koniec porównaj różne wartości właściwości wydajności z dwóch uruchomień.

    • Czy zapytanie używało innego planu przy dwóch różnych szacunkach CE?

    • Czy zapytanie działa wolniej w ramach najnowszej wersji CE?

    • O ile zapytanie nie działa lepiej i według innego planu na starszej wersji CE, najprawdopodobniej zechcesz najnowszą wersję CE.

    • Jeśli jednak zapytanie działa z szybszym planem w ramach starszego Estymatora liczności (CE), rozważ wymuszenie przez system korzystania z szybszego planu i zignorowania Estymatora liczności. W ten sposób można mieć najnowsze CE we wszystkim, jednocześnie utrzymując szybszy plan w jednym szczególnym przypadku.

Jak aktywować najlepszy plan zapytań

Załóżmy, że w przypadku wersji CE 120 lub nowszej jest generowany mniej wydajny plan zapytania dla zapytania. Poniżej przedstawiono kilka opcji aktywacji lepszego planu, uporządkowanych od największego zakresu do najmniejszego:

• Poziom zgodności bazy danych można ustawić na wartość niższą niż najnowsza dostępna dla całej bazy danych.

  • Na przykład ustawienie poziomu zgodności 110 lub niższego aktywuje CE 70, ale sprawia, że wszystkie zapytania podlegają poprzedniemu modelowi CE.

  • Ponadto ustawienie niższego poziomu zgodności powoduje również pominięcie szeregu ulepszeń optymalizatora zapytań dla najnowszych wersji i wpływa na wszystkie zapytania względem bazy danych.

• Można użyć LEGACY_CARDINALITY_ESTIMATION opcji konfiguracji o określonym zakresie bazy danych, aby cała baza danych korzystała ze starszego CE, zachowując inne ulepszenia optymalizatora zapytań.

• Możesz użyć wskazówki zapytania LEGACY_CARDINALITY_ESTIMATION do wykorzystania starszego CE dla pojedynczego zapytania, zachowując jednocześnie inne ulepszenia optymalizatora zapytań.

• Możesz wymusić działanie LEGACY_CARDINALITY_ESTIMATION za pomocą funkcji podpowiedzi Magazynu Zapytań, aby jedno zapytanie korzystało ze starszego CE, bez konieczności zmiany samego zapytania.

• Wymuś inny plan za pomocą Query Store.

Poziom zgodności bazy danych

Możesz upewnić się, że baza danych jest na określonym poziomie, korzystając z następującego kodu Transact-SQL dla COMPATIBILITY_LEVEL.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO  

W przypadku wstępnie istniejących baz danych działających na niższych poziomach zgodności, o ile aplikacja nie musi używać ulepszeń, które są dostępne tylko na wyższym poziomie zgodności bazy danych, jest to prawidłowe podejście do zachowania poprzedniego poziomu zgodności bazy danych. W przypadku nowych prac programistycznych lub gdy istniejąca aplikacja wymaga użycia nowych funkcji, takich jak inteligentne przetwarzanie zapytań, a także niektórych nowych transact-SQL, zaplanuj uaktualnienie poziomu zgodności bazy danych do najnowszej dostępnej wersji. Aby uzyskać więcej informacji, zobacz Poziomy zgodności i Uaktualnienia aparatu bazy danych.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO  

W przypadku bazy danych programu SQL Server ustawionej na poziomie zgodności 120 lub wyższym aktywacja flagi śledzenia 9481 wymusza użycie systemu w wersji 70 CE.

Starsze narzędzie do szacowania kardynalności

W przypadku bazy danych programu SQL Server ustawionej na poziomie zgodności 120 lub nowszym, starszy estymator kardynalności (CE w wersji 70) można aktywować na poziomie bazy danych przy użyciu polecenia ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

Modyfikowanie zapytania w celu użycia wskazówki

Począwszy od programu SQL Server 2016 (13.x) z dodatkiem SP1, zmodyfikuj zapytanie, aby używało wskazówki zapytaniaUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

Ustaw wskazówkę Query Store

Zapytania można wymusić na użyciu starszego narzędzia do szacowania kardynalności bez modyfikowania zapytania przy użyciu wskazówek magazynu zapytań.

1. Zidentyfikuj zapytanie w widokach katalogu sys.query_store_query_text i sys.query_store_query Magazynu zapytań. Na przykład wyszukaj wykonane zapytanie według fragmentu tekstu:

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt 
   INNER JOIN sys.query_store_query q ON 
   qt.query_text_id = q.query_text_id 
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
   AND query_sql_text not like N'%query_store%';
   

2. Poniższy przykład stosuje wskazówkę magazynu zapytań, aby wymusić użycie starszego estymatora kardynalności na query_id 39 bez modyfikowania zapytania.

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Jak wymusić określony plan zapytania

W celu uzyskania najlepszej kontroli można wymusić użycie planu wygenerowanego z CE 70 podczas testowania. Po przypięciu preferowanego planu możesz ustawić całą bazę danych, aby korzystała z najnowszego poziomu zgodności i CE. Opcja jest opracowywana w następnej kolejności.

Magazyn zapytań udostępnia różne sposoby wymuszania użycia określonego planu zapytania przez system:

• Wykonaj sys.sp_query_store_force_plan.

• W programie SQL Server Management Studio (SSMS) rozwiń węzeł Magazyn zapytań, kliknij prawym przyciskiem myszy pozycję Węzły zużywające najwięcej zasobów, a następnie wybierz pozycję Wyświetl węzły zużywające najwięcej zasobów. Na ekranie są wyświetlane przyciski oznaczone etykietą Force Plan i Unforce Plan.

Aby uzyskać więcej informacji na temat magazynu zapytań, zobacz Monitorowanie wydajności przy użyciu magazynu zapytań.

Stałe składanie i obliczanie wyrażeń podczas szacowania kardynalności

Aparat bazy danych ocenia niektóre wyrażenia stałe na wczesnym etapie w celu zwiększenia wydajności zapytań. Jest to nazywane ciągłym składaniem. Stała jest literałem Transact-SQL, takim jak 3, , 'ABC''2005-12-31', 1.0e3lub 0x12345678. Aby uzyskać więcej informacji, zobacz Stałe składanie.

Ponadto niektóre wyrażenia, które nie są stałe złożone, ale których argumenty są znane w czasie kompilacji, niezależnie od tego, czy argumenty są parametrami lub stałymi, są oceniane przez narzędzie do szacowania rozmiaru zestawu wyników (kardynalności), który jest częścią optymalizatora zapytań podczas optymalizacji. Aby uzyskać więcej informacji, zobacz Ocena wyrażeń.

Najlepsze metody: używanie stałego składania i obliczania wyrażeń w czasie kompilacji do generowania optymalnych planów zapytań

Aby upewnić się, że wygenerujesz optymalne plany zapytań, najlepiej zaprojektować zapytania, procedury składowane i zestawy instrukcji, aby optymalizator zapytań mógł dokładnie oszacować selektywność warunków w zapytaniu, w oparciu o statystyki dotyczące rozkładu danych. W przeciwnym razie optymalizator zapytań musi użyć domyślnego oszacowania podczas szacowania selektywności.

Aby upewnić się, że narzędzie do szacowania kardynalności optymalizatora zapytań zapewnia dobre oszacowania, najpierw upewnij się, że opcje zestawów AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS bazy danych są włączone (ustawienie domyślne) lub że ręcznie utworzono statystyki dotyczące wszystkich kolumn, do których odwołuje się warunek zapytania. Następnie podczas projektowania warunków w zapytaniach wykonaj następujące czynności, gdy jest to możliwe:

• Unikaj używania zmiennych lokalnych w zapytaniach. Zamiast tego użyj parametrów, literałów lub wyrażeń w zapytaniu.

• Ogranicz użycie operatorów i funkcji osadzonych w zapytaniu, które zawiera parametr, do operatorów i funkcji wymienionych w Compile-Time Expression Evaluation for Cardinality Estimation.

• Upewnij się, że wyrażenia zawierające tylko stałe w warunku zapytania są podlegające składaniu lub mogą być ewaluowane w czasie kompilacji.

• Jeśli musisz użyć zmiennej lokalnej do obliczenia wyrażenia, które ma być używane w zapytaniu, rozważ ocenę go w innym zakresie niż zapytanie. Na przykład przydatne może być wykonanie jednej z następujących opcji:

  • Przekaż wartość zmiennej do procedury składowanej zawierającej zapytanie, które chcesz ocenić, i użyj parametru procedury zamiast zmiennej lokalnej.

  • Skonstruuj ciąg zawierający zapytanie częściowo na podstawie wartości zmiennej lokalnej, a następnie wykonaj ciąg przy użyciu dynamicznego języka SQL (EXEC lub najlepiej sp_executesql).

  • Sparametryzuj zapytanie i wykonaj je przy użyciu metody sp_executesql, a następnie przekaż wartość zmiennej jako parametr do zapytania.

Przykłady ulepszeń CE

W tej sekcji opisano przykładowe zapytania, które korzystają z ulepszeń wdrożonych w ce w ostatnich wersjach. Są to informacje w tle, które nie wymagają konkretnego działania z twojej strony.

Przykład A. CE rozumie, że maksymalna wartość może być wyższa niż w przypadku ostatniego zebrania statystyk

Załóżmy, że statystyki zostały ostatnio zebrane dla OrderTable w dniu 2016-04-30, kiedy maksymalna OrderAddedDate wynosiła 2016-04-30. CE 120 (i wyższa wersja) rozumie, że kolumny w OrderTable, które mają rosnące dane, mogą mieć wartości większe niż maksymalna zarejestrowana przez statystyki. Zrozumienie to umożliwia ulepszenie planu wykonania zapytań dla instrukcji SELECT w języku Transact-SQL, takich jak poniżej.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';  

Przykład B. CE rozumie, że przefiltrowane predykaty w tej samej tabeli są często skorelowane

W poniższym poleceniu SELECT uwidaczniają się przefiltrowane predykaty na Model i ModelVariant. Intuicyjnie rozumiemy, że gdy Model jest "Xbox", istnieje szansa, że ModelVariant to "One", biorąc pod uwagę, że Xbox ma wariant o nazwie One.

Począwszy od CE 120, SQL Server rozumie, że istnieje możliwa korelacja między dwiema kolumnami w tej samej tabeli, Model i ModelVariant. Mechanizm oceny dokonuje dokładniejszego oszacowania liczby wierszy, które mogą zostać zwrócone przez zapytanie, a optymalizator zapytań generuje optymalny plan.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';  

Przykład C. CE nie zakłada już żadnej korelacji między filtrowanymi predykatami z różnych tabel

Obszerne nowe badania dotyczące nowoczesnych obciążeń i rzeczywistych danych biznesowych pokazują, że filtry predykatu z różnych tabel zwykle nie są ze sobą skorelowane. W poniższym zapytaniu CE zakłada, że nie ma korelacji między s.type i r.date. W związku z tym CE dokonuje niższego oszacowania liczby zwracanych wierszy.

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';  

Treści powiązane

• szacowania kardynalności (CE) opinii
• Monitorowanie i dostrajanie pod kątem wydajności
• optymalizowanie planów zapytań przy użyciu narzędzia do szacowania kardynalności programu SQL Server 2014
• wskazówki (Transact-SQL) - zapytania
• WSKAZÓWKI DOTYCZĄCE ZAPYTAŃ WSKAZÓWEK DOTYCZĄCYCH WSKAZÓWEK
• Uaktualnianie baz danych przy użyciu Asystenta dostrajania zapytań
• Monitorowanie wydajności za pomocą magazynu zapytań
• przewodnik po architekturze przetwarzania zapytań
• Wskazówki dotyczące Magazynu Zapytań
• inteligentne przetwarzanie zapytań w bazach danych SQL
• DBCC TRACEON — flagi śledzenia (Transact-SQL)
• sys.query_store_query_hints