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

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

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

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

• Изучение и устранение проблем с учетом параметров
• Параметры и повторное использование планов выполнения
• Запросы с проблемами плана с учетом параметров (PSP)

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

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

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

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

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

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

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

Terminology

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

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

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

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

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

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

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

Во время выполнения кратность каждого предиката вычисляется на основе значений параметров среды выполнения. Диспетчер разбивает значения кардинальности на три диапазона кардинальности предикатов во время компиляции. Например, функция оптимизации 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>

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 содержит сведения о родительско-дочерних отношениях между исходными параметризованными запросами (также известными как родительские запросы), планами маршрутизатора и их дочерними вариантами запросов.

• При наличии нескольких предикатов в одной таблице, оптимизация 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 напрямую, а по сути представляет собой вычисляемое значение на основе внутреннего хэша текста пакета. Для получения дополнительной информации см. раздел «Возвращённая таблица» в документации DMV.

  Планы вариантов запросов помещаются в хранилище объектов кэша планов (CACHESTORE_OBJCP), а планы диспетчера помещаются в хранилище кэша планов SQL (CACHESTORE_SQLCP). Однако функция PSP сохранит object_id родительский элемент варианта запроса в атрибуте ObjectID, который является частью указания PLAN PER VALUE, которое PSP добавляет в XML ShowPlan, если родительский запрос входит в состав модуля, а не является динамическим или нерегламентированным 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.

Considerations

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

• Для получения дополнительных сведений о функции оптимизации 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 планов). Кроме того, если у каждого варианта запроса в среднем по пять планов, может оказаться, что в Query Store для родительского запроса будет более 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 и в других таблицах каталога хранилища запросов.

Принудительное применение плана в Query Store

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

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

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

Поведение подсказок запроса в Query Store

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

• Если к родительскому запросу добавляется подсказка хранилища запросов, а дочерний запрос (вариант запроса) не имеет существующей подсказки хранилища запросов, то дочерний запрос (вариант запроса) наследует подсказку от родительского запроса.

• Если подсказка для запросов в Query Store удаляется из родительского запроса, то из дочерних запросов (вариантов запросов) она также удаляется.

• 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	Вариант запроса принудительный план	Вариант запроса принудительный план	Вариант запроса принудительный план	Вариант запроса принудительный план
Нет указания или принудительного плана	Подсказка родительского пользователя	Нет намека	Нет действий	Нет действий

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

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

• 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	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 * from 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>

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

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

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

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

• Перекомпилирование планов выполнения
• Параметры и повторное использование планов выполнения
• Простая параметризация
• Принудительная параметризация
• Подсказки запросов (Transact-SQL)
• Интеллектуальная обработка запросов в базах данных SQL
• Конфиденциальность параметров
• КОНФИГУРАЦИЯ СКОПА БАЗЫ ДАННЫХ ALTER (Transact-SQL)