Optymalizacja planu wrażliwego na parametry

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL database w usłudze Microsoft Fabric

Optymalizacja planu wrażliwego na parametry (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. Dzieje się tak w przypadku nieuprzyzwoitych dystrybucji danych. Aby uzyskać więcej informacji, zobacz Czułość parametrów i Parametry i ponowne użycie planu wykonywania.

Aby uzyskać więcej informacji na temat istniejących obejść tego scenariusza problemu, zobacz:

• Badanie i rozwiązywanie problemów z uwzględnieniem parametrów
• Ponowne użycie parametrów i planu wykonania
• Zapytania, które mają problemy z planem wrażliwym na parametry (PSP)

Optymalizacja PSP automatycznie włącza wiele aktywnych planów buforowanych dla pojedynczej instrukcji sparametryzowanej. Buforowane plany wykonywania mają różne rozmiary danych na podstawie wartości parametrów środowiska uruchomieniowego dostarczonego przez klienta.

Zrozumienie parametryzacji

W aparacie bazy danych SQL Server używanie parametrów lub znaczników parametrów w instrukcjach Transact-SQL (T-SQL) zwiększa zdolność silnika relacyjnego do dopasowywania nowych instrukcji T-SQL do istniejących, wcześniej skompilowanych planów wykonywania i promuje ponowne użycie planu. Aby uzyskać więcej informacji, zobacz Simple Parameterization (Prosta parametryzacja).

Można również zmienić domyślne ustawienia prostej parametryzacji w programie SQL Server, określając, że wszystkie SELECT, INSERT, UPDATE i DELETE instrukcje w bazie danych są sparametryzowane, przy uwzględnieniu pewnych ograniczeń. Aby uzyskać więcej informacji, zobacz Wymuszone parametryzacja.

Implementacja optymalizacji PSP

Podczas początkowej kompilacji histogramy statystyk kolumn identyfikują niejednolite rozkłady i oceniają sparametryzowane predykaty, które są najbardziej zagrożone, maksymalnie trzy spośród wszystkich dostępnych predykatów. Innymi słowy, jeśli wiele predykatów w ramach tego samego zapytania spełnia kryteria, optymalizacja PSP wybiera trzy pierwsze. Funkcja PSP ogranicza liczbę ocenianych predykatów, aby uniknąć przeciążenia pamięci podręcznej planu oraz magazynu zapytań (jeśli magazyn zapytań jest włączony) zbyt dużą liczbą planów.

W przypadku kwalifikujących się planów początkowa kompilacja tworzy plan dyspozytora , który zawiera logikę optymalizacji PSP nazywaną wyrażeniem dyspozytora. Plan dyspozytora mapuje warianty zapytań na podstawie predykatów granic zakresu kardynalności.

Terminology

Zapytanie nadrzędne

Oryginalne parametryczne zapytanie, dla którego optymalizacja PSP generuje plan dyspozytora i warianty zapytań. Warianty zapytań to zapytania potomne zapytania nadrzędnego. Query Store może przechowywać więcej niż jeden plan dyspozytora dla tego samego zapytania nadrzędnego w czasie.

Wyrażenie dyspozytora

Ocenia kardynalność predykatów na podstawie wartości parametrów czasu wykonania i kieruje wykonanie do różnych wariantów zapytania.

Plan dyspozytora

Plan zawierający wyrażenie dyspozytora jest buforowany dla oryginalnego parametryzowanego zapytania, znanego również jako zapytanie nadrzędne. Plan dyspozytora to w zasadzie zbiór predykatów wybranych przez daną funkcję, z kilkoma dodatkowymi szczegółami. Dla każdego wybranego predykatu plan dyspozytora zawiera wysokie i niskie wartości graniczne. Wartości te dzielą wartości parametrów na różne przedziały lub zakresy. Plan dyspozytora zawiera również statystyki, które zostały użyte do obliczenia wartości granic.

Wariant zapytania

Gdy plan dyspozytora ocenia kardynalność predykatów w oparciu o wartości parametrów środowiska uruchomieniowego, grupuje te wartości i generuje oddzielne zapytania podrzędne do kompilacji i wykonania. Te zapytania podrzędne są nazywane wariantami zapytań. Warianty zapytań mają własne plany w cache planów i magazynie zapytań.

Zakres kardynalności predykatu

W czasie wykonywania kardynalność każdego predykatu jest oceniana na podstawie wartości parametrów środowiska uruchomieniowego. Dyspozytor grupuje wartości kardynalności w trzy zakresy kardynalności warunków w czasie kompilacji. Na przykład funkcja optymalizacji PSP może tworzyć trzy zakresy, które reprezentują zakresy niskiej, średniej i wysokiej kardynalności, jak pokazano na poniższym diagramie.

Innymi słowy, gdy zapytanie sparametryzowane jest początkowo kompilowane, funkcja optymalizacji PSP generuje plan szkieletu określany jako plan dyspozytora. Wyrażenie dyspozytora ma logikę, która dzieli zapytania na warianty zapytań na podstawie wartości parametrów w czasie działania. Po rozpoczęciu rzeczywistego wykonywania dyspozytor wykonuje dwa kroki:

• Dyspozytor analizuje swoje wyrażenie dotyczące danego zestawu parametrów w celu wyznaczenia przedziału kardynalności.

• Dyspozytor mapuje te zakresy na określone warianty zapytań, które następnie kompiluje i wykonuje. Dzięki zastosowaniu wielu wariantów zapytań funkcja optymalizacji PSP osiąga wiele planów dla pojedynczego zapytania.

Granice zakresu kardynalności można zobaczyć w pliku XML ShowPlan planu wysyłki:

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

Wygenerowana wskazówka dotycząca optymalizacji PSP jest dołączana do instrukcji SQL w kodzie XML ShowPlan wariantu zapytania. Wskazówka nie może być używana bezpośrednio i nie jest analizowana w przypadku ręcznego dodania. Wskazówka zawiera następujące elementy:

opcja ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [tableName]. [columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )

• ObjectID pochodzi z modułu (czyli procedury składowanej, funkcji, wyzwalacza), której częścią jest bieżąca instrukcja; z założeniem, że instrukcja została wygenerowana na podstawie modułu. Jeśli instrukcja jest wynikiem dynamicznego lub ad hoc SQL (tj. sp_executesql) element ObjectID równa 0.
• QueryVariantID jest w przybliżeniu odpowiednikiem kombinacji zakresów dla wszystkich predykatów wybranych przez optymalizację PSP. Na przykład jeśli zapytanie ma dwa predykaty, które kwalifikują się do PSP, a każdy predykat ma trzy zakresy, będzie dziewięć zakresów wariantów zapytania numerowanych 1–9.
• zakres predykatów to wstępnie określony zakres kardynalności wygenerowany na podstawie wyrażenia dyspozytora.

Ponadto w pliku ShowPlan XML wariantu zapytania (wewnątrz elementu Dispatcher):

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

Remarks

• Począwszy od programu SQL Server 2025 (17.x) i zgodności bazy danych 170, optymalizacja PSP obejmuje następujące cztery ulepszenia:

  • Obsługa instrukcji języka manipulacji danymi (DML), takich jak DELETE, INSERT, MERGE, oraz UPDATE.
  • Rozszerzona obsługa programu tempdb.
  • Wsparcie dla wyboru wielu kwalifikujących się predykatów z tej samej tabeli, z maksymalnie jednym predykatem wybranym na kolumnę.
  • Zmiany w zdarzeniu rozszerzonym query_with_parameter_sensitivity, które obejmowały pola interesting_predicate_count, max_skewness, psp_optimization_supported i query_type przed zmianami w SQL Server 2025 (17.x) i zgodności bazy danych 170. Jednak teraz zawiera pola „interesting_predicate_count”, „interesting_predicate_details”, „psp_optimization_supported” i „query_type”. Aby uzyskać więcej informacji, zobacz sekcję Zdarzenia rozszerzone .

• Funkcja optymalizacji PSP obecnie działa tylko z predykatami równości.

• Plany dyspozytora są automatycznie odbudowywane, jeśli występują znaczące zmiany w dystrybucji danych. Plany wariantów zapytań są kompilowane niezależnie w miarę potrzeb, podobnie jak każdy inny typ planu zapytania, zgodnie z domyślnymi zdarzeniami ponownej kompilacji. Aby uzyskać więcej informacji na temat ponownej kompilacji, zobacz Ponowne komkompilowanie planów wykonania.

• Systemowy widok katalogu magazynu zapytań sys.query_store_plan został zmieniony w celu odróżnienia normalnego skompilowanego planu, planu dyspozytora i planu wariantu zapytania. Nowy widok katalogu systemowego Query Store, sys.query_store_query_variant, zawiera informacje o hierarchicznych relacjach między oryginalnymi zapytaniami sparametryzowanymi (nazywanymi również zapytaniami nadrzędnymi), planami dyspozytora, oraz ich wariantami zapytań podrzędnych.

• Na poziomie zgodności bazy danych 160, gdy istnieje wiele kwalifikujących się predykatów będących częścią odwołania do tej samej tabeli, optymalizacja PSP wybiera predykat, który ma najbardziej nierównomierny rozkład danych na podstawie bazowego histogramu statystyk. Na przykład w przypadku SELECT * FROM table1 WHERE column1 = @predicate1 AND column2 = @predicate2 wybierany jest tylko najbardziej nierównomierny predykat, ponieważ oba predykaty odnoszą się do tej samej tabeli. Jednak jeśli zapytanie odnosi się do tabeli więcej niż raz, na przykład w oddzielnych gałęziach UNION lub poprzez aliasy w sprzężeniu własnym, optymalizacja PSP może wybrać predykaty z więcej niż jednej referencji do tabeli.

• Począwszy od SQL Server 2025 (17.x) i poziomu zgodności bazy danych 170 optymalizacja PSP może wybrać wiele kwalifikujących się predykatów z tej samej tabeli. Wybierany jest tylko jeden predykat na kolumnę, a łączna liczba predykatów wybranych dla zapytania pozostaje ograniczona do trzech. Na przykład, przy SELECT * FROM table1 WHERE column1 = @predicate1 AND column2 = @predicate2, oba predykaty można wybrać, jeśli spełniają kryteria optymalizacji PSP.

• Kod XML programu ShowPlan dla wariantu zapytania będzie wyglądał podobnie do poniższego przykładu, gdzie oba predykaty, które zostały wybrane, mają dołączone odpowiednio swoje informacje do powiązanej wskazówki PLAN PER VALUE PSP.

  <Batch>
    <Statements>
      <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
        <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
        <Dispatcher>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
        </Dispatcher>
        <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">
  

• Można wpływać na bieżące progi niesymetryczności używane przez funkcję optymalizacji PSP przy użyciu co najmniej jednej z następujących metod:

  • Narzędzie do szacowania kardynalności (CE) z flagami śledzenia, takimi jak flaga śledzenia 9481 (poziom globalny, sesji lub zapytania)

  • Opcje konfiguracji w zakresie bazy danych, które próbują obniżyć używany model CE lub wpłynąć na założenia, które model CE stosuje w odniesieniu do niezależności wielu predykatów. Jest to szczególnie przydatne w przypadkach, gdy nie istnieją statystyki wielokolumnowe, co wpływa na zdolność optymalizacji PSP do oceny zasadności tych predykatów.

  • Aby uzyskać więcej informacji, zobacz sekcję Zwiększone założenie korelacji dla wielu predykatów w oficjalnym dokumencie Optymalizowanie planów zapytań przy użyciu narzędzia do szacowania kardynalności programu SQL Server 2014 . Nowszy model CE próbuje założyć pewną korelację i mniejszą niezależność dla koniunkcji i alternatywy predykatów. Użycie starszego modelu CE może mieć wpływ na to, jak można obliczyć selektywność predykatów w scenariuszu sprzężenia wielokolumnowego. Ta akcja powinna być uwzględniana tylko w określonych scenariuszach i nie zaleca się używania starszego modelu CE dla większości obciążeń.

• Optymalizacja PSP kompiluje i wykonuje każdy wariant zapytania jako osobną przygotowaną instrukcję. Plany wariantów zapytań mogą być buforowane i ponownie wykorzystane, więc nie jest wymagana nowa kompilacja przy każdym wykonaniu. Ponieważ warianty zapytań to oddzielne przygotowane instrukcje, ich wartości pamięci podręcznej planu object_id nie identyfikują modułu nadrzędnego, jeśli zapytanie nadrzędne jest częścią modułu (czyli procedury przechowywanej, wyzwalacza, funkcji itd.). Dla przygotowanej instrukcji plan cache object_id to zasadniczo wartość obliczona na podstawie wewnętrznego skrótu tekstu wsadowego, a nie identyfikatora obiektu w sys.objects. Aby uzyskać więcej informacji, zobacz sekcję Tabela Zwrócona w dokumentacji DMV.

  Plany wariantów zapytań są umieszczane w magazynie pamięci podręcznej SQL Plans (CACHESTORE_SQLCP). Plan dyspozytora pozostaje częścią oryginalnego planu wsadowego lub modułu zapisanego w pamięci podręcznej. Jeśli zapytanie nadrzędne jest częścią procedury przechowywanej lub innego modułu, którego skompilowany plan jest przechowywany w pamięci podręcznej planów obiektów (CACHESTORE_OBJCP), plan dyspozycji jest przechowywany w CACHESTORE_OBJCP. Jeśli zapytanie nadrzędne jest częścią dynamicznego kodu SQL z parametrami lub kwalifikującej się partii ad hoc, plan rozsyłający jest przechowywany w CACHESTORE_SQLCP.

  Funkcja PSP przechowuje object_id modułu nadrzędnego wariantu zapytania w atrybucie ObjectID, będącym częścią podpowiedzi PLAN PER VALUE w kodzie XML ShowPlan. Jeśli zapytanie nadrzędne jest dynamiczne lub ad hoc T-SQL, ten atrybut to 0. Agregacja statystyk wydajności dla buforowanych procedur, funkcji i wyzwalaczy może być nadal używana w odpowiednich celach. Bardziej szczegółowe statystyki związane z realizacją, takie jak te z sys.dm_exec_query_stats DMV, nadal zawierają dane dotyczące wariantów zapytań. Jednak powiązanie tych statystyk z modułem nadrzędnym wymaga dodatkowego przetwarzania kodu XML ShowPlan, ponieważ pamięć podręczna planu wariantu zapytania object_id nie identyfikuje bezpośrednio modułu nadrzędnego. Statystyki czasu wykonywania i oczekiwania dla wariantów zapytań można uzyskać ze Query Store bez konieczności dodatkowego parsowania XML w ShowPlan, jeśli Query Store jest włączony.

• ID obiektu modułu nadrzędnego nie jest bezpośrednio udostępniane jako object_id wariantu zapytania w DMV związanych z sys.dm_exec_* pamięcią podręczną planu. Buforowany plan modułu zawierający dyspozytor zachowuje identyfikator obiektu modułu. W przypadku wariantów zapytań identyfikator obiektu modułu nadrzędnego można uzyskać z atrybutu ObjectID w podpowiedce PLAN PER VALUE , przetwarzając plik XML ShowPlan. Query Store dostarcza model relacyjny do identyfikacji tych relacji. Widok katalogowy sys.query_store_query_variant identyfikuje oryginalne parametryzowane zapytanie za pomocą parent_query_id, plan dyspozytora za pomocą dispatcher_plan_id oraz zapytanie potomne za pomocą query_variant_query_id.

Considerations

• Aby włączyć optymalizację PSP, użyj poziomu kompatybilności bazy danych 160 lub wyższego dla bazy danych, do której jesteś podłączony podczas wykonywania zapytania. Ulepszenia optymalizacji PSP wprowadzone w SQL Server 2025 (17.x) wymagają poziomu kompatybilności baz danych 170 lub wyższego.

• Aby uzyskać dodatkowe informacje na temat funkcji optymalizacji PSP, zalecamy włączenie magazynu zapytań, co umożliwi integrację z jego funkcjami. Poniższy przykład włącza magazyn zapytań dla wstępnie istniejącej bazy danych o nazwie MyNewDatabase:

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

• Aby wyłączyć optymalizację PSP na poziomie bazy danych, użyj ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF konfiguracji zakresu bazy danych.

• Aby wyłączyć optymalizację PSP na poziomie zapytania, użyj DISABLE_PARAMETER_SENSITIVE_PLAN wskazówki zapytania.

• Jeśli wyczuwanie parametrów jest wyłączone przez flagę śledzenia 4136, konfigurację w zakresie bazy danych PARAMETER_SNIFFING lub wskazówkę zapytania USE HINT('DISABLE_PARAMETER_SNIFFING'), optymalizacja PSP jest wyłączona dla związanych z tym obciążeń i kontekstów wykonania. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań i ALTER DATABASE SCOPED CONFIGURATION.

• Liczba unikatowych wariantów planu przypadających na dyspozytora przechowywanych w pamięci podręcznej jest ograniczona, aby uniknąć przeciążenia pamięci podręcznej. Próg wewnętrzny nie jest udokumentowany. Ponieważ każdy zestaw SQL ma potencjał tworzenia wielu planów, a każdy plan wariantu zapytania ma niezależny wpis w pamięci podręcznej planu, możliwe jest osiągnięcie maksymalnej domyślnej liczby dozwolonych wpisów planu. Jeśli współczynnik eksmisji pamięci podręcznej planu jest zauważalnie wysoki lub rozmiary CACHESTORE_OBJCPCACHESTORE_SQLCP i są nadmierne, należy rozważyć zastosowanie flagi śledzenia 174.

• Liczba unikatowych wariantów planu przechowywanych dla zapytania w magazynie zapytań jest ograniczona przez opcję konfiguracji max_plans_per_query. Ponieważ warianty zapytań mogą mieć więcej niż jeden plan, w Query Store (magazynie zapytań) może znajdować się łącznie 200 planów na zapytanie. Ta liczba obejmuje wszystkie plany wariantów zapytania dla wszystkich dyspozytorów powiązanych z zapytaniem nadrzędnym. Rozważ zwiększenie max_plans_per_query opcji konfiguracji magazynu zapytań.

  • Przykładem sytuacji, w której liczba unikatowych planów może przekroczyć domyślny limit magazynu max_plans_per_query zapytań, jest scenariusz charakteryzujący się następującym zachowaniem. Załóżmy, że masz zapytanie z identyfikatorem zapytania 10, który ma dwa plany dyspozytora, a każdy plan dyspozytora ma 20 wariantów zapytań (łącznie 40 wariantów zapytań). Całkowita liczba planów dla identyfikatora zapytania 10 to 40 planów dla wariantów zapytania i dwóch planów dyspozytora. Możliwe jest również, że samo zapytanie nadrzędne (identyfikator zapytania 10) może mieć 5 zwykłych (innych niż dyspozytor) planów. Dzięki temu mamy 47 planów (40 z wariantów zapytań, 2 dotyczące dyspozytorów oraz 5 planów niezwiązanych z PSP). Ponadto jeśli każdy wariant zapytania ma również średnio pięć planów, w tym scenariuszu można mieć więcej niż 200 planów w magazynie zapytań dla zapytania nadrzędnego. Może to również zależeć od dużych niesymetryczności danych w zestawach danych, do których może się odwoływać to przykładowe zapytanie nadrzędne.

• Dla każdego przypisania wariantu zapytania do określonego dyspozytora:

  • Element query_plan_hash jest unikatowy. Ta kolumna jest dostępna w sys.dm_exec_query_stats, w dynamicznych widokach zarządzania oraz w tabelach katalogowych.
  • Element plan_handle jest unikatowy. Ta kolumna jest dostępna w sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans oraz w innych dynamicznych widokach zarządzania i funkcjach, a także tabelach katalogowych.
  • Element query_hash jest wspólny dla innych wariantów mapujących do tego samego dyspozytora, więc można określić łączne użycie zasobów dla zapytań, które różnią się tylko wartościami parametrów wejściowych. Ta kolumna jest dostępna w sys.dm_exec_query_stats, sys.query_store_query i innych dynamicznych widokach zarządzania oraz tabelach katalogowych.
  • Element sql_handle jest unikatowy ze względu na dodanie specjalnych identyfikatorów optymalizacji PSP do tekstu zapytania podczas kompilacji. Ta kolumna jest dostępna w sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans oraz w innych dynamicznych widokach zarządzania i funkcjach, a także tabelach katalogowych. Te same informacje o uchwycie są dostępne w magazynie zapytań jako kolumna last_compile_batch_sql_handle w tabeli katalogowej sys.query_store_query.
  • Element query_id jest unikatowy w magazynie zapytań SQL. Ta kolumna jest dostępna w sys.query_store_query i innych tabelach katalogu Query Store.

Planowanie wymuszania w magazynie zapytań

Używa tych samych procedur składowanych sp_query_store_force_plan i sp_query_store_unforce_plan do obsługi planów dyspozytora lub wariantu.

Jeśli wymuszasz wariant planu zapytania, nie wymuszasz powiązanego dispatcher plan. Jeśli dyspozytor jest zmuszony, tylko warianty z tego dyspozytora są uznawane za kwalifikujące się do użycia:

• Wcześniej wymuszone warianty z innych dyspozytorów stają się nieaktywne, ale zachowują stan wymuszony do momentu ponownego wymuszenia przez ich dyspozytora
• Wcześniej wymuszone warianty w tym samym dyspozytocie, który stał się nieaktywny, są ponownie wymuszane

Zachowanie wskazówek dotyczących zapytań w Query Store

• Gdy wskazówka Query Store zostanie dodana do wariantu zapytania (zapytania podrzędnego), jest ona stosowana w taki sam sposób, jak wskazówka do zapytania innego niż PSP. Wskazówki dotyczące wariantów zapytania mają wyższy priorytet, jeśli do zapytania głównego w Query Store także została zastosowana wskazówka.

• Gdy do zapytania nadrzędnego zostanie dodana wskazówka magazynu zapytań, a zapytanie podrzędne (wariant zapytania) nie ma istniejącej wskazówki magazynu zapytań, zapytanie podrzędne (wariant zapytania) dziedziczy wskazówkę z zapytania nadrzędnego.

• Jeśli wskazówka dotycząca zapytania w magazynie zapytań zostanie usunięta z zapytania nadrzędnego, to w zapytaniach podrzędnych (wariantach zapytań) również zostanie usunięta.

• RECOMPILE Jeśli do zapytania nadrzędnego zostanie dodana wskazówka, system wygeneruje plany inne niż PSP po usunięciu istniejących planów wariantów zapytania z pamięci podręcznej planu, ponieważ funkcja PSP nie działa na zapytaniach, które mają wskazówkęRECOMPILE.

• Wyniki wskazówek Query Store można zaobserwować przy użyciu zdarzeń query_store_hints_application_success i query_store_hints_application_failed rozszerzeń. W przypadku tabeli sys.query_store_query_hints zawiera ona informacje dotyczące zastosowanej wskazówki dotyczącej zapytania. Jeśli wskazówka została zastosowana tylko w zapytaniu nadrzędnym, katalog systemowy zawiera informacje o wskazówce dla zapytania nadrzędnego, ale nie dla zapytań podrzędnych, chociaż zapytania podrzędne dziedziczą wskazówkę zapytania nadrzędnego.

Zachowanie PSP z podpowiedziami dotyczącymi zapytań i wymuszaniem planu można podsumować w poniższej tabeli:

¹ Składnik automatycznej korekty planu w funkcji automatycznego dostrajania systemu

Zdarzenia rozszerzone

• parameter_sensitive_plan_optimization_skipped_reason: Występuje, gdy pomijana jest funkcjonalność wrażliwa na parametry planu. To zdarzenie służy do monitorowania przyczyny pomijania optymalizacji PSP.

  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] = 'psp_skipped_reason_enum'
  ORDER BY map_key;
  

• parameter_sensitive_plan_optimization: występuje, gdy zapytanie używa funkcji optymalizacji PSP. Debuguj tylko kanał. Niektóre interesujące obszary mogą być następujące:

  • is_query_variant: opisuje, czy jest to plan dyspozytora (nadrzędny) lub plan wariantu zapytania (podrzędny)
  • predicate_count: liczba predykatów wybranych przez PSP
  • query_variant_id: wyświetla identyfikator wariantu zapytania. Wartość 0 oznacza, że obiekt jest planem dyspozytora (nadrzędnym).

• query_with_parameter_sensitivity: To zdarzenie, gdy zostanie wyzwolone, wyświetli liczbę interesujących predykatów, więcej szczegółów w formacie JSON dotyczących tych predykatów, oraz informację, czy PSPO jest obsługiwane dla tego predykatu lub predykatów.

• Przykładowe dane wyjściowe z zdarzenia rozszerzonego query_with_parameter_sensitivity

Zachowanie inspekcji programu SQL Server

Optymalizacja PSP dostarcza dane audytu dotyczące instrukcji planu dyspozytora oraz wszelkich wariantów zapytań związanych z dyspozytorem. Kolumna additional_information w ramach inspekcji programu SQL Server zawiera również odpowiednie informacje o stosie języka T-SQL dla wariantów zapytań. MyNewDatabase Korzystając z bazy danych jako przykładu, jeśli ta baza danych ma tabelę o nazwie T2 i procedurę składowaną o nazwie usp_test, po wykonaniu procedury składowanej usp_test dziennik inspekcji może zawierać następujące wpisy:

Znane problemy

Resolved

Wyjątek naruszenia dostępu może wystąpić w replikach pomocniczych z możliwością odczytu w określonych warunkach

(Rozwiązano w styczniu 2026 r.)

Zapytania spełniające następujące warunki mogą doświadczyć naruszenia dostępu, gdy wariant zapytania planu wrażliwego na parametr (PSP) nie może określić utrwalonego stanu swojej nadrzędnej instrukcji dyspozytora.

• Wykonywane w repliki pomocniczej
• Wrażliwe na wąchanie parametrów
• Spełnia warunki dla optymalizacji planu wrażliwego na parametry (PSP)

Wyjątek naruszenia dostępu występuje w magazynie zapytań w programie SQL Server 2022 w określonych warunkach

(Rozwiązano w marcu 2023 r.)

Ten problem wystąpił z powodu wyścigu, który może wystąpić, gdy statystyki uruchomieniowe dla wykonanych zapytań są zapisywane z reprezentacji magazynu zapytań w pamięci (znajdującego się w MEMORYCLERK_QUERYDISKSTORE_HASHMAP obsłudze pamięci) do wersji dyskowej magazynu zapytań. Statystyki środowiska uruchomieniowego, wyświetlane jako statystyki środowiska uruchomieniowego, są przechowywane w pamięci przez okres czasu zdefiniowany przez DATA_FLUSH_INTERVAL_SECONDS opcję SET QUERY_STORE instrukcji (wartość domyślna to 15 minut). Możesz użyć okna dialogowego Magazyn zapytań programu Management Studio, aby wprowadzić wartość interwału opróżniania danych (w minutach), która jest wewnętrznie konwertowana na sekundy. Jeśli system jest pod presją pamięci, statystyki środowiska uruchomieniowego można opróżnić na dysk wcześniej, niż to określono za pomocą opcji DATA_FLUSH_INTERVAL_SECONDS. Jeśli dodatkowe wątki w tle Magazynu Zapytań związane z czyszczeniem planu zapytań (tj. opcjami Magazynu Zapytań STALE_QUERY_THRESHOLD_DAYS i/lub MAX_STORAGE_SIZE_MB), istnieje sytuacja, w której wariant zapytania i/lub jego skojarzona instrukcja dyspozytora mogą zostać przedwcześnie dereferencjonowane. Może to spowodować naruszenie dostępu podczas operacji wstawiania lub usuwania wariantów zapytań w magazynie zapytań.

Zapoznaj się z sekcją Uwagi artykułu How Query Store Collects Data (Jak magazyn zapytań zbiera dane), aby uzyskać więcej informacji na temat operacji magazynu zapytań.

Treści powiązane

• Ponowne komkompilowanie planów wykonania
• Ponowne użycie parametrów i planu wykonania
• Prosta parametryzacja
• Wymuszona parametryzacja
• wskazówki dotyczące zapytań (Transact-SQL)
• inteligentne przetwarzanie zapytań w bazach danych SQL
• Czułość parametrów
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)