Szczegółowe funkcje inteligentnego przetwarzania zapytań

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

Ten artykuł zawiera szczegółowe opisy różnych funkcji inteligentnego przetwarzania zapytań (IQP), notatek o wydaniu oraz innych szczegółów. Rodzina funkcji inteligentnego przetwarzania zapytań (IQP) obejmuje funkcje o szerokim wpływie, które zwiększają wydajność istniejących obciążeń przy minimalnym wysiłku implementacji do wdrożenia.

Można automatycznie umożliwić obciążeniom kwalifikowanie się do inteligentnego przetwarzania zapytań, włączając odpowiedni poziom zgodności bazy danych. Można to ustawić przy użyciu języka Transact-SQL. Aby na przykład ustawić poziom zgodności bazy danych na SQL Server 2022 (16.x):

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 160;

Aby uzyskać więcej informacji na temat zmian wprowadzonych w nowych wersjach, zobacz:

• Co nowego w programie SQL Server 2017
• Co nowego w programie SQL Server 2019
• Co nowego w programie SQL Server 2022

Sprzężenia adaptacyjne w trybie wsadowym

Dotyczy: SQL Server (począwszy od programu SQL Server 2017 (14.x)), Azure SQL Database

Funkcja adaptacyjnych sprzężeń w trybie wsadowym pozwala na opóźnienie wyboru metody sprzężenia skrótu lub zagnieżdżonego sprzężenia aż do zeskanowania pierwszych danych wejściowych, przy wykorzystaniu pojedynczego planu buforowanego. Operator Sprzężenia Adaptacyjnego definiuje próg, który określa moment przełączenia się na plan zagnieżdżonych pętli. Dlatego Twój plan może dynamicznie zmieniać się na bardziej efektywną strategię połączenia w trakcie realizacji.

Aby uzyskać więcej informacji, w tym jak wyłączyć sprzężenia adaptacyjne bez zmiany poziomu zgodności, zobacz Understanding Adaptive joins (Opis sprzężeń adaptacyjnych).

Przeplatane wykonywanie dla msTVFs

Dotyczy: SQL Server (począwszy od programu SQL Server 2017 (14.x)), Azure SQL Database

Funkcja z wieloma instrukcjami (MSTVF) jest typem funkcji zdefiniowanej przez użytkownika, która może akceptować parametry, wykonywać wiele instrukcji języka T-SQL i RETURN tabelę.

Przeplatane wykonywanie pomaga w problemach z wydajnością obciążeń, które są spowodowane stałym oszacowaniem kardynalności skojarzonym z MSTVF. W przypadku przeplatanego wykonywania rzeczywiste liczby wierszy z funkcji są używane do podejmowania lepszych świadomych decyzji dotyczących planu zapytania podrzędnego.

MSTVF-y mają stałą prognozę kardynalności wynoszącą 100, począwszy od SQL Server 2014 (12.x), i 1 dla wcześniejszych wersji SQL Server.

Przeplatane wykonywanie zmienia jednokierunkową granicę między fazami optymalizacji i wykonywania dla wykonywania pojedynczego zapytania i umożliwia dostosowanie planów na podstawie poprawionych szacunków kardynalności. Podczas optymalizacji, jeśli aparat bazy danych napotka kandydata do wykonania przeplatanego, które korzysta z wielowykładowych funkcji o wartości tabeli (MSTVFs), optymalizacja zostanie wstrzymana, wykona odpowiednie poddrzewo, przechwytuje dokładne oszacowania kardynalności, a następnie wznowi optymalizację dla operacji podrzędnych.

Na poniższej ilustracji przedstawiono dane wyjściowe Statystyki Zapytania Na Żywo, podzbiór ogólnego planu wykonywania, który pokazuje wpływ dokładnych oszacowań kardynalności z MSTVF.

Możesz zobaczyć rzeczywisty przepływ wierszy w porównaniu z szacowanymi wierszami. Istnieją trzy godne uwagi obszary planu (przepływ czytany od prawej do lewej):

• Skanowanie tabeli MSTVF ma oszacowanie stałe na 100 wierszy. W tym przykładzie istnieje jednak 527 597 wierszy przepływających przez to skanowanie tabeli MSTVF, jak widać w statystykach zapytań na żywo za pośrednictwem 527597 100 rzeczywistych szacowanych wartości - więc stałe oszacowanie jest znacznie niesymetryczne.
• W przypadku operacji zagnieżdżonych pętli jest przyjęte, że po zewnętrznej stronie sprzężenia zwracanych jest tylko 100 wierszy. Biorąc pod uwagę dużą liczbę wierszy rzeczywiście zwracanych przez MSTVF, prawdopodobnie lepiej będzie użyć zupełnie innego algorytmu sprzężenia.
• Zwróć uwagę na mały symbol ostrzeżenia w przypadku operacji dopasowania skrótu, który w tym przypadku wskazuje zapis na dysk.

Porównaj poprzedni plan z obecnym planem wygenerowanym z włączoną funkcją przeplatanej realizacji.

• Zwróć uwagę, że skanowanie tabeli MSTVF odzwierciedla teraz dokładne oszacowanie kardynalności. Zwróć również uwagę na zmiana kolejności tego skanowania tabeli i innych operacji.
• Jeśli chodzi o algorytmy sprzężenia, przełączyliśmy się z operacji Zagnieżdżonej Pętli na operację Dopasowania hashu, która jest bardziej optymalna, biorąc pod uwagę dużą liczbę wierszy.
• Zwróć również uwagę, że nie mamy już ostrzeżeń o rozlaniu danych, ponieważ przydzielamy więcej pamięci na podstawie rzeczywistej liczby wierszy przepływających ze skanowania tabeli MSTVF.

Przeplatane instrukcje uprawniające do wykonania

MSTVF odwołujące się do stwierdzeń w przeplatanym wykonywaniu muszą być obecnie tylko do odczytu i nie mogą być częścią operacji zmiany danych. Ponadto pliki MSTVFs nie kwalifikują się do przeplatanego wykonywania, jeśli nie używają stałych środowiska uruchomieniowego.

Korzyści z przeplatanego wykonywania

Ogólnie rzecz biorąc, im większa jest niesymetryczność między szacowaną a rzeczywistą liczbą wierszy, w połączeniu z liczbą operacji planu podrzędnego, tym większy wpływ na wydajność.

Ogólnie rzecz biorąc, przeplatane wykonywanie przynosi korzyści zapytaniom, dla których:

• Istnieje duża niesymetryczność między szacowaną a rzeczywistą liczbą wierszy dla zestawu wyników pośrednich (w tym przypadku MSTVF).
• Ogólne zapytanie jest wrażliwe na zmianę rozmiaru wyniku pośredniego. Zwykle dzieje się tak, gdy w planie zapytania powyżej tego poddrzewa znajduje się złożone drzewo. Proste SELECT * z MSTVF nie skorzysta z przeplatanego wykonywania.

Przeplatane obciążenie związane z wykonywaniem

Obciążenie powinno być minimalne do żadnego. MSTVFs były już zmaterializowane przed wprowadzeniem przeplatanego wykonywania, jednak różnica polega na tym, że teraz pozwalamy na odroczoną optymalizację, a następnie używamy oszacowania kardynalności zmaterializowanego zestawu wierszy. Podobnie jak w przypadku każdego planu mającego wpływ na zmiany, niektóre plany mogą ulec zmianie, tak aby z lepszą kardynalnością poddrzewa uzyskaliśmy gorszy plan dla całego zapytania. Środki zaradcze mogą obejmować przywrócenie poziomu zgodności lub użycie Query Store do wymuszenia używania wersji planu bez regresji.

Przeplatane wykonania i konsekwentne wykonania

Po buforowaniu przeplatanego planu wykonania plan ze zmienionymi oszacowaniami pierwszego wykonania jest używany do kolejnych wykonań bez ponownego inicjowania wykonania.

Śledzenie przeplatanych działań wykonywania

Atrybuty użycia są widoczne w rzeczywistym planie wykonywania zapytania:

Możesz również śledzić przeplatane wystąpienia wykonywania za pomocą następujących zdarzeń rozszerzonych:

Aby umożliwić przeplatane wykonywanie i skorygować oszacowania kardynalności funkcji tabelowej wielowartościowej MSTVF, trzeba wykonać zapytanie. Jednak szacowany plan wykonania nadal pokazuje, kiedy istnieją kandydaci do przeplatanych wykonań za pośrednictwem atrybutu ContainsInterleavedExecutionCandidates showplan.

Przeplatane buforowanie wykonywania

Jeśli plan zostanie wyczyszczony lub wykluczony z pamięci podręcznej, po wykonaniu zapytania istnieje nowa kompilacja, która używa przeplatanego wykonywania. Instrukcja używająca polecenia OPTION (RECOMPILE) tworzy nowy plan przy użyciu przeplatanego wykonywania i nie buforuje go.

Przeplatane wykonywanie i interoperacyjność Query Store

Plany korzystające z przeplatanego wykonywania można wymusić. Plan jest wersją, która zawiera skorygowane estymaty kardynalności na podstawie wstępnego wykonania.

Wyłącz przeplatane wykonywanie bez zmiany poziomu zgodności

Wykonywanie przeplatane można wyłączyć w zakresie bazy danych lub instrukcji przy zachowaniu zgodności bazy danych na poziomie 140 i wyższym. Aby wyłączyć przeplatane wykonywanie dla wszystkich wykonań zapytań pochodzących z bazy danych, wykonaj następujące czynności w kontekście odpowiedniej bazy danych:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = OFF;

Po włączeniu to ustawienie jest wyświetlane jako włączone w sys.database_scoped_configurations. Aby ponownie włączyć przeplatane wykonywanie dla wszystkich wykonań zapytań pochodzących z bazy danych, wykonaj następujące czynności w kontekście odpowiedniej bazy danych:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON;

Można również wyłączyć przeplatane wykonywanie dla określonego zapytania, wyznaczając DISABLE_INTERLEAVED_EXECUTION_TVF jako wskazówkę zapytania USE HINT. For example:

SELECT [fo].[Order Key], [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
                            '1-01-2013',
                            '10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
                            AND [fo].[City Key] = [foo].[City Key]
                            AND [fo].[Customer Key] = [foo].[Customer Key]
                            AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
                            AND [fo].[Order Date Key] = [foo].[Order Date Key]
                            AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
                            AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
                            AND [fo].[Picker Key] = [foo].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

Wskazówka zapytania USE HINT ma pierwszeństwo przed ustawieniem konfiguracji w zakresie bazy danych lub flagi śledzenia.

Scalar UDF inlining (Podkreślenie funkcji UDF skalarnych)

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)), Azure SQL Database

Scalar UDF inlining automatycznie przekształca skalarne funkcje zdefiniowane przez użytkownika w wyrażenia relacyjne. Osadza je w wywołującym zapytaniu SQL. Ta transformacja poprawia wydajność obciążeń korzystających ze skalarnych UDF. Włączanie skalarnych funkcji zdefiniowanych przez użytkownika (UDF) ułatwia optymalizację opartą na kosztach operacji wewnątrz tych funkcji. Wyniki są wydajne, zorientowane na zestaw i równoległe zamiast nieefektywnych, iteracyjnych, szeregowych planów wykonywania. Ta funkcja jest domyślnie włączona na poziomie zgodności bazy danych 150 lub nowszym.

Aby uzyskać więcej informacji, zobacz Scalar UDF inlining.

Kompilacja odroczona zmiennej tabeli

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)), Azure SQL Database

Kompilacja odroczona zmiennej tabeli poprawia jakość planu i ogólną wydajność zapytań odwołujących się do zmiennych tabeli. Podczas optymalizacji i początkowej kompilacji planu ta funkcja propaguje szacunki kardynalności oparte na rzeczywistych liczbach wierszy zmiennych tabeli. Te dokładne informacje dotyczące liczby wierszy będą następnie używane do optymalizowania operacji planu podrzędnego.

W przypadku kompilacji odroczonej zmiennej tabeli kompilacja instrukcji odwołującej się do zmiennej tabeli jest odroczona do pierwszego rzeczywistego wykonania instrukcji. To odroczone zachowanie kompilacji jest identyczne z zachowaniem tabel tymczasowych. Ta zmiana powoduje użycie rzeczywistej liczby kardynalnej zamiast oryginalnego szacowania jednego wiersza.

Aby włączyć kompilację odroczonej zmiennej tabeli, włącz poziom zgodności bazy danych 150 lub wyższy dla bazy danych, z którą nawiązano połączenie podczas uruchamiania zapytania.

Kompilacja odroczona zmiennej tabeli nie zmienia żadnych innych cech zmiennych tabeli. Na przykład ta funkcja nie dodaje statystyk kolumn do zmiennych tabeli.

Kompilacja odroczona zmiennej tabeli nie zwiększa częstotliwości ponownej kompilacji. Zamiast tego zmienia się miejsce, w którym następuje początkowa kompilacja. Wynikowy buforowany plan jest generowany na podstawie początkowej liczby wierszy zmiennej tabeli kompilacji odroczonej. Plan buforowany jest ponownie używany przez kolejne zapytania. Jest ponownie używany do czasu usunięcia lub ponownego skompilowania planu.

Liczba wierszy zmiennej tabeli używana do początkowej kompilacji planu reprezentuje typową wartość, która może się różnić od stałego przypuszczenia liczby wierszy. Jeśli jest inaczej, operacje podrzędne będą korzystne. Wydajność może nie zostać ulepszona przez tę funkcję, jeśli liczba wierszy zmiennej tabeli różni się znacząco w różnych wykonaniach.

Wyłączanie kompilacji odroczonej zmiennej tabeli bez zmiany poziomu zgodności

Wyłącz kompilację odroczoną zmiennej tabeli na poziomie bazy danych lub instrukcji, zachowując poziom zgodności bazy danych 150 lub wyższy. Aby wyłączyć kompilację odroczonej zmiennej tabeli dla wszystkich wykonań zapytań pochodzących z bazy danych, wykonaj poniższy przykład w kontekście odpowiedniej bazy danych:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

Aby ponownie włączyć kompilację odroczonej zmiennej tabeli dla wszystkich wykonań zapytań pochodzących z bazy danych, wykonaj następujący przykład w kontekście odpowiedniej bazy danych:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

Można również wyłączyć odroczoną kompilację zmiennej tabeli dla określonego zapytania, przypisując DISABLE_DEFERRED_COMPILATION_TV jako wskazówkę zapytania „USE HINT”. For example:

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE    O_ORDERKEY    =    L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

Optymalizacja planu wrażliwości parametrów

Dotyczy: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

Optymalizacja planu wrażliwości parametrów (PSP) jest częścią rodziny funkcji inteligentnego przetwarzania zapytań. Dotyczy to scenariusza, w którym pojedynczy buforowany plan zapytania sparametryzowanego nie jest optymalny dla wszystkich możliwych wartości parametrów przychodzących. Jest to przypadek w przypadku nieuniformowych dystrybucji danych.

• Aby uzyskać więcej informacji na temat optymalizacji planu wrażliwego na parametry, zobacz Optymalizacja planu wrażliwego na parametry.
• Aby uzyskać więcej informacji na temat parametryzacji i czułości parametrów, zobacz Czułość parametrów i Parametry i Ponowne Wykorzystanie Planu Wykonywania.

Przybliżone przetwarzanie zapytań

Przybliżone przetwarzanie zapytań to nowa rodzina funkcji. Agreguje się w dużych zestawach danych, w których czas odpowiedzi jest bardziej krytyczny niż bezwzględna precyzja. Przykładem jest obliczanie COUNT(DISTINCT()) wśród 10 miliardów wierszy do wyświetlenia na pulpicie nawigacyjnym. W takim przypadku bezwzględna precyzja nie jest ważna, ale czas odpowiedzi jest krytyczny.

Przybliżona liczba unikalnych

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)), Azure SQL Database

Nowa funkcja agregacji APPROX_COUNT_DISTINCT zwraca przybliżoną liczbę unikatowych wartości innych niż null w grupie.

Ta funkcja jest dostępna od programu SQL Server 2019 (15.x), niezależnie od poziomu zgodności.

Aby uzyskać więcej informacji, zobacz APPROX_COUNT_DISTINCT (Transact-SQL).

Approximate Percentile

Dotyczy: SQL Server (począwszy od programu SQL Server 2022 (16.x)), Azure SQL Database

Te funkcje agregujące obliczają percentyle dla dużego zestawu danych z akceptowalnymi granicami błędów opartymi na klasyfikacji, aby pomóc w podejmowaniu szybkich decyzji przy użyciu przybliżonych funkcji agregacji percentylu.

Aby uzyskać więcej informacji, zobacz APPROX_PERCENTILE_DISC (Transact-SQL) i APPROX_PERCENTILE_CONT (Transact-SQL)

Tryb wsadowy w przechowywaniu wierszy

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)), Azure SQL Database

Tryb wsadowy w magazynie wierszy umożliwia wykonywanie trybu wsadowego dla obciążeń analitycznych bez konieczności stosowania indeksów magazynu kolumn. Ta funkcja obsługuje wykonywanie trybu wsadowego i filtry bitmap dla stert na dysku i indeksów B-drzewa. Tryb wsadowy przy przechowywaniu wierszy umożliwia obsługę wszystkich operatorów obsługujących tryb wsadowy.

Omówienie wykonywania trybu wsadowego

Program SQL Server 2012 (11.x) wprowadził nową funkcję przyspieszania obciążeń analitycznych: indeksów magazynu kolumn. Przypadki użycia i wydajność indeksów magazynu kolumn wzrosły w każdej kolejnej wersji programu SQL Server. Tworzenie indeksów kolumnowych w tabelach może zwiększyć wydajność obciążeń analitycznych. Istnieją jednak dwa powiązane, ale odrębne zestawy technologii:

• W przypadku indeksów magazynu kolumn zapytania analityczne uzyskują dostęp tylko do danych w potrzebnych kolumnach. Kompresja strony w formacie magazynu kolumn jest również bardziej efektywna niż kompresja w tradycyjnych indeksach magazynu wierszy .
• Dzięki przetwarzaniu w trybie wsadowym operatorzy zapytań przetwarzają dane wydajniej. Działają one na zbiorze wierszy zamiast jednego wiersza jednocześnie. Wiele innych ulepszeń skalowalności jest powiązanych z przetwarzaniem trybu wsadowego. Aby uzyskać więcej informacji na temat trybu wsadowego, zobacz Tryby wykonywania.

Dwa zestawy funkcji współpracują ze sobą, aby poprawić wykorzystanie danych wejściowych/wyjściowych (we/wy) i procesora CPU:

• Korzystając z indeksów kolumnowych, więcej danych mieści się w pamięci. Zmniejsza to obciążenie I/O.
• Przetwarzanie w trybie wsadowym używa procesora CPU wydajniej.

Te dwie technologie korzystają ze siebie zawsze, gdy jest to możliwe. Na przykład agregacje w trybie wsadowym można ocenić w ramach skanowania indeksu kolumnowego. Ponadto dane magazynowane w kolumnach, które są skompresowane, są przetwarzane przy użyciu kodowania długości serii znacznie wydajniej w przypadku łączenia w trybie wsadowym i agregacji w trybie wsadowym.

Ważne jest jednak, aby zrozumieć, że te dwie funkcje są niezależne:

• Możesz uzyskać plany w trybie wierszy korzystające z indeksów kolumnowych.
• Możesz uzyskać plany trybu wsadowego, które używają tylko indeksów typu rowstore.

Zwykle uzyskujesz najlepsze wyniki, gdy używasz tych dwóch funkcji razem. Przed programem SQL Server 2019 (15.x) optymalizator zapytań programu SQL Server rozważał przetwarzanie trybu wsadowego tylko w przypadku zapytań obejmujących co najmniej jedną tabelę z indeksem magazynu kolumn.

Indeksy kolumnowe mogą nie być odpowiednie dla niektórych aplikacji. pl-PL: Aplikacja może używać niektórych innych funkcji, które nie są obsługiwane przez indeksy kolumnowe. Na przykład modyfikacje bezpośrednie nie są zgodne z kompresją magazynu danych kolumnowych. W związku z tym wyzwalacze nie są obsługiwane w tabelach z klastrowanym indeksowaniem magazynu kolumn. Co ważniejsze, indeksy kolumnowe powodują dodatkowe obciążenie instrukcji DELETE i UPDATE.

W przypadku niektórych hybrydowych obciążeń transakcyjno-analitycznych narzut obciążenia transakcyjnego przewyższa korzyści wynikające z używania indeksów kolumnowych. W takich scenariuszach można zyskać na lepszym wykorzystaniu procesora dzięki zastosowaniu samego przetwarzania w trybie wsadowym. Dlatego funkcja batch-mode-on-rowstore uwzględnia tryb wsadowy dla wszystkich zapytań niezależnie od typu indeksów.

Obciążenia, które mogą korzystać z trybu wsadowego w przechowywaniu danych wierszowych.

Następujące obciążenia mogą korzystać z trybu wsadowego na przechowywaniu wierszy:

• Znaczna część obciążenia składa się z zapytań analitycznych. Zazwyczaj te zapytania używają operatorów, takich jak sprzężenia lub agregacje, które przetwarzają setki tysięcy wierszy lub więcej.
• Obciążenie jest ograniczone przez procesor. Jeśli wąskie gardło to we/wy, nadal zaleca się rozważenie indeksu magazynu kolumn, jeśli jest to możliwe.
• Utworzenie indeksu kolumnowego powoduje zbyt duże obciążenie dla transakcyjnej części przetwarzania. Lub utworzenie indeksu magazynu kolumn nie jest możliwe, ponieważ aplikacja zależy od funkcji, która nie jest jeszcze obsługiwana w indeksach magazynu kolumn.

Jakie zmiany zachodzą w trybie wsadowym w przypadku magazynu wierszy?

Tryb wsadowy w przechowywaniu wierszy wymaga zgodności bazy danych na poziomie 150.

Nawet jeśli zapytanie nie uzyskuje dostępu do żadnych tabel z indeksami kolumnowymi, procesor zapytań stosuje heurystykę, aby zdecydować, czy rozważyć tryb wsadowy. Heurystyka składa się z następujących kontroli:

1. Wstępna kontrola rozmiarów tabel, używanych operatorów i szacowanych kardynalności w zapytaniu wejściowym.
2. Dodatkowe punkty kontrolne, gdy optymalizator odnajduje nowe, tańsze plany zapytania. Jeśli te alternatywne plany nie wykorzystują znacząco trybu wsadowego, optymalizator przestaje badać alternatywy w trybie wsadowym.

Jeśli używany jest tryb wsadowy na magazynie wierszy, w planie zapytania zostanie wyświetlony rzeczywisty tryb działania jako tryb wsadowy. Operator skanowania używa trybu wsadowego dla stosów na dysku i indeksów B-drzew. To skanowanie w trybie wsadowym może oceniać filtry map bitowych działające w tym trybie. W planie możesz również zauważyć inne operatory trybu wsadowego. Przykłady to łączniki z wykorzystaniem funkcji skrótu, agregacje z użyciem funkcji skrótu, sortowania, agregacje okienkowe, filtry, konkatenacja i operatory skalarne obliczeniowe.

Remarks

Plany zapytań nie zawsze używają trybu wsadowego. Optymalizator zapytań może uznać, że tryb wsadowy nie jest korzystny dla zapytania.

Zmienia się obszar wyszukiwania Optymalizator zapytań. Jeśli więc otrzymasz plan w trybie wiersza, może on nie być taki sam jak plan, który otrzymasz na niższym poziomie kompatybilności. Jeśli otrzymasz plan trybu wsadowego, może on różnić się od planu, który otrzymujesz z indeksem columnstore.

Plany mogą również ulec zmianie w przypadku zapytań, które mieszają indeksy kolumnowe i wierszowe, ze względu na nowe skanowanie wierszy w trybie wsadowym.

Istnieją bieżące ograniczenia dotyczące nowego trybu wsadowego podczas skanowania magazynu wierszy:

• Nie będzie on uruchamiany dla tabel OLTP w pamięci ani dla żadnego indeksu innego niż sterta dysku i drzewa B-trees.
• Nie zostanie również uruchomiona, jeśli zostanie pobrana lub przefiltrowana duża kolumna obiektu (LOB). To ograniczenie obejmuje rozrzedłe zestawy kolumn i kolumny XML.

Istnieją zapytania, dla których tryb wsadowy nie jest używany nawet w przypadku indeksów magazynu kolumn. Przykłady to zapytania obejmujące kursory. Te same wykluczenia rozszerzają się również na tryb wsadowy w magazynie wierszy.

Konfigurowanie trybu wsadowego w składnicy wierszy

Konfiguracja BATCH_MODE_ON_ROWSTOREo określonym zakresie bazy danych jest domyślnie włączona.

Możesz wyłączyć tryb wsadowy w magazynie danych wierszowych, nie zmieniając poziomu zgodności bazy danych.

-- Disabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

-- Enabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

Tryb wsadowy można wyłączyć w składowaniu wierszowym za pomocą konfiguracji w zakresie bazy danych. Można jednak nadal zastąpić ustawienie na poziomie zapytania, używając wskazówki zapytania ALLOW_BATCH_MODE. Poniższy przykład umożliwia tryb wsadowy w magazynie wierszy nawet z funkcją wyłączoną za pośrednictwem konfiguracji o określonym zakresie bazy danych:

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

Możesz również wyłączyć tryb wsadowy w magazynie wierszy dla określonego zapytania przy użyciu DISALLOW_BATCH_MODE wskazówki dotyczącej zapytania. Zobacz następujący przykład:

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));

Funkcje opinii dotyczących przetwarzania zapytań

Funkcje przetwarzania zapytań z informacją zwrotną są częścią rodziny inteligentnego przetwarzania zapytań.

Opinie dotyczące przetwarzania zapytań to proces, za pomocą którego procesor zapytań w programie SQL Server, usłudze Azure SQL Database i usłudze Azure SQL Managed Instance używa danych historycznych dotyczących wykonywania zapytania, aby zdecydować, czy zapytanie może otrzymać pomoc z co najmniej jednej zmiany sposobu kompilowania i wykonywania. Dane wydajności są zbierane w magazynie zapytań z różnymi sugestiami w celu ulepszenia wykonywania zapytań. W przypadku powodzenia zapisujemy te modyfikacje na dysku, w pamięci i/lub w magazynie zapytań do późniejszego użycia. Jeśli sugestie nie dają wystarczającej poprawy, zostaną one odrzucone, a zapytanie będzie nadal wykonywane bez tej opinii.

Aby uzyskać informacje na temat funkcji przetwarzania zapytań, które są dostępne w różnych wersjach programu SQL Server lub w usłudze Azure SQL Database lub Azure SQL Managed Instance, zobacz Inteligentne przetwarzanie zapytań w bazach danych SQL lub następujące artykuły dotyczące każdej funkcji opinii.

Informacja zwrotna o przydziale pamięci

Opinie dotyczące zapotrzebowania na pamięć zostały wprowadzone stopniowo w kolejnych dużych wersjach programu SQL Server.

Przekazywanie opinii o pamięci w trybie wsadowym

Aby uzyskać informacje na temat przekazywania opinii o pamięci trybu usługi Batch, odwiedź stronę Przekazywanie opinii o pamięci trybu usługi Batch.

Przekazywanie informacji zwrotnej o pamięci dla trybu wiersza

Aby uzyskać informacje na temat informacji zwrotnych dotyczących przydziału pamięci w trybie wierszowym, zobacz Informacje zwrotne dotyczące przydziału pamięci w trybie wierszowym.

Informacje zwrotne dotyczące przydziału pamięci w trybie percentylowym i trwałym

Aby uzyskać informacje o przekazywaniu opinii dotyczących alokacji pamięci w trybie percentyla i trwałości, odwiedź stronę Informacje o przekazywaniu opinii dotyczących alokacji pamięci w trybie percentyla i trwałości.

Stopień równoległości (DOP) — informacje zwrotne

Aby uzyskać informacje na temat informacji zwrotnej DOP, odwiedź stronę Stopień równoległości (DOP).

Informacja zwrotna na temat szacowania kardynalności (CE)

Aby uzyskać informacje na temat opinii CE, odwiedź stronę Opinia na temat szacowania kardynalności (CE).

Zoptymalizowane wymuszanie planu z użyciem magazynu zapytań

Aby uzyskać informacje na temat wymuszania zoptymalizowanego planu przy użyciu Magazynu Zapytań, odwiedź stronę Wymuszanie zoptymalizowanego planu z Magazynem Zapytań.

Zoptymalizowana ochrona Halloween

Dotyczy: SQL Server (począwszy od programu SQL Server 2025 (17.x) (wersja zapoznawcza)

Zoptymalizowana ochrona Halloween może zmniejszyć tempdb zużycie miejsca i poprawić wydajność zapytań, przeprojektując sposób, w jaki aparat bazy danych rozwiązuje problem Halloween. Ten problem występuje, gdy instrukcja języka modyfikacji danych (DML) zmienia dane w taki sposób, że ta sama instrukcja nieoczekiwanie przetwarza wiersz więcej niż raz.

Tradycyjnie aparat bazy danych chroni instrukcje DML przed problemem Halloween, wprowadzając operatorbuforu w planie zapytania lub korzystając z innego operatora blokującego, który już istnieje w planie, na przykład Sort lub Hash Match.

Jeśli jest używany operator Spool, tworzy tymczasową kopię danych do modyfikacji przed wprowadzeniem jakichkolwiek zmian w danych tabeli.

Chociaż spool ochronny unika problemu Halloween, ma swoje wady:

• Bufor wymaga dodatkowego miejsca w systemie , dodatkowe operacje we/wy tempdbdysku i procesora CPU do zapisu i odczytu danych oraz dodatkowej pamięci w celu buforowania danych.
• Przetwarzanie instrukcji zapytań przez podrzędne operatory zapytań jest blokowane, dopóki dane nie zostaną całkowicie zapisane w buforze.
• Bufor dodaje złożoność planu zapytania, co może sprawić, że optymalizator zapytań wygeneruje mniej wydajny plan.

Zoptymalizowana ochrona Halloween usuwa te wady, dzięki czemu operator Spool staje się zbędny.

Zoptymalizowana ochrona Halloween

W przypadku zapytania w celu korzystania ze zoptymalizowanej ochrony Halloween wymagane są następujące wymagania wstępne:

• Dla bazy danych należy włączyć przyspieszone odzyskiwanie bazy danych (ADR).
• Zapytanie musi zostać skompilowane na poziomie zgodności 170.
• Należy włączyć konfigurację OPTIMIZED_HALLOWEEN_PROTECTIONw zakresie bazy danych .

Konfiguracja OPTIMIZED_HALLOWEEN_PROTECTION o zakresie bazy danych jest włączona domyślnie. Oznacza to, że po włączeniu reguły ADR dla bazy danych przy użyciu poziomu zgodności 170 zapytania w bazie danych zaczynają domyślnie korzystać z zoptymalizowanej ochrony Halloween.

Aby upewnić się, że zapytania w bazie danych używają zoptymalizowanej ochrony Halloween, wykonaj następujące instrukcje:

ALTER DATABASE [<database-name-placeholder>] SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON;

Aby włączyć reguły ADR, wymagana jest wyłączna blokada bazy danych. Oznacza to, że polecenie ALTER DATABASE ... SET ACCELERATED_DATABASE_RECOVERY = ON jest zablokowane, gdy w bazie danych istnieją inne sesje, a wszystkie nowe sesje czekają na wykonanie tych ALTER DATABASE poleceń. Aby włączyć ADR bez oczekiwania, możesz dodać klauzulę zakończenia WITH ROLLBACK IMMEDIATE do polecenia ALTER DATABASE w celu przerwania jakichkolwiek aktywnych sesji w bazie danych.

Aby wyłączyć zoptymalizowaną ochronę Halloween dla bazy danych, wyłącz konfigurację OPTIMIZED_HALLOWEEN_PROTECTION o zakresie bazy danych:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = OFF;

Zastosuj zoptymalizowaną ochronę Halloween poprzez podpowiedzi dotyczące zapytań

Możesz użyć ENABLE_OPTIMIZED_HALLOWEEN_PROTECTION i DISABLE_OPTIMIZED_HALLOWEEN_PROTECTION jako wskazówek dotyczących zapytań, aby włączyć i wyłączyć zoptymalizowaną ochronę „Halloween” dla danego zapytania. Wskazówki muszą być określone za pomocą klauzuli USE HINT . Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań (Transact-SQL).

Wskazówki działają na dowolnym poziomie zgodności i zastępują konfigurację ograniczoną do zakresu bazy danych OPTIMIZED_HALLOWEEN_PROTECTION.

ENABLE_OPTIMIZED_HALLOWEEN_PROTECTION i DISABLE_OPTIMIZED_HALLOWEEN_PROTECTION można określić bezpośrednio w zapytaniu lub za pomocą wskazówek Magazynu Zapytań.

Zoptymalizowana właściwość ochrony Halloween w planie zapytania

Gdy zoptymalizowana ochrona Halloween jest używana dla operatora w planie zapytania, właściwość OptimizedHalloweenProtectionUsed operatora w planie zapytania XML jest ustawiona na True.

Jak działa zoptymalizowana ochrona Halloween

Po włączeniu reguły ADR każda instrukcja w transakcji uzyskuje unikatowy identyfikator instrukcji, znany jako identyfikator zagnieżdżenia. Każdy wiersz zmodyfikowany przez instrukcję DML jest oznaczany identyfikatorem gniazda tej instrukcji. Jest to wymagane do zapewnienia semantyki transakcji ACID z ADR .

Podczas przetwarzania instrukcji DML, gdy silnik bazy danych odczytuje dane, pomija każdy wiersz, który ma ten sam identyfikator zagnieżdżenia co bieżąca instrukcja DML. Oznacza to, że procesor zapytań nie widzi wierszy już przetworzonych przez instrukcję, unikając problemu Halloween.

Ograniczenia zoptymalizowanej ochrony Halloween

Zoptymalizowana ochrona Halloween nie jest obecnie używana w następujących przypadkach:

• W instrukcji DML, która modyfikuje
  • Tabela tymczasowa, zmienna tabeli lub tabela nietemporarna tempdb w bazie danych.
  • Tabela zawierająca klastrowany lub nieklastrowany indeks kolumnowy.
  • Tabela grafowa lub tabela księgi.
  • Widok utworzony za pomocą opcji CHECK .
  • Tabela, gdy tabela lub kolumna w tej tabeli mają ograniczenie sprawdzania, które używa zdefiniowanej przez użytkownika funkcji skalarnej.
  • Tabela zawierająca zasady zabezpieczeń na poziomie wiersza z blokiem lub predykatem filtrującym.
  • Tabela zawierająca kolumnę z atrybutem FILESTREAM .
• Instrukcje DELETE i MERGE, które zawierają klauzulę OUTPUT.
• Instrukcje MERGE i UPDATE ... FROM, które mogą wielokrotnie modyfikować ten sam wiersz.

We wszystkich tych przypadkach zamiast tego zastosowana jest tradycyjna ochrona Halloween przy użyciu operatora Spool (lub innego operatora blokującego, jeśli jest już obecny).

Jeśli ta sama instrukcja DML modyfikuje wiele tabel w różnych bazach danych, zoptymalizowana ochrona Halloween jest używana tylko dla operatorów modyfikujących tabele w bazach danych, w których włączono zoptymalizowaną ochronę Halloween. Na przykład zarówno zoptymalizowana, jak i tradycyjna ochrona Halloween może wystąpić w instrukcji DML komponowalnej z klauzulą OUTPUT wstawiania danych do tabeli w innej bazie danych.

Related content

• Sprzężenia (SQL Server)
• Execution modes
• przewodnik po architekturze przetwarzania zapytań
• Odwołanie do operatorów logicznych i fizycznych Showplan
• ZMIEŃ KONFIGURACJĘ ZAKRESU BAZY DANYCH (Transact-SQL)
• Co nowego w programie SQL Server 2017
• Co nowego w programie SQL Server 2019
• Co nowego w programie SQL Server 2022
• Przedstawianie Inteligentnego Przetwarzania Zapytan
• Stałe składanie i obliczanie wyrażeń
• Pokazy inteligentnego przetwarzania zapytań w usłudze GitHub
• Performance Center dla aparatu bazy danych SQL Server i usługi Azure SQL Database
• Monitorowanie wydajności za pomocą magazynu zapytań
• Najlepsze rozwiązania dotyczące monitorowania obciążeń za pomocą magazynu zapytań