Udostępnij za pomocą

Opcjonalna optymalizacja planu parametrów (OPPO)

Dotyczy:: SQL Server 2025 (17.x) Azure SQL DatabaseSQL database w usłudze Microsoft Fabric

Termin parametry opcjonalne odnosi się do konkretnej odmiany problemu planu wrażliwego na parametr (PSP), w którym wrażliwa wartość parametru, która istnieje podczas wykonywania zapytania, określa, czy musimy wykonać wyszukiwanie, czy przeskanować tabelę. Prosty przykład wygląda następująco:

SELECT column1,
       column2
FROM Table1
WHERE (column1 = @p
      OR @p IS NULL);

W tym przykładzie program SQL Server zawsze wybiera plan, który skanuje tabelęTable1, nawet jeśli istnieje indeks na .Table1(col1) Plan wyszukiwania może być niemożliwy z wartościami NULL. Techniki wskazówek dotyczących zapytań, takie jak OPTIMIZE FOR, mogą nie być przydatne w przypadku tego typu problemu PSP, ponieważ obecnie nie istnieje operator, który dynamicznie przekształca wyszukiwanie w indeksie na skanowanie podczas wykonywania. Tego rodzaju kombinacja wyszukiwania i skanowania w czasie wykonywania może również nie być skuteczna, ponieważ szacunki kardynalności na podstawie tego operatora prawdopodobnie będą niedokładne. Wynikiem są nieefektywne wybory planu i nadmierne przydziały pamięci dla bardziej złożonych zapytań z podobnymi wzorcami zapytań.

Funkcja optymalizacji planu opcjonalnych parametrów (OPPO) korzysta z infrastruktury adaptacyjnej optymalizacji planu (Multiplan), która została wprowadzona wraz z ulepszeniem optymalizacji planu wrażliwego na parametry, generując wiele planów z jednej instrukcji. Dzięki temu funkcja może stosować różne założenia w zależności od wartości parametrów używanych w zapytaniu. W czasie wykonywania zapytania, OPPO wybiera odpowiedni plan:

  • gdzie wartość parametru IS NOT NULL, używa planu przeszukiwania lub czegoś bardziej optymalnego niż plan pełnego skanowania.
  • gdzie wartość parametru to NULL, używa planu skanowania.

W ramach rodziny funkcji optymalizacji planu adaptacyjnego, która obejmuje optymalizację planu wrażliwego na parametry, OPPO oferuje rozwiązanie drugiego komponentu zestawu funkcji Multiplan, co obejmuje możliwości dynamicznego wyszukiwania.

  • Predykaty równości

    WHERE column1 = @p

  • Wyszukiwanie dynamiczne

    WHERE (column1 = @p1 OR @p1 IS NULL)
  AND (column2 = @p2 OR @p2 IS NOT NULL)

Terminologia i sposób jej działania

Term Description
Wyrażenie dyspozytora To wyrażenie ocenia kardynalność predykatów na podstawie wartości parametrów środowiska uruchomieniowego i kieruje wykonywanie do różnych wariantów zapytania.
Plan dyspozytora Plan zawierający wyrażenie dyspozytora jest buforowany dla oryginalnego zapytania. Plan dyspozytora jest zasadniczo kolekcją predykatów wybranych przez tę funkcję, z kilkoma dodatkowymi szczegółami. Dla każdego predykatu wybranego niektóre szczegóły zawarte w planie dyspozytora to wysokie i niskie wartości graniczne. Te wartości są używane do dzielenia wartości parametrów na różne zasobniki lub zakresy. Plan dyspozytora zawiera również statystyki, które zostały użyte do obliczenia wartości granic.
Wariant zapytania Ponieważ plan dyspozytora ocenia kardynalność predykatów na podstawie wartości parametrów środowiska uruchomieniowego, grupuje je i generuje oddzielne zapytania podrzędne do wykonania. Te zapytania podrzędne są nazywane wariantami zapytań. Warianty zapytań mają własne plany w pamięci podręcznej planu i magazynie zapytań. Innymi słowy, używając różnych wariantów zapytań, osiągamy cel wielu planów dla pojedynczego zapytania.

Rozważmy na przykład formularz aplikacji internetowej dla firmy nieruchomościowej, który umożliwia opcjonalne filtrowanie według liczby sypialni dla określonej oferty. Typowym antywzorzecem może być wyrażenie opcjonalnego filtru jako:

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

Nawet jeśli parametr @bedrooms = 10 jest analizowany za pomocą znaczników parametrów i wiemy, że kardynalność dla liczby sypialni jest prawdopodobnie bardzo niska, optymalizator nie tworzy planu, który korzysta z indeksu istniejącego w kolumnie sypialni, ponieważ nie jest to prawidłowy plan dla sytuacji, gdy @bedrooms jest NULL. Wygenerowany plan nie obejmuje skanowania indeksu.

Wyobraź sobie, że można to napisać ponownie jako dwie oddzielne instrukcje. W zależności od wartości parametru w czasie działania, możemy ocenić coś takiego:

IF @bedrooms IS NULL
    SELECT * FROM Properties;
ELSE
    SELECT * FROM Properties
    WHERE bedrooms = @bedrooms;

Możemy to osiągnąć przy użyciu infrastruktury optymalizacji planu adaptacyjnego, która umożliwia utworzenie planu dyspozytora wysyłającego dwa warianty zapytania.

Podobnie jak w przypadku zakresu kardynalności predykatu używanego przez optymalizację PSP, OPPO osadza użyteczną dla systemu wskazówkę zapytania wraz z tekstem zapytania planu. Ta wskazówka nie jest prawidłowa do użycia przez aplikację lub jeśli próbujesz użyć jej samodzielnie.

Kontynuując poprzedni przykład,

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

Obiekt OPPO może wygenerować dwa warianty zapytania, które w pliku XML programu Showplan mogą mieć do nich dodane następujące atrybuty:

  • Parametr @bedrooms ma wartość NULL. Wariant zapytania zmodyfikował oryginalne zapytanie, aby osiągnąć plan skanowania.

    SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms jest NULL))

  • @bedrooms IS NOT NULL

    SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms jest NULL))

Korzystanie z opcjonalnej optymalizacji planu parametrów

Aby włączyć OPPO dla bazy danych, wymagane są następujące warunki wstępne:

  • Baza danych musi używać poziomu zgodności 170.
  • Należy włączyć konfigurację OPTIONAL_PARAMETER_OPTIMIZATION powiązaną z bazą danych.

Konfiguracja OPTIONAL_PARAMETER_OPTIMIZATION o zakresie bazy danych jest włączona domyślnie. Oznacza to, że baza danych korzystająca z poziomu zgodności 170 (domyślnie w programie SQL Server 2025) domyślnie używa obiektu OPPO.

Aby zapewnić, że baza danych używa obiektu OPPO w programie SQL Server 2025, wykonaj następujące instrukcje:

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

Aby wyłączyć opcjonalną optymalizację planu parametrów dla bazy danych, wyłącz konfigurację OPTIONAL_PARAMETER_OPTIMIZATION o zakresie bazy danych:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Korzystanie z opcjonalnej optymalizacji planu parametrów za pomocą wskazówek dotyczących zapytań

Możesz użyć DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION wskazówki dotyczącej zapytania, aby wyłączyć opcjonalną optymalizację planu parametrów 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ń.

Wskazówki działają na dowolnym poziomie zgodności i zastępują konfigurację bazy danych ustawioną za pomocą OPTIONAL_PARAMETER_OPTIMIZATION.

Wskazówkę DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION dotyczącą zapytania można określić bezpośrednio w zapytaniu lub za pomocą wskazówek magazynu zapytań.

Zdarzenia rozszerzone

  • optional_parameter_optimization_skipped_reason: Występuje, gdy OPPO postanawia, że zapytanie nie kwalifikuje się do optymalizacji. To rozszerzone zdarzenie jest zgodne z tym samym wzorcem co zdarzenie o nazwie parameter_sensitive_plan_optimization_skipped_reason, które jest używane przez optymalizację PSP. Ponieważ zapytanie może generować zarówno optymalizację PSP, jak i warianty zapytań OPPO, należy sprawdzić oba zdarzenia, aby zrozumieć, dlaczego jedna lub żadna funkcja nie jest aktywna.

    Poniższe zapytanie pokazuje wszystkie możliwe powody, dla których PSP został pominięty:

    SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;

  • query_with_optional_parameter_predicate: Zdarzenie rozszerzone podąża tym samym wzorcem co zdarzenie query_with_parameter_sensitivity używane przez optymalizację PSP. Zawiera dodatkowe pola dostępne w ulepszonym procesie optymalizacji PSP, obejmujące wyświetlanie liczby predykatów, które zostały uznane za interesujące, dodatkowe szczegóły w formacie JSON dotyczące tych predykatów, a także informację, czy OPPO jest obsługiwane dla danego predykatu lub predykatów.

Remarks

  • Kod XML ShowPlan dla wariantu zapytania będzie wyglądać podobnie do poniższego przykładu, w którym wybrane predykaty mają odpowiednie informacje dodane do wskazówki PLAN PER VALUE, optional_predicate.
<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">
  • Przykładowe dane wyjściowe z zdarzenia rozszerzonego query_with_optional_parameter_predicate
Field Value
optional_parameter_optimization_supported True
optional_parameter_predicate_count 3
predicate_details {"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}
query_type 193

Treści powiązane

  • Last updated on