Оптимизация конфиденциального плана параметров

Статья
01/08/2024

Область применения: SQL Server 2022 (16.x) и более поздних версий, База данных SQL Azure, Управляемый экземпляр SQL Azure

Оптимизация конфиденциального плана параметров (PSP) является частью семейства функций интеллектуальной обработки запросов. В нем рассматривается сценарий, в котором один кэшированный план для параметризованного запроса не является оптимальным для всех возможных входящих значений параметров. Это происходит при неравномерном распределении данных. Дополнительные сведения см. в разделах Учет параметров и Параметры и повторное использование планов.

Дополнительные сведения о существующих обходных решениях для этого сценария проблемы см. в следующих статье:

Оптимизация PSP автоматически включает несколько активных кэшируемых планов для одной параметризованной инструкции. Кэшированные планы выполнения учитывают различные размеры данных на основе значений параметра среды выполнения, предоставленных клиентом.

Общие сведения о параметризации

В ядро СУБД SQL Server использование параметров или маркеров параметров в инструкциях Transact-SQL (T-SQL) повышает способность реляционного обработчика сопоставлять новые инструкции T-SQL с существующими, ранее скомпилированные планы выполнения и повысить повторное использование плана. Дополнительные сведения см. в разделе Простая параметризация.

Вы также можете переопределить поведение простой параметризации по умолчанию SQL Server, указав, что все SELECTоператоры , INSERTUPDATEи DELETE инструкции в базе данных параметризованы, при условии определенных ограничений. Дополнительные сведения см. в разделе Принудительная параметризация.

Реализация оптимизации PSP

Во время первоначальной компиляции гистограммы статистики столбцов определяют неоднородные распределения и оценивают наиболее параметризованные предикаты с риском до трех из всех доступных предикатов. Другими словами, если несколько предикатов в одном запросе соответствуют критериям, оптимизация PSP выбирает первые три. Функция PSP ограничивает количество вычисляемых предикатов, чтобы избежать больших двоичных значений кэша планов и хранилище запросов (если хранилище запросов включена) с слишком большим количеством планов.

Для соответствующих планов начальная компиляция создает план диспетчера, содержащий логику оптимизации PSP, называемую выражением диспетчера. План диспетчера сопоставляется с вариантами запросов на основе предикатов граничных значений диапазона кратности.

Терминология

Выражение диспетчера

Оценивает кратность предикатов на основе значений параметров среды выполнения и выполнения маршрута в различные варианты запроса.

План диспетчера

План, содержащий выражение диспетчера, кэшируется для исходного запроса. План диспетчера по сути представляет собой коллекцию предикатов, выбранных функцией, с несколькими дополнительными сведениями. Для каждого предиката, выбранного некоторые сведения, включенные в план диспетчера, являются высокими и низкими значениями границ. Эти значения используются для разделения значений параметров на разные контейнеры или диапазоны. План диспетчера также содержит статистику, используемую для вычисления значений границ.

Вариант запроса

Как план диспетчера оценивает кратность предикатов на основе значений параметров среды выполнения, он сегментирует эти значения и создает отдельные дочерние запросы для компиляции и выполнения. Эти дочерние запросы называются вариантами запросов. Варианты запросов имеют собственные планы в кэше планов и хранилище запросов.

Диапазон кратности предиката

Во время выполнения кратность каждого предиката вычисляется на основе значений параметров среды выполнения. Диспетчер сегментизирует значения кратности в три диапазона кратности предиката во время компиляции. Например, функция оптимизации PSP может создавать три диапазона, которые будут представлять диапазоны низкого, среднего и высокого кратности, как показано на следующей схеме.

Другими словами, при первоначальной компиляции параметризованного запроса функция оптимизации PSP создает план оболочки, известный как план диспетчера. Выражение диспетчера содержит логику, которая сегментирует запросы в варианты запросов на основе значений среды выполнения параметров. Когда начинается фактическое выполнение, диспетчер выполняет два шага:

Диспетчер оценивает выражение диспетчера для заданного набора параметров для вычисления диапазона кратности.
диспетчер сопоставляет эти диапазоны с определенными вариантами запросов и компилирует и выполняет варианты. Благодаря нескольким вариантам запросов функция оптимизации PSP достигает нескольких планов для одного запроса.

Границы диапазона кратности можно увидеть в формате ShowPlan XML плана отправки:

<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>

Созданное указание оптимизации PSP добавляется к инструкции SQL в файле ShowPlan XML варианта запроса. Указание нельзя использовать напрямую и не анализируется при добавлении вручную. Указание содержит следующие элементы:

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

ObjectID поступает из модуля (т. е. хранимой процедуры, функции, триггера), в который входит текущая инструкция; с предположением, что инструкция была создана из модуля. Если оператор является результатом динамического или нерегламентированного SQL (т sp_executesql. е. ) элемент ObjectID равен 0.
QueryVariantID примерно эквивалентен сочетанию диапазонов для всех предикатов, выбранных оптимизацией PSP. Например, если запрос имеет два предиката, которые имеют право на PSP, а каждый предикат имеет три диапазона, будет иметь девять диапазонов вариантов запроса, нумеруемых 1–9.
Диапазон предиката — это сведения о диапазоне предиката, созданные из выражения диспетчера.

Кроме того, в файле ShowPlan XML варианта запроса (внутри элемента Диспетчера):

<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>

Замечания

Функция оптимизации PSP в настоящее время работает только с предикатами равенства.
Планы диспетчера автоматически перестраиваются при значительных изменениях распределения данных. При необходимости планы вариантов запросов перекомпилируйте независимо, как и в любом другом типе плана запроса, при условии, что события перекомпиляции по умолчанию. Дополнительные сведения о перекомпиляции см. в разделе Перекомпиляция планов выполнения.
Представление системного каталога sys.query_store_plan (Transact-SQL хранилище запросов) было изменено, чтобы различать обычный скомпилированный план, план диспетчера и план варианта запроса. Новое представление системного каталога хранилище запросов sys.query_store_query_variant (Transact-SQL) содержит сведения о отношениях родительского-дочернего объекта между исходными параметризованными запросами (также известными как родительские запросы), планами диспетчера и их дочерними вариантами запросов.
При наличии нескольких предикатов, входящих в одну таблицу, оптимизация PSP выбирает предикат, который имеет большую часть данных на основе базовой гистограммы статистики. Например, с SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2тем, что оба column1 = @predicate1 и column2 = @predicate2 находятся в одной таблице, table1только наиболее сложенный предикат будет оцениваться функцией. Однако если в примере запроса используется оператор, например UNION, PSP оценивает несколько предикатов. Например, если запрос имеет характеристики, аналогичные SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, PSP выбирает не более двух предикатов в этом случае, так как система обрабатывает этот сценарий так, как если бы они были двумя разными таблицами. Такое же поведение можно наблюдать из запросов, которые самосоединяются с помощью псевдонимов таблиц.

Xml ShowPlan для варианта запроса будет выглядеть примерно так, как в следующем примере, где оба предикаты, выбранные были выбраны, добавляют соответствующие сведения в подсказку 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">

Вы можете повлиять на текущие пороговые значения, используемые функцией оптимизации PSP, с одним или несколькими из следующих методов:
- Флаги трассировки кратности (CE), такие как флаг трассировки 9481 (глобальный, сеанс или уровень запроса)
- Параметры конфигурации с областью действия базы данных, которые пытаются снизить используемую модель CE или повлиять на предположения, что модель CE делает в отношении независимости нескольких предикатов. Это особенно полезно в случаях, когда статистика с несколькими столбцами не существует, что влияет на способность оптимизации PSP оценивать кандидатуру этих предикатов.
- Дополнительные сведения см. в разделе "Увеличение допущения корреляции для нескольких предикатов" статьи "Оптимизация планов запросов" с помощью технического документа оценки кратности SQL Server 2014. Более новая модель CE пытается предположить некоторую корреляцию и меньше независимости для сочетания и дисъегации предикатов. Использование устаревшей модели CE может повлиять на то, как можно вычислить выборку предикатов в сценарии соединения с несколькими столбцами. Это действие следует учитывать только для определенных сценариев, и не рекомендуется использовать устаревшую модель CE для большинства рабочих нагрузок.
Оптимизация PSP в настоящее время компилирует и выполняет каждый вариант запроса в качестве новой подготовленной инструкции, что является одной из причин, по которым варианты запроса теряют связь с любыми родительскими модулями, object_id если план диспетчера основан на модуле (то есть хранимой процедуре, триггере, функции, представлении и т. д.). Как подготовленная инструкция, это не что-либо, object_id которое можно сопоставить с объектом sys.objects напрямую, но по сути вычисляемое значение на основе внутреннего хэша пакетного текста. Дополнительные сведения см. в разделе sys.dm_exec_plan_attributes "Возвращаемая таблица" документации по dmV.

Планы вариантов запросов помещаются в хранилище объектов кэша планов (CACHESTORE_OBJCP), а планы диспетчера помещаются в хранилище кэша планов SQL (CACHESTORE_SQLCP). Однако функция PSP сохранит object_id родительский элемент варианта запроса в атрибуте ObjectID, который является частью указания PLAN PER VALUE, что PSP добавляет в ShowPlan XML, если родительский запрос является частью модуля, а не динамическим или нерегламентированным T-SQL. Статистическая статистика производительности для кэшированных процедур, функций и триггеров может продолжать использоваться в соответствующих целях. Более детализированные статистические данные, связанные с выполнением, например те, которые находятся в представлениях, аналогичных sys.dm_exec_query_stats динамическому административному представлению, object_id по-прежнему содержат данные для вариантов запросов и объектов в sys.objects таблице, без дополнительной обработки XML ShowPlan для каждого из вариантов запроса, в которых требуется более подробная статистика среды выполнения. Сведения о среде выполнения и ожидании вариантов запросов можно получить из хранилище запросов без дополнительных методов синтаксического анализа ShowPlan XML, если хранилище запросов включен.
Так как варианты запросов PSP выполняются в качестве новой подготовленной инструкции, они object_id не предоставляются автоматически в различных динамических представлениях, связанных sys.dm_exec_* с кэшем планов, без перерезки XML ShowPlan и применения методов сопоставления шаблонов текста (т. е. дополнительной обработки XQuery). В настоящее время только планы диспетчера оптимизации PSP выдают соответствующий идентификатор родительского объекта. Он object_id предоставляется в хранилище запросов, так как хранилище запросов позволяет более реляционной модели, чем иерархия кэша планов. Дополнительные сведения см. в хранилище запросов представлении системного каталога sys.query_store_query_variant (Transact-SQL).

Рекомендации

Чтобы включить оптимизацию PSP, включите уровень совместимости базы данных 160 для базы данных, к к ней подключенной при выполнении запроса.
Для получения дополнительных сведений о функции оптимизации PSP рекомендуется включить интеграцию хранилище запросов, включив хранилище запросов. В следующем примере включается хранилище запросов для предварительно существующей базы данныхMyNewDatabase:

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

Примечание.

Начиная с SQL Server 2022 (16.x), хранилище запросов теперь включен по умолчанию для всех вновь созданных баз данных.

Чтобы отключить оптимизацию PSP на уровне базы данных, используйте конфигурацию с областью базы данных ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.
Чтобы отключить оптимизацию PSP на уровне запроса, используйте указание запроса DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.
Если сниффинг параметров отключен флагом трассировки 4136, PARAMETER_SNIFFING конфигурацией области базы данных или USE HINT('DISABLE_PARAMETER_SNIFFING') указанием запроса, оптимизация PSP отключена для связанных рабочих нагрузок и контекстов выполнения. Дополнительные сведения см. в разделах Указания (Transact-SQL) — запросы и ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
Количество уникальных вариантов плана для каждого диспетчера, хранящихся в кэше планов, ограничено, чтобы избежать раздувания кэша. Внутреннее пороговое значение не задокументировано. Так как каждый пакет SQL может создавать несколько планов, и каждый план варианта запроса имеет независимую запись в кэше планов, можно достичь максимального количества разрешенных записей плана по умолчанию. Если скорость вытеснения кэша плана заметно высока или размеры хранилищ кэша являются чрезмерными, следует рассмотреть возможность применения флага CACHESTORE_OBJCP CACHESTORE_SQLCP трассировки 174.
Количество уникальных вариантов плана, хранящихся для запроса в хранилище запросов, ограничено параметром конфигурации max_plans_per_query. В качестве вариантов запросов может быть несколько планов, в хранилище запросов в хранилище запросов может присутствовать всего 200 планов. Это число включает все планы вариантов запроса для всех диспетчеров, принадлежащих родительскому запросу. Рассмотрите max_plans_per_query возможность увеличения параметра конфигурации хранилище запросов.
- Пример того, как количество уникальных планов может превышать ограничение по умолчанию, хранилище запросов max_plans_per_query будет сценарием, в котором используется следующее поведение. Предположим, что у вас есть запрос с идентификатором запроса 10, который содержит два плана диспетчера, и каждый план диспетчера имеет 20 вариантов запросов каждый (40 вариантов запросов в общей сложности). Общее количество планов для идентификатора запроса 10 — 40 планов для вариантов запроса и двух планов диспетчера. Также возможно, что родительский запрос (идентификатор запроса 10) может иметь 5 обычных (ненаправителя) планов. Это делает 47 планов (40 из вариантов запросов, 2 диспетчера и 5 связанных планов, отличных от PSP). Кроме того, если каждый вариант запроса также имеет в среднем пять планов, в этом сценарии может быть более 200 планов в хранилище запросов родительского запроса. Это также зависит от сильного распределения данных в наборах данных, которые могут ссылаться на этот пример родительского запроса.
Для каждого сопоставления вариантов запроса с заданным диспетчером:
- query_plan_hash является уникальным. Этот столбец доступен в , а также в sys.dm_exec_query_statsдругих динамических административных представлениях и таблицах каталога.
- plan_handle является уникальным. Этот столбец доступен в sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans и других динамических административных представлениях и функциях и таблицах каталога.
- query_hash является общим для других вариантов, сопоставленных с одним и тем же диспетчером, поэтому можно определить совокупное использование ресурсов для запросов, которые отличаются только значениями входных параметров. Этот столбец доступен в sys.dm_exec_query_stats, sys.query_store_query и других динамических административных представлениях и таблицах каталога.
- sql_handle является уникальным из-за специальных идентификаторов оптимизации PSP, добавляемых в текст запроса во время компиляции. Этот столбец доступен в sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans и других динамических административных представлениях и функциях и таблицах каталога. В хранилище запросов доступны те же сведения о дескрипторе, что и в столбце last_compile_batch_sql_handle в таблице каталога sys.query_store_query.
- query_id является уникальным в хранилище запросов. Этот столбец доступен в sys.query_store_query и в других таблицах каталога хранилища запросов.

Принудительное применение плана в хранилище запросов

Использует те же хранимые процедуры sp_query_store_force_plan и sp_query_store_unforce_plan для работы с диспетчером или планами вариантов.

Если вариант принудительно, родительский диспетчер не принудительно. Если диспетчер является принудительным, то для использования доступны только варианты этого диспетчера.

Ранее принудительные варианты от других диспетчеров становятся неактивными, но сохраняют принудительное состояние до тех пор, пока их диспетчер снова не будет вынужден
Варианты в том же диспетчере, ставшие неактивными и помеченные ранее как принудительные, снова станут принудительными

поведение указания запроса хранилище запросов

При добавлении хранилище запросов в вариант запроса (дочерний запрос) указание применяется так же, как и запрос, отличный от PSP. Указания вариантов запроса имеют более высокий приоритет, если указание также было применено к родительскому запросу в хранилище запросов.
Если в родительский запрос добавляется указание хранилище запросов, а дочерний запрос (вариант запроса) не имеет существующего хранилище запросов указания, дочерний запрос (вариант запроса) наследует указание от родительского запроса.
Если подсказка запроса хранилище запросов удаляется из родительского запроса, дочерние запросы (варианты запросов) также удаляются.
RECOMPILE Если подсказка добавляется в родительский запрос, система создаст планы, отличные от PSP после удаления существующих планов вариантов запросов из кэша планов, так как функция PSP не работает с запросами с указаниемRECOMPILE.
хранилище запросов результаты указания можно наблюдать с помощью расширенных событий и query_store_hints_application_failed событийquery_store_hints_application_success. В таблице sys.query_store_query_hints содержится информация о подсказке запроса, которая была применена. Если указание было применено только к родительскому запросу, системный каталог содержит сведения о подсказке для родительского запроса, но не для его дочерних запросов, хотя дочерние запросы наследуют подсказку родительского запроса.

PSP с указаниями запросов и поведением принудительного планирования можно свести в следующей таблице:

Указание варианта запроса или план	Родительский элемент имеет подсказку, применяемую пользователем	Родитель имеет подсказку, примененную к отзыву	Родительский план вручную	Родитель имеет план принудительного использования APC ¹
Указание через пользователя	Указание варианта запроса	Указание варианта запроса	Указание варианта запроса	Н/П
Указание с помощью обратной связи	Указание варианта запроса	Указание варианта запроса	Указание варианта запроса	Н/П
Планирование, принудительное выполнение пользователем	Вариант запроса принудительный план	Вариант запроса принудительный план	Вариант запроса принудительный план	Вариант запроса принудительный план
Планирование, принудительное выполнение APC	Вариант запроса принудительный план	Вариант запроса принудительный план	Вариант запроса принудительный план	Вариант запроса принудительный план
Нет указания или принудительного плана	Указание родительского пользователя	Нет намека	Никаких действий	Никаких действий

¹ Компонент автоматической коррекции плана функции автоматической настройки

Расширенные события

parameter_sensitive_plan_optimization_skipped_reason: происходит при пропуске функции плана с учетом параметров. Используйте это событие для отслеживания причины пропуска оптимизации PSP.

В следующем запросе показаны все возможные причины пропуска PSP:
```
SELECT map_value FROM sys.dm_xe_map_values WHERE [name] ='psp_skipped_reason_enum' ORDER BY map_key;
```
parameter_sensitive_plan_optimization: происходит, когда запрос использует функцию оптимизации PSP. Только канал отладки. Некоторые поля, интересующие вас, могут быть:
- is_query_variant: описывает, является ли это план диспетчера (родительский) или план варианта запроса (дочерний)
- predicate_count: количество предикатов, выбранных PSP
- query_variant_id: отображает идентификатор варианта запроса. Значение 0 означает, что объект является планом диспетчера (родительским).

Поведение аудита SQL Server

Оптимизация PSP предоставляет данные аудита для оператора плана диспетчера и любые варианты запросов, связанные с диспетчером. Столбец additional_information аудита SQL Server также предоставляет соответствующие сведения о стеке T-SQL для вариантов запросов. MyNewDatabase Использование базы данных в качестве примера, если эта база данных имеет таблицу T2 с именем хранимой процедуры с именем usp_test, после выполнения хранимой процедуры usp_test журнал аудита может содержать следующие записи:

action_id	object_name	statement	additional_information
AUSC			`<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>`
EX	usp_test	exec usp_test 300
SL	T2	select * из dbo.t2 where ID=@id	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
SL	T2	select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[ dbo]. [T2]. [ID] = @id, 100.0, 100000.0)))	`tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
EX	usp_test	exec usp_test 60000
SL	T2	select * из dbo.t2 where ID=@id	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
SL	T2	select * из dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[ dbo]. [T2]. [ID] = @id, 100.0, 100000.0)))	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`

Известные проблемы

Проблема	Дата обнаружения	Состояние	Дата разрешения
Исключение нарушения доступа возникает в хранилище запросов в SQL Server 2022 (16.x) в определенных условиях. При включен хранилище запросов ной интеграции оптимизации PSP могут возникнуть исключения нарушений доступа. Дополнительные сведения см. в разделе об обновлении оптимизации конфиденциального плана параметров, почему?.	Март 2023 г.	"Разрешено"	Август 2023 г. (CU 7)

"Разрешено"

Исключение нарушения доступа возникает в хранилище запросов в SQL Server 2022 в определенных условиях

Примечание.

Начиная с SQL Server 2022 (16.x) накопительного обновления 7, несколько исправлений для состояния гонки, что может привести к нарушению доступа, было выпущено. Если нарушения доступа, связанные с оптимизацией PSP с интеграцией хранилище запросов, возникают после применения накопительного обновления 7 для SQL Server 2022 (16.x), рассмотрите следующий раздел обходного решения.

Эта проблема возникает из-за состояния гонки, которое может быть вызвано тем, что статистика среды выполнения для выполненного запроса сохраняется из представления хранилище запросов памяти (найденного в MEMORYCLERK_QUERYDISKSTORE_HASHMAP клерке памяти) на диск версии хранилище запросов. Статистика среды выполнения, показанная как статистика среды выполнения, хранится в памяти в течение определенного периода времени, определяемого DATA_FLUSH_INTERVAL_SECONDS параметром инструкции SET QUERY_STORE (значение по умолчанию — 15 минут). Диалоговое окно хранилище запросов Management Studio можно использовать для ввода значения интервала очистки данных (минут), которое внутренне преобразуется в секунды. Если система находится под давлением памяти, статистика среды выполнения может быть отброшена на диск раньше, чем определено с параметром DATA_FLUSH_INTERVAL_SECONDS . При дополнительных хранилище запросов фоновых потоках, связанных с очисткой плана запросов хранилище запросов (тSTALE_QUERY_THRESHOLD_DAYS. е. параметров или MAX_STORAGE_SIZE_MB хранилище запросов), запросы из хранилище запросов, есть сценарий, в котором вариант запроса и /или его связанная инструкция диспетчера может быть преждевременно разыменовано. Это может привести к нарушению доступа во время операций вставки или удаления вариантов запросов в хранилище запросов.

Дополнительные сведения об операциях хранилище запросов см. в разделе "Примечания" статьи о том, как хранилище запросов собирает данные.

Обходное решение. Варианты запросов, которые находятся в хранилище запросов, можно удалить, или функцию PSP можно временно отключить на уровне запроса или базы данных, пока дополнительные исправления не будут доступны, если система по-прежнему испытывает нарушения доступа в хранилище запросов с интеграцией PSP после применения накопительного обновления 7 для SQL Server 2022 (16.x).

Чтобы отключить оптимизацию PSP на уровне базы данных, используйте конфигурацию с областью базы данных ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.
Чтобы отключить оптимизацию PSP на уровне запроса, используйте указание запроса DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

Чтобы удалить все варианты запросов из хранилище запросов, а не только варианты запросов, которые отображаются в представлении каталога sys.query_store_query_variant (Transact-SQL), можно использовать запрос, аналогичный следующему. Замените [<database>] соответствующую базу данных, которая столкнулась с проблемами:

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the Query Store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

Если хранилище запросов большой или имеет значительную рабочую нагрузку или большое количество нерегламентированных не параметризованных запросов, которые соответствуют захвату хранилище запросов, отключение хранилище запросов может занять некоторое время. Чтобы принудительно отключить хранилище запросов в этих сценариях, используйте ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) команду в предыдущем примере T-SQL. Сведения о поиске не параметризованных запросов см. в разделе "Поиск не параметризованных запросов" в хранилище запросов.

Поделиться через

Оптимизация конфиденциального плана параметров

Общие сведения о параметризации

Реализация оптимизации PSP

Терминология

Выражение диспетчера

План диспетчера

Вариант запроса

Диапазон кратности предиката

Замечания

Рекомендации

Принудительное применение плана в хранилище запросов

поведение указания запроса хранилище запросов

Расширенные события

Поведение аудита SQL Server

Известные проблемы

"Разрешено"

Исключение нарушения доступа возникает в хранилище запросов в SQL Server 2022 в определенных условиях

Обратная связь

Дополнительные ресурсы

Поделиться через

Оптимизация конфиденциального плана параметров

Общие сведения о параметризации

Реализация оптимизации PSP

Терминология

Выражение диспетчера

План диспетчера

Вариант запроса

Диапазон кратности предиката

Замечания

Рекомендации

Принудительное применение плана в хранилище запросов

поведение указания запроса хранилище запросов

Расширенные события

Поведение аудита SQL Server

Известные проблемы

"Разрешено"

Исключение нарушения доступа возникает в хранилище запросов в SQL Server 2022 в определенных условиях

Связанный контент

Обратная связь

Дополнительные ресурсы