Założenie dotyczące dołączania w narzędziu do szacowania nowej kardynalności obniża wydajność zapytań

Ten artykuł ułatwia rozwiązywanie problemów z wydajnością, które mogą wystąpić w programie SQL Server 2014 i nowszych wersjach podczas kompilowania zapytań przy użyciu nowego narzędzia do szacowania kardynalności.

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 3189675

Symptomy

Rozważmy następujący scenariusz:

• Używasz programu SQL Server 2014 lub nowszej wersji.
• Uruchamiasz zapytanie zawierające sprzężenia i predykaty filtrów innych niż sprzężenia.
• Zapytanie kompilujesz przy użyciu nowego szacowania kardynalności (SQL Server) (Nowy CE).

W tym scenariuszu występuje spadek wydajności zapytań.

Ten problem nie występuje, jeśli skompilujesz zapytanie przy użyciu starszej wersji CE.

Przyczyna

W programie SQL Server 2014 wprowadzono nowy narzędzie do szacowania kardynalności (Nowy CE) dla poziomu zgodności bazy danych 120 i nowszych. Nowy CE zmienia kilka założeń ze starszej wersji CE w modelu, który jest używany przez Optymalizator zapytań, gdy szacuje kardynalność dla różnych operatorów i predykatów.

Jedna z tych zmian jest związana z założeniem dołączenia.

Model Legacy CE zakłada, że użytkownicy zawsze wysyłają zapytania o dane, które istnieją. Oznacza to, że w przypadku predykatu sprzężenia, który obejmuje operację równoczesną dla dwóch tabel, sprzężone kolumny istnieją po obu stronach sprzężenia. W obecności dodatkowych predykatów filtrów innych niż sprzężenia względem tabeli sprzężenia starsza wersja CE zakłada pewien poziom korelacji dla predykatów sprzężenia i predykatów filtrów innych niż sprzężenia. Ta dorozumiana korelacja jest nazywana prostym zawieraniem.

Alternatywnie Nowy CE używa podstawowego zawierania jako korelacji. Nowy model CE zakłada, że użytkownicy mogą wykonywać zapytania dotyczące danych, które nie istnieją. Oznacza to, że predykaty filtrów w oddzielnych tabelach mogą nie być skorelowane ze sobą. W związku z tym używamy podejścia probabilistycznego.

W przypadku wielu praktycznych scenariuszy użycie założenia Podstawowe zawieranie tworzy lepsze oszacowania. To z kolei powoduje utworzenie bardziej wydajnych wyborów planu zapytań. Jednak w niektórych sytuacjach użycie założenia prostego zawierania może zapewnić lepsze wyniki. W takim przypadku można skorzystać z mniej wydajnego wyboru planu zapytań w przypadku korzystania z nowego CE zamiast starszej wersji CE.

Aby uzyskać więcej informacji na temat rozwiązywania problemów związanych z nowym ce, zobacz Zmniejszona wydajność zapytań po uaktualnieniu z programu SQL Server 2012 lub starszego do wersji 2014 lub nowszej.

Rozwiązanie

W programie SQL Server 2014 i nowszych wersjach można użyć flagi śledzenia 9476 , aby wymusić użycie założenia prostego zawierania zamiast domyślnego założenia podstawowego zawierania. Jeśli możesz zmodyfikować zapytanie aplikacji, lepszym rozwiązaniem jest użycie wskazówki ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS dotyczącej zapytania po programie SQL Server 2016 (13.x) SP1. Aby uzyskać więcej informacji, zobacz USE HINT (WSKAZÓWKA UŻYCIA). Na przykład:

SELECT * FROM Table1 t1
JOIN Table2 t2
ON t1.Col1 = t2.Col1
WHERE Col1 = 10
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

Włączenie tej flagi śledzenia lub użycie wskazówki zapytania może poprawić wybór planu zapytania bez konieczności pełnego przywracania do starszego modelu CE, jeśli spełnione są następujące warunki:

• Występuje nieoptymalny wybór planu zapytania, który powoduje ogólną obniżoną wydajność zapytań dla zapytań zawierających sprzężenia i predykaty filtrów nieprzyłączonych.
• Można sprawdzić znaczną niedokładność w szacowaniu "kardynalności sprzężenia" (czyli rzeczywistej i szacowanej liczbie wierszy, które różnią się znacząco).
• Ta niedokładność nie istnieje podczas kompilowania zapytań przy użyciu starszej wersji CE.

Tę flagę śledzenia można włączyć globalnie, na poziomie sesji lub na poziomie zapytania.

Uwaga 16.

Nieprawidłowe używanie flag śledzenia może obniżyć wydajność obciążenia. Aby uzyskać więcej informacji, zobacz Wskazówki (Transact-SQL) — zapytanie.