Оптимизация плана, чувствительного к параметрам

Применимо к: SQL Server 2022 (16.x) и более поздним версиям База данных SQL AzureAzure SQL Управляемый экземплярSQL база данных в Microsoft Fabric

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

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

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

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

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

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

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

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

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

Terminology

Родительский запрос

Исходный параметризованный запрос, для которого оптимизация 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 варианта запроса. Указание нельзя использовать напрямую и не анализируется при добавлении вручную. Указание содержит следующие элементы:

опция ( ПЛАН НА ЗНАЧЕНИЕ ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [имя базы данных].[ 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>

Remarks

  • Начиная с SQL Server 2025 (17.x) и совместимости базы данных 170 оптимизация PSP включает следующие четыре улучшения:

    • Поддержка операторов языка манипулирования данными (DML), таких как DELETE, INSERT, MERGE и UPDATE.
    • Расширенная поддержка tempdb.
    • Поддержка выбора нескольких подходящих предикатов из одной таблицы, при этом в каждом столбце выбирается не более одного предиката.
    • Изменения в расширенном query_with_parameter_sensitivity событии, включающее поля interesting_predicate_count, max_skewness, psp_optimization_supported и query_type до изменений в SQL Server 2025 (17.x) и совместимости базы данных 170. Но теперь включите поля Interesting_Predicate_Count, Interesting_Predicate_Details, PSP_Optimization_Supported и Query_Type. Дополнительные сведения см. в разделе "Расширенные события ".

  • Функция оптимизации PSP в настоящее время работает только с предикатами равенства.

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

  • Представление системного каталога хранилища запросов sys.query_store_plan было изменено, чтобы различать обычный скомпилированный план, план диспетчера и план варианта запроса. Новое представление системного каталога хранилища запросов sys.query_store_query_variant содержит сведения о родительско-дочерних отношениях между исходными параметризованными запросами (также известными как родительские запросы), планами маршрутизатора и их дочерними вариантами запросов.

  • На уровне совместимости базы данных 160, когда существует несколько допустимых предикатов, относящихся к одной и той же ссылке на таблицу, оптимизация PSP выбирает предикат с наибольшей неравномерностью распределения данных на основе гистограммы базовой статистики. Например, при SELECT * FROM table1 WHERE column1 = @predicate1 AND column2 = @predicate2, выбирается только самый смещённый предикат, потому что оба предиката ссылаются на одну и ту же таблицу. Однако если запрос ссылается на таблицу более одного раза, например, в отдельных ветвях конструкции UNION или через псевдонимы в самоприсоединении, оптимизация PSP может выбрать предикаты из более чем одной ссылки на таблицу.

  • Начиная с SQL Server 2025 (17.x) и уровня совместимости баз данных 170, оптимизация PSP может выбирать несколько подходящих предикатов из одной таблицы. Выбирается только один предикат на каждый столбец, а общее количество предикатов, выбранных для запроса, остаётся ограниченным тремя. Например, при SELECT * FROM table1 WHERE column1 = @predicate1 AND column2 = @predicate2, оба предиката можно выбрать, если они соответствуют критериям оптимизации 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. Для получения дополнительной информации см. раздел «Возвращённая таблица» в документации DMV.

    Вариантные планы запросов помещаются в кэш SQL Plans (CACHESTORE_SQLCP). План диспетчеризации остаётся частью оригинального кэшированного плана партии или модуля. Если родительский запрос является частью сохранённой процедуры или другого модуля, скомпилированный план которого хранится в хранилище кэша Object Plans (CACHESTORE_OBJCP), план диспетчера хранится в CACHESTORE_OBJCP. Если родительский запрос является частью параметризованного динамического SQL или допустимого ad hoc пакета, план диспетчера хранится в CACHESTORE_SQLCP.

    Функция PSP хранит object_id родительского модуля варианта запроса внутри атрибута ObjectID, который входит в подсказку PLAN PER VALUE в XML ShowPlan. Если родительский запрос динамический или ad hoc T-SQL, этот атрибут равен 0. Статистическая статистика производительности для кэшированных процедур, функций и триггеров может продолжать использоваться в соответствующих целях. Более детализированные статистические данные, связанные с выполнением, например, в sys.dm_exec_query_stats DMV, всё ещё содержат данные для вариантов запросов. Однако связывание этих статистик с родительским модулем требует дополнительной обработки XML ShowPlan, поскольку кэш object_id плана варианта запроса напрямую не идентифицирует родительский модуль. Статистику времени выполнения и ожидания вариантов запросов можно получить из хранилище запросов без дополнительного разбора XML в ShowPlan, если хранилище запросов включён.

  • Идентификатор объекта родительского модуля не отображается напрямую как object_id варианта запроса в связанных с кэшем планов sys.dm_exec_* DMV. Кэшированный план модуля, содержащий диспетчер, сохраняет идентификатор объекта модуля. Для вариантов запроса идентификатор объекта родительского модуля можно получить из ObjectID атрибута в PLAN PER VALUE подсказке, обработав XML ShowPlan. хранилище запросов предоставляет реляционную модель для выявления этих отношений. Вид sys.query_store_query_variant каталога идентифицирует исходный параметризованный запрос с parent_query_id, план диспетчера с dispatcher_plan_id, а дочерний запрос с query_variant_query_id.

Considerations

  • Чтобы включить оптимизацию PSP, используйте уровень совместимости базы данных 160 или выше для базы, к которой вы подключены при выполнении запроса. Улучшения оптимизации PSP, введённые в SQL Server 2025 (17.x), требуют уровня совместимости с базами данных 170 и выше.

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

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

Note

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

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

  • Чтобы отключить оптимизацию PSP на уровне запроса, используйте указание запроса DISABLE_PARAMETER_SENSITIVE_PLAN.

  • Если сниффинг параметров отключен флагом трассировки 4136, PARAMETER_SNIFFING конфигурацией области базы данных или USE HINT('DISABLE_PARAMETER_SNIFFING') указанием запроса, оптимизация PSP отключена для связанных рабочих нагрузок и контекстов выполнения. Дополнительные сведения см. в указаниях запросов и ALTER DATABASE SCOPED CONFIGURATION.

  • Количество уникальных вариантов плана для каждого диспетчера, хранящихся в кэше планов, ограничено, чтобы избежать раздувания кэша. Внутреннее пороговое значение не задокументировано. Так как каждый пакет 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_success и событий query_store_hints_application_failed. В таблице sys.query_store_query_hints содержится информация о подсказке запроса, которая была применена. Если указание было применено только к родительскому запросу, системный каталог содержит сведения о подсказке для родительского запроса, но не для его дочерних запросов, хотя дочерние запросы наследуют подсказку родительского запроса.

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

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

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

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

  • 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 означает, что объект является планом диспетчера (родительским).

  • query_with_parameter_sensitivity: это событие при срабатывании отобразит количество найденных функцией интересных предикатов, предоставив дополнительные сведения об этих интересных предикатах в формате JSON, а также поддерживается ли PSPO для этих предикатов.

  • Пример выходных данных из расширенного query_with_parameter_sensitivity события

Field Value
interesting_predicate_count 3
interesting_predicate_details {"Predicates":[{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75}]}
psp_optimization_supported True
query_type 195

Поведение аудита 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 выберите * из dbo.t2, где ID=@id опция (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 выберите * из dbo.t2, где ID=@id опция (ПЛАН ПО ЗНАЧЕНИЮ(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>

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

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

Resolved

Исключение «нарушение доступа» может возникать на вторичных репликах, доступных для чтения, при определенных условиях.

(Устранено в январе 2026 г.)

Запросы, соответствующие следующим условиям, могут столкнуться с нарушением доступа, если вариант запроса, зависящий от параметров (PSP), не может определить сохраненное состояние оператор-диспетчера родительского уровня:

  • Выполнено на вторичной реплике
  • Уязвимо к перехвату параметров
  • Подходит для оптимизации плана, чувствительного к параметрам (PSP)

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

(Разрешено в марте 2023 г.)

Note

Накопительный пакет обновления 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 параметры хранилища запросов), извлекают запросы из хранилища запросов, возникает ситуация, когда вариант запроса и/или его связанная инструкция диспетчера могут быть преждевременно потеряны из ссылки. Это может привести к нарушению доступа во время операций вставки или удаления вариантов запросов в хранилище запросов.

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

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

  • Последнее обновление: