Otimização de Planos Sensíveis a Parâmetros

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

A otimização do Plano Sensível a Parâmetros (PSP) faz parte da família de recursos de processamento inteligente de consultas. Ele aborda o cenário em que um único plano armazenado em cache para uma consulta parametrizada não é ideal para todos os possíveis valores de parâmetros de entrada. É o caso das distribuições de dados não uniformes. Para obter mais informações, consulte Sensibilidade de Parâmetros e Parâmetros e Reutilização do Plano de Execução.

Para obter mais informações sobre soluções alternativas existentes para esse cenário de problema, consulte:

• Investigue e resolva problemas sensíveis a parâmetros
• Reutilização de Parâmetros e Plano de Execução
• Consultas com problemas de plano sensível a parâmetros (PSP)

A otimização PSP permite automaticamente vários planos em cache ativos para uma única instrução parametrizada. Os planos de execução armazenados em cache acomodam diferentes tamanhos de dados com base no(s) valor(es) do(s) parâmetro(s) de tempo de execução fornecido(s) pelo cliente.

Entenda a parametrização

No Mecanismo de Banco de Dados do SQL Server, o uso de parâmetros ou marcadores de parâmetros em instruções Transact-SQL (T-SQL) aumenta a capacidade do mecanismo relacional de fazer a correspondência de novas instruções T-SQL com planos de execução existentes compilados anteriormente e promover a reutilização do plano. Para obter mais informações, consulte Parametrização simples.

Você também pode sobrepor o comportamento de parametrização simples padrão do SQL Server especificando que todas as instruções SELECT, INSERT, UPDATE e DELETE num banco de dados sejam parametrizadas, sujeito a certas limitações. Para obter mais informações, consulte Parametrização forçada.

Implementação de otimização PSP

Durante a compilação inicial, histogramas de estatísticas de coluna identificam distribuições não uniformes e avaliam os predicados parametrizados de maior risco , até três de todos os predicados disponíveis. Em outras palavras, se vários predicados dentro da mesma consulta atenderem aos critérios, a otimização PSP escolhe os três primeiros. O recurso PSP limita o número de predicados que são avaliados a fim de evitar a sobrecarga do cache de planos e do Repositório de Consultas (se o Repositório de Consultas estiver ativado) por causa de muitos planos.

Para planos elegíveis, a compilação inicial produz um plano do dispatcher que contém a lógica de otimização PSP chamada expressão do dispatcher. Um plano de despacho mapeia variantes de consulta com base nos predicados dos valores de fronteira do intervalo de cardinalidade.

Terminology

Expressão do despachante

Avalia a cardinalidade de predicados com base nos valores dos parâmetros em tempo de execução e o encaminhamento da execução para diferentes variantes de consulta.

Plano do despachante

Um plano que contém a expressão dispatcher é armazenado em cache para a consulta original. O plano do despachante é essencialmente uma coleção dos predicados que foram selecionados pela funcionalidade, com alguns detalhes extras. Para cada predicado selecionado, alguns dos detalhes incluídos no plano do distribuidor incluem os valores de limite superior e inferior. Esses valores são usados para dividir os valores dos parâmetros em diferentes grupos ou intervalos. O plano do despachante também contém as estatísticas que foram usadas para calcular os valores limite.

Variante de consulta

À medida que um plano de dispatcher avalia a cardinalidade dos predicados com base nos valores dos parâmetros de tempo de execução, ele agrupa esses valores e gera consultas secundárias separadas para compilar e executar. Essas consultas filho são chamadas de variantes de consulta. As variantes de consulta têm os seus próprios planos no cache de planos e no Repositório de Consultas.

Intervalo de cardinalidade de predicados

No tempo de execução, a cardinalidade de cada predicado é avaliada com base nos valores dos parâmetros de tempo de execução. O dispatcher agrupa os valores de cardinalidade em três intervalos de cardinalidade de predicados em tempo de compilação. Por exemplo, o recurso de otimização PSP pode criar três intervalos que representariam intervalos de cardinalidade baixa, média e alta, conforme mostrado no diagrama a seguir.

Em outras palavras, quando uma consulta parametrizada é inicialmente compilada, o recurso de otimização PSP gera um plano de shell conhecido como plano dispatcher. A expressão dispatcher possui a lógica que classifica consultas em variantes de acordo com os valores dos parâmetros durante o tempo de execução. Quando a execução real começa, o despachante executa duas etapas:

• O dispatcher avalia sua expressão dispatcher para um determinado conjunto de parâmetros para calcular o intervalo de cardinalidade.

• O dispatcher mapeia esses intervalos para variantes de consulta específicas e compila e executa as variantes. Em virtude de ter várias variantes de consulta, o recurso de otimização PSP consegue ter vários planos para uma única consulta.

Os limites do intervalo de cardinalidade podem ser vistos dentro do XML ShowPlan de um plano de expedição:

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

Uma dica gerada pela otimização PSP é anexada à instrução SQL no XML ShowPlan de uma variante de consulta. A dica não pode ser usada diretamente e não é analisada se adicionada manualmente. A dica contém os seguintes elementos:

opção ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [nome da tabela]. [columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) )

• ObjectID vem do módulo (ou seja, procedimento armazenado, função, gatilho) do qual a instrução atual faz parte; com a suposição de que a instrução foi gerada a partir de um módulo. Se a instrução for o resultado de SQL dinâmico ou ad hoc (ou seja, sp_executesql), o elemento ObjectID será 0igual a .
• QueryVariantID é aproximadamente equivalente à combinação de intervalos para todos os predicados que a otimização PSP selecionou. Por exemplo, se uma consulta tiver dois predicados elegíveis para PSP e cada predicado tiver três intervalos, haverá nove intervalos de variantes de consulta numerados de 1 a 9.
• Intervalo de predicados é a informação do intervalo de cardinalidade predicada gerada a partir da expressão do dispatcher.

E, dentro do XML ShowPlan de uma variante de consulta (dentro do elemento Dispatcher):

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

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

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

Remarks

• A partir do SQL Server 2025 (17.x) e da compatibilidade de bases de dados 170, a otimização para PSP inclui as seguintes quatro melhorias:

  • Suporte para linguagem de manipulação de dados Instruções DML (Data Manipulation Language), como DELETE, INSERT, MERGE e UPDATE.
  • Suporte expandido para tempdb.
  • Consideração adicional dada em cenários em que existem vários predicados elegíveis na mesma tabela.
  • Alterações ao query_with_parameter_sensitivity evento estendido que incluíam os campos interesting_predicate_count, max_skewness, psp_optimization_supported, e query_type antes das mudanças no SQL Server 2025 (17.x) e na compatibilidade da base de dados 170. Mas agora, inclua os campos de contagem de predicados interessantes, detalhes de predicados interessantes, suporte à otimização PSP e tipo de consulta. Para obter mais informações, consulte a seção Eventos estendidos .

• O recurso de otimização PSP atualmente só funciona com predicados de igualdade.

• Os planos do dispatcher são reconstruídos automaticamente se houver alterações significativas na distribuição de dados. Os planos de variantes de consulta são recompilados independentemente conforme necessário, como com qualquer outro tipo de plano de consulta, sujeito a eventos de recompilação padrão. Para obter mais informações sobre recompilação, consulte Recompilando planos de execução.

• A exibição do catálogo do sistema sys.query_store_plan Query Store foi alterada para diferenciar entre um plano compilado normal, um plano de dispatcher e um plano de variante de consulta. A nova exibição de catálogo do sistema do Repositório de Consultas, sys.query_store_query_variant, contém informações sobre as relações pai-filho entre as consultas parametrizadas originais (também conhecidas como consultas pai), planos de dispatcher e suas variantes de consulta filho.

• Quando há vários predicados que fazem parte da mesma tabela, a otimização PSP seleciona o predicado que tem mais distorção de dados com base no histograma de estatísticas subjacente. Por exemplo, com SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, porque ambos column1 = @predicate1 e column2 = @predicate2 são da mesma tabela, table1, apenas o predicado mais enviesado será avaliado pelo recurso. No entanto, se a consulta de exemplo envolver um operador como um UNION, o PSP avaliará mais de um predicado. Por exemplo, se uma consulta tiver características semelhantes a SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, o PSP seleciona no máximo dois predicados neste caso, porque o sistema trata esse cenário como se fossem duas tabelas diferentes. O mesmo comportamento pode ser observado a partir de consultas que se auto-unem por meio de aliases de tabela.

• O XML ShowPlan para uma variante de consulta seria semelhante ao exemplo a seguir, onde ambos os predicados selecionados têm as suas respetivas informações adicionadas à dica relacionada com 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">
  

• Você pode influenciar os limites de assimetria atuais usados pelo recurso de otimização PSP, com um ou mais dos seguintes métodos:

  • Sinalizadores de rastreamento do estimador de cardinalidade (CE), como o sinalizador de rastreamento 9481 (nível global, de sessão ou de consulta)

  • Opções de configuração com escopo de banco de dados que tentam reduzir o modelo CE em uso ou influenciar as suposições que o modelo CE faz em relação à independência de vários predicados. Isso é especialmente útil nos casos em que as estatísticas de várias colunas não existem, o que afeta a capacidade da otimização do PSP de avaliar a candidatura desses predicados.

  • Para obter mais informações, consulte a seção Suposição de correlação aumentada para vários predicados do whitepaper Otimizando seus planos de consulta com o SQL Server 2014 Cardinality Estimator . O novo modelo CE tenta assumir alguma correlação e menos independência para a conjunção e disjunção de predicados. O uso do modelo CE herdado pode afetar como a seletividade dos predicados em um cenário de junção de várias colunas pode ser calculada. Essa ação só deve ser considerada para cenários específicos e não é recomendável usar o modelo CE herdado para a maioria das cargas de trabalho.

• A otimização do PSP atualmente compila e executa cada variante de consulta como uma nova instrução preparada, o que é uma das razões pelas quais as variantes de consulta perdem a associação com qualquer módulo pai caso object_id o plano do despachante tenha sido baseado em um módulo (ou seja, procedimento armazenado, trigger, função, vista, e assim por diante). Como uma declaração preparada, o object_id não é algo que possa ser mapeado diretamente para um objeto em sys.objects, mas é essencialmente um valor calculado com base numa hash interna do texto em lote. Para obter mais informações, consulte a seção Tabela retornada da documentação do sys.dm_exec_plan_attributes Detran.

  Os planos de variantes de consulta são colocados no repositório de cache de planos (CACHESTORE_OBJCP) enquanto os planos de dispatcher são colocados no repositório de cache de planos SQL (CACHESTORE_SQLCP). No entanto, o recurso PSP armazenará o object_id do pai de uma variante de consulta dentro do atributo ObjectID, que faz parte da dica PLAN PER VALUE que o PSP adiciona ao XML ShowPlan, se a consulta pai fizer parte de um módulo e não for T-SQL dinâmico ou ad hoc. As estatísticas de desempenho agregadas para procedimentos, funções e gatilhos armazenados em cache podem continuar a ser usadas para seus respetivos fins. Estatísticas relacionadas à execução mais granulares, como as encontradas em visualizações semelhantes ao sys.dm_exec_query_stats DMV, ainda contêm dados para variantes de consulta; no entanto, a associação entre as object_id variantes de consulta e objetos dentro da tabela sys.objects atualmente não se alinham, sem processamento adicional do XML ShowPlan para cada uma das variantes de consulta nas quais são necessárias estatísticas de tempo de execução mais granulares. As informações de estatísticas de tempo de execução e espera para variantes de consulta podem ser obtidas do Repositório de Consultas sem técnicas adicionais de análise XML do ShowPlan se o Repositório de Consultas estiver habilitado.

• Como as variantes de consulta PSP são executadas como uma nova instrução preparada, a object_id não é automaticamente exposta nos vários DMVs relacionados ao cache do plano sem desfragmentar o XML do ShowPlan e aplicar técnicas de correspondência de padrões de texto (ou seja, processamento XQuery adicional). Atualmente, apenas os planos de despachante de otimização PSP emitem o ID de objeto pai apropriado. O object_id está exposto no Armazém de Consultas, pois o Armazém de Consultas oferece um modelo mais relacional do que a hierarquia de cache do plano proporciona. Para obter mais informações, consulte a exibição de catálogo do sistema Query Store sys.query_store_query_variant.

Considerations

• Para habilitar a otimização PSP, habilite o nível de compatibilidade de banco de dados 160 para o banco de dados ao qual você está conectado ao executar a consulta.

• Para obter informações adicionais sobre o recurso de otimização PSP, recomendamos que a integração do Repositório de Consultas esteja habilitada, ativando o Repositório de Consultas. O exemplo a seguir ativa o Repositório de Consultas para um banco de dados preexistente chamado MyNewDatabase:

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

Note

A partir do SQL Server 2022 (16.x), o Repositório de Consultas agora está habilitado por padrão para todos os bancos de dados recém-criados.

• Para desativar a otimização PSP ao nível da base de dados, utilize a configuração com âmbito de ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF base de dados.

• Para desativar a otimização PSP ao nível de consulta, use a sugestão de consulta DISABLE_PARAMETER_SENSITIVE_PLAN.

• Se a deteção de parâmetros estiver desabilitada pelo sinalizador de rastreamento 4136, PARAMETER_SNIFFING pela configuração com escopo do banco de dados ou pela dica de consulta, a USE HINT('DISABLE_PARAMETER_SNIFFING') otimização do PSP será desabilitada para as cargas de trabalho e contextos de execução associados. Para obter mais informações, consulte Dicas de consulta e ALTER DATABASE SCOPED CONFIGURATION.

• O número de variantes de plano exclusivas por dispatcher armazenadas no cache do plano é limitado para evitar o inchaço do cache. O limite interno não está documentado. Como cada lote SQL tem o potencial de criar vários planos, e cada plano de variante de consulta tem uma entrada independente no cache de planos, é possível atingir o número máximo padrão de entradas de plano permitidas. Se a taxa de remoção de cache do plano for observavelmente alta ou se os tamanhos dos CACHESTORE_OBJCP forem CACHESTORE_SQLCP excessivos, você deve considerar a aplicação do sinalizador de rastreamento 174.

• O número de variantes de plano exclusivas armazenadas para uma consulta no repositório do Repositório de Consultas é limitado pela max_plans_per_query opção de configuração. Como as variantes de consulta podem ter mais de um plano, um total de 200 planos podem estar presentes por consulta no Repositório de Consultas. Esse número inclui todos os planos de variantes de consulta para todos os expedidores que pertencem a uma consulta pai. Considere aumentar a opção de configuração do max_plans_per_query Repositório de Consultas.

  • Um exemplo de como o número de planos exclusivos pode exceder o limite padrão do Repositório de Consultas max_plans_per_query seria um cenário no qual você tem o seguinte comportamento. Vamos supor que tenha uma consulta com uma ID de consulta igual a 10, que tem dois planos de distribuição e cada plano de distribuição tem 20 variantes de consulta cada (40 variantes de consulta no total). O número total de planos para a ID de Consulta 10 é de 40 planos para as variantes de consulta e os dois planos de distribuição. É também possível que a consulta principal em si (ID de consulta 10) possa ter 5 planos normais (não despachantes). Isto perfaz um total de 47 planos (40 provenientes de variantes de consulta, 2 de distribuição e 5 planos não relacionados com PSP). Além disso, se cada variante de consulta também tiver uma média de cinco planos, será possível, neste caso, ter mais de 200 planos no Repositório de Consultas para uma consulta pai. Isso também dependeria do grande desequilíbrio de dados no(s) conjunto(s) de dados a que esta consulta de exemplo principal pode estar se referindo.

• Para cada variante de consulta mapeada para um determinado encaminhador:

  • O query_plan_hash é único. Esta coluna está disponível no sys.dm_exec_query_stats, e em outras exibições de gerenciamento dinâmico e tabelas de catálogo.
  • O plan_handle é único. Esta coluna está disponível em sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_planse em outras Exibições e Funções de Gerenciamento Dinâmico e tabelas de catálogo.
  • O query_hash é comum a outras variantes mapeadas para o mesmo dispatcher, portanto, é possível determinar o uso de recursos agregados para consultas que diferem apenas por valores de parâmetros de entrada. Esta coluna está disponível em sys.dm_exec_query_stats, sys.query_store_query e em outras Visões de Gestão Dinâmica e tabelas de catálogo.
  • O sql_handle é único devido a identificadores de otimização PSP especiais sendo adicionados ao texto da consulta durante a compilação. Esta coluna está disponível em sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_planse em outras Exibições e Funções de Gerenciamento Dinâmico e tabelas de catálogo. As mesmas informações de identificador estão disponíveis no Repositório de Consultas como a coluna last_compile_batch_sql_handle na tabela de catálogo sys.query_store_query.
  • O query_id é único no Repositório de Consultas. Esta coluna está disponível no sys.query_store_query, e em outras tabelas de catálogo do Repositório de Consultas.

Planejar o forcing no Repositório de Consultas

Utiliza os mesmos procedimentos armazenados sp_query_store_force_plan e sp_query_store_unforce_plan para operar em planos de despachante ou de variante.

Se uma variante for forçada, o despachante pai não será forçado. Se um despachante for forçado, apenas as variantes desse despachante são consideradas elegíveis para utilização:

• Variantes anteriormente forçadas de outros despachantes tornam-se inativas, mas mantêm o status forçado até que seu despachante seja forçado novamente
• Variantes anteriormente forçadas no mesmo despachante que se tornaram inativas são forçadas novamente

Comportamento da dica de consulta do Repositório de Consultas

• Quando uma dica do Repositório de Consultas é adicionada a uma variante de consulta (consulta filho), a dica é aplicada da mesma maneira que uma consulta não PSP. As sugestões de variantes de consulta têm maior precedência se também tiver sido aplicada uma sugestão à consulta principal no Repositório de Consultas.

• Quando uma dica do Repositório de Consultas é adicionada à consulta pai e a consulta filha (variante) não tem uma dica existente do Repositório de Consultas, a consulta filha (variante) herda a dica da consulta pai.

• Se uma dica de consulta do Repositório de Consultas for removida da consulta pai, as consultas filho (variantes de consulta) também terão a dica removida.

• Se uma RECOMPILE pista for adicionada à consulta pai, o sistema gerará planos não PSP depois que qualquer variante de consulta existente tiver sido removida do cache de planos, já que o recurso PSP não opera em consultas que tenham uma RECOMPILE pista.

• Os resultados da dica do Repositório de Consultas podem ser observados usando os eventos estendidos query_store_hints_application_success e query_store_hints_application_failed. Para a tabela sys.query_store_query_hints , ela contém informações sobre a dica de consulta que foi aplicada. Se a dica tiver sido aplicada apenas numa consulta-mãe, o catálogo do sistema conterá as informações da dica para a consulta-mãe, mas não para as suas consultas-filhas, embora as consultas-filhas herdem a dica da consulta-mãe.

PSP com dicas de consulta e comportamento de imposição de planos pode ser resumido na seguinte tabela:

Dica ou plano de variante de consulta	O pai tem dica aplicada pelo usuário	O pai tem uma dica aplicada por feedback	O pai tem um plano forçado manualmente	Pai tem plano forçado APC 1
Dica via usuário	Dica de variante de consulta	Dica de variante de consulta	Dica de variante de consulta	N/A
Dica através de feedback	Dica de variante de consulta	Dica de variante de consulta	Dica de variante de consulta	N/A
Plano forçado pelo usuário	Variante de consulta plano obrigatório	Variante de consulta plano obrigatório	Variante de consulta plano obrigatório	Variante de consulta plano obrigatório
Plano forçado pela APC	Variante de consulta plano obrigatório	Variante de consulta plano obrigatório	Variante de consulta plano obrigatório	Variante de consulta plano obrigatório
Nenhuma dica ou plano forçado	Dica do usuário pai	Sem pista	Sem ação	Sem ação

¹ Componente de correção automática de plano do recurso de ajuste automático

Eventos Prolongados

• parameter_sensitive_plan_optimization_skipped_reason: Ocorre quando é ignorado o recurso de plano sensível a parâmetros. Use este evento para monitorar o motivo pelo qual a otimização do PSP é ignorada.

  A consulta seguinte mostra todas as possíveis razões pelas quais a PSP foi ignorada:

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

• parameter_sensitive_plan_optimization: Ocorre quando uma consulta usa o recurso de otimização PSP. Apenas canal de depuração. Algumas áreas de interesse podem ser:

  • is_query_variant: indica se é um plano de distribuição (pai) ou um plano de variação de consulta (filho)
  • predicate_count: número de predicados selecionados pela PSP
  • query_variant_id: exibe o ID da variante de consulta. Um valor de 0 significa que o objeto é um plano de distribuição (superior).

• query_with_parameter_sensitivity: Este evento quando disparado exibirá o número de predicados que a funcionalidade considerou interessantes, mais detalhes no formato JSON sobre os predicados interessantes, bem como se PSPO é suportado para o predicado ou predicados.

• Exemplo de saída do query_with_parameter_sensitivity evento estendido

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

Comportamento de auditoria do SQL Server

A otimização do PSP fornece dados de auditoria para a declaração do plano do distribuidor e as variantes de consulta associadas ao distribuidor. A coluna additional_information na Auditoria do SQL Server também fornece as informações relevantes de T-SQL para diferentes tipos de consultas. Usando a MyNewDatabase base de dados como exemplo, se esta base de dados tiver uma tabela chamada T2 e um procedimento armazenado com o nome de usp_test, após a execução do procedimento armazenado usp_test, o log de auditoria poderá conter as seguintes entradas:

action_id	object_name	declaração	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	Executar usp_test 300
SL	T2	selecione * de dbo.t2 onde ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	selecione * de dbo.t2 onde ID=@id opção (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	Executar usp_test 60000
SL	T2	selecione * de dbo.t2 onde ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	Selecione * de dbo.t2 onde ID=@id opção (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>

Problemas conhecidos

Issue	Data de descoberta	Status	Data concluída
A exceção de Violação de Acesso ocorre no Repositório de Consultas no SQL Server 2022 (16.x) sob determinadas condições. Você pode encontrar exceções de violação de acesso quando a integração de otimização PSP do Query Store estiver ativada. Para obter mais informações, consulte a atualização em Otimização de plano sensível a parâmetros, por quê?	Março de 2023	Resolved	Agosto 2023 (CU 7)
A exceção de violação de acesso pode ocorrer em réplicas secundárias legíveis sob certas condições no SQL Server 2025 (17.x). Poderá deparar-se com exceções de violação de acesso quando a otimização PSP estiver ativada num banco de dados secundário legível que tenha sido configurado para usar o Repositório de Consultas para secundários legíveis.	Setembro de 2025	Tem solução alternativa

Tem solução alternativa

A exceção de violação de acesso pode ocorrer em réplicas secundárias legíveis sob determinadas condições

As consultas que satisfazem as seguintes condições podem sofrer uma violação de acesso quando uma variante de consulta PSP não consegue determinar o estado persistente da instrução de dispatcher mãe.

• Executado numa réplica secundária
• Sensível à deteção de parâmetros
• Elegível para otimização do plano sensível a parâmetros (PSP)

Solução alternativa: desative o PSP em secundários para cada banco de dados integrado para usar o recurso Repositório de Consultas para secundários legíveis. No contexto de uma base de dados específica, emita a seguinte declaração Transact-SQL:

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Resolved

A exceção de violação de acesso ocorre no Repositório de Consultas no SQL Server 2022 sob determinadas condições

Note

A Atualização Cumulativa 7 do SQL Server 2022 (16.x) lançou várias correções para uma condição de corrida que pode levar a uma violação de acesso.

Esse problema ocorreu devido a uma condição de concorrência que pode ser causada quando as estatísticas de tempo de execução para uma consulta executada estão sendo persistidas da representação na memória do Repositório de Consultas (encontrado no MEMORYCLERK_QUERYDISKSTORE_HASHMAP controle de memória) para a versão em disco do Repositório de Consultas. As estatísticas de tempo de execução, mostradas como Estatísticas de tempo de execução, são mantidas na memória por um período de tempo, definido pela DATA_FLUSH_INTERVAL_SECONDS opção da SET QUERY_STORE instrução (o valor padrão é 15 minutos). Você pode usar a caixa de diálogo Repositório de Consultas do Management Studio para inserir um valor para Intervalo de Liberação de Dados (Minutos), que é convertido internamente em segundos. Se o sistema estiver sob pressão de memória, as estatísticas de tempo de execução podem ser liberadas no disco antes do definido com a DATA_FLUSH_INTERVAL_SECONDS opção. Quando threads adicionais em plano de fundo do Query Store estão relacionadas com a limpeza do plano de consulta (seja nas opções STALE_QUERY_THRESHOLD_DAYS e/ou MAX_STORAGE_SIZE_MB do Query Store), há um cenário em que uma variante de consulta e/ou sua instrução de despachante associada pode ser desreferenciada prematuramente. Isso pode resultar em uma violação de acesso durante operações de inserção ou exclusão de variantes de consulta no Repositório de Consultas.

Consulte a seção Comentários do artigo Como o Repositório de Consultas Coleta Dados para obter mais informações sobre as operações do Repositório de Consultas.

Conteúdo relacionado

• Recompilando planos de execução
• Reutilização de Parâmetros e Plano de Execução
• Parametrização simples
• Parametrização forçada
• Dicas de consulta (Transact-SQL)
• Processamento inteligente de consultas em bancos de dados SQL
• Sensibilidade ao parâmetro
• ALTERAR A CONFIGURAÇÃO DE ESCOPO DA BASE DE DADOS (Transact-SQL)