Otimização do plano confidencial de parâmetro

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores

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

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

• Investigar e resolve problemas sensíveis a parâmetros
• Reutilização de Parâmetros e Plano de Execução
• Consultas que têm problemas de PSP (plano sensível ao parâmetro)

A otimização do PSP habilita automaticamente vários planos ativos em cache para uma única instrução parametrizada. Os planos de execução armazenados em cache acomodam diferentes tamanhos de dados com base nos valores de parâmetro de runtime fornecidos pelo cliente.

Entender a parametrização

No Mecanismo de Banco de Dados SQL Server, o uso de parâmetros ou marcadores de parâmetro em instruções T-SQL (Transact-SQL) aumenta a capacidade do mecanismo relacional de corresponder 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, confira Parametrização Simples.

Você também pode substituir o comportamento de parametrização simples padrão de SQL Server especificando que todas as SELECTinstruções , INSERT, UPDATEe DELETE em um banco de dados são parametrizadas, sujeitas a determinadas limitações. Para obter mais informações, confira a Parametrização Forçada.

Implementação da otimização do PSP

Durante a compilação inicial, os histogramas de estatísticas de coluna identificam distribuições não uniformes e avaliam os predicados parametrizados mais em risco , até três de todos os predicados disponíveis. Em outras palavras, se vários predicados na mesma consulta atenderem aos critérios, a otimização do PSP escolherá 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 (caso o Repositório de Consultas esteja habilitado) com muitos planos.

Para planos qualificados, a compilação inicial produz um plano de dispatcher que contém a lógica de otimização PSP chamada de expressão dispatcher. Um plano de dispatcher é mapeado para consultar variantes com base nos predicados de valores de limite do intervalo de cardinalidade.

Terminologia

Expressão dispatcher

Avalia a cardinalidade de predicados com base nos valores de parâmetro de runtime e na execução da rota para diferentes variantes de consulta.

Plano do Dispatcher

Um plano que contém a expressão dispatcher é armazenado em cache para a consulta original. O plano do dispatcher é essencialmente uma coleção dos predicados que foram selecionados pelo recurso, com alguns detalhes adicionais. Para cada predicado selecionado, alguns dos detalhes incluídos no plano do dispatcher são os valores de limite alto e baixo . Esses valores são usados para dividir valores de parâmetro em diferentes buckets ou intervalos. O plano do dispatcher também contém as estatísticas que foram usadas para calcular os valores de limite.

Variante de consulta

Como um plano de dispatcher avalia a cardinalidade de predicados com base nos valores de parâmetro de runtime, ele bucketiza esses valores e gera consultas filho separadas para compilar e executar. Essas consultas filho são chamadas de variantes de consulta. As variantes de consulta têm planos próprios no cache de planos e no Repositório de Consultas.

Intervalo de cardinalidade de predicado

Em runtime, a cardinalidade de cada predicado é avaliada com base nos valores de parâmetro de runtime. O dispatcher coloca os valores de cardinalidade em três intervalos de cardinalidade de predicado no tempo de compilação. Por exemplo, o recurso de otimização do PSP pode criar três intervalos que representarão 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 do PSP gera um plano de shell conhecido como um plano do dispatcher. A expressão dispatcher tem a lógica que coloca consultas em variantes de consulta com base nos valores de runtime dos parâmetros. Quando a execução real começa, o dispatcher executa duas etapas:

• o dispatcher avalia sua expressão dispatcher para o conjunto determinado 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 do PSP consegue ter vários planos para uma só consulta.

Os limites de intervalo de cardinalidade podem ser vistos no 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 é acrescentada à instrução SQL no ShowPlan XML 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:

option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [tableName]. [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 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á igual 0a .
• QueryVariantID é aproximadamente equivalente à combinação de intervalos para todos os predicados selecionados pela otimização PSP. Por exemplo, se uma consulta tiver dois predicados qualificados para PSP e cada predicado tiver três intervalos, haverá nove intervalos de variantes de consulta numerados de 1 a 9.
• intervalo de predicados são as informações de intervalo de cardinalidade predefinidas geradas a partir da expressão dispatcher.

E, dentro do ShowPlan XML 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>

Comentários

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

• Os planos de dispatcher serão recompilados automaticamente se houver alterações significativas na distribuição de dados. Os planos de variante de consulta são recompilados independentemente conforme necessário, como acontece com qualquer outro tipo de plano de consulta, sujeito a eventos de recompilação padrão. Para obter mais informações sobre a recompilação, confira Recompilação de planos de execução.

• O sys.query_store_plan (Transact-SQL) Repositório de Consultas exibição do catálogo do sistema foi alterado para diferenciar entre um plano compilado normal, um plano de dispatcher e um plano de variante de consulta. O novo Repositório de Consultas exibição do catálogo do sistema, sys.query_store_query_variant (Transact-SQL), 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 e column1 = @predicate1column2 = @predicate2 são da mesma tabela, esse é table1. No entanto, se a consulta de exemplo envolver um operador como um , o UNIONPSP 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 escolherá no máximo dois predicados nesse caso, pois o sistema tratará esse cenário como se fossem duas tabelas diferentes. O mesmo comportamento pode ser observado a partir de consultas que se unem por meio de aliases de tabela.

• O ShowPlan XML para uma variante de consulta seria semelhante ao exemplo a seguir, em que ambos os predicados selecionados têm suas respectivas informações adicionadas à dica relacionada a 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 distorção atuais usados pelo recurso de otimização PSP, com um ou mais dos seguintes métodos:

  • Sinalizadores de rastreamento do AVALIADOR de Cardinalidade (CE), como Sinalizador de Rastreamento 9481 (nível global, de sessão ou de consulta)

  • Opções de configuração no escopo do banco de dados que tentam reduzir o modelo ce em uso ou influenciam 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 de 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 white paper Otimizando seus planos de consulta com o white paper avaliador de cardinalidade SQL Server 2014. O modelo ce mais recente 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 a forma 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 de CE herdado para a maioria das cargas de trabalho.

• Atualmente, a otimização PSP compila e executa cada variante de consulta como uma nova instrução preparada, que é um dos motivos pelos quais as variantes de consulta perdem sua associação com qualquer módulo pai se object_id o plano do dispatcher foi baseado em um módulo (ou seja, procedimento armazenado, gatilho, função, exibição e assim por diante). Como uma instrução preparada, o object_id não é nada que possa ser mapeado para um objeto diretamente, sys.objects mas é essencialmente um valor calculado com base em um hash interno do texto em lote. Para obter mais informações, consulte a seção Tabela Retornada da documentação da sys.dm_exec_plan_attributes DMV.

  Os planos de variante de consulta são colocados no repositório de objetos do cache de planos (CACHESTORE_OBJCP) enquanto os planos do dispatcher são colocados no repositório de cache dos 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 ShowPlan XML se a consulta pai fizer parte de um módulo e não de um T-SQL dinâmico ou ad hoc. As estatísticas de desempenho agregadas para procedimentos armazenados em cache, funções e gatilhos podem continuar a ser usadas para suas respectivas finalidades. Estatísticas mais granulares relacionadas à execução, como as encontradas em exibições semelhantes à sys.dm_exec_query_stats DMV, ainda contêm dados para variantes de consulta, no entanto, a associação entre o object_id para variantes de consulta e objetos dentro da sys.objects tabela não se alinham no momento, sem processamento adicional do XML ShowPlan para cada uma das variantes de consulta nas quais estatísticas de runtime mais granulares são necessárias. As informações de estatísticas de tempo de execução e de espera para variantes de consulta podem ser obtidas do Repositório de Consultas sem técnicas adicionais de análise do ShowPlan XML se o Repositório de Consultas estiver habilitado.

• Como as variantes de consulta PSP são executadas como uma nova instrução preparada, o object_id não é exposto automaticamente nas várias DMVs relacionadas ao sys.dm_exec_* cache de planos sem fragmentar o XML do ShowPlan e aplicar técnicas de correspondência de padrões de texto (ou seja, processamento XQuery adicional). Somente os planos do dispatcher de otimização PSP emitem atualmente a ID de objeto pai apropriada. O object_id é exposto no Repositório de Consultas, pois Repositório de Consultas permite um modelo mais relacional do que a hierarquia de cache de planos fornece. Para obter mais informações, consulte a sys.query_store_query_variant de exibição do catálogo do sistema Repositório de Consultas (Transact-SQL).

Considerações

• Para permitir a otimização do PSP, habilite o nível de compatibilidade do 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 de PSP, recomendamos que Repositório de Consultas integração esteja habilitada, ativando o Repositório de Consultas. O exemplo a seguir ativa o Repositório de Consultas para um banco de dados pré-existente chamado MyNewDatabase:

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

Observação

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

• Para desabilitar a otimização do PSP no nível do banco de dados, use a configuração com escopo do banco de dados ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.

• Para desabilitar a otimização do PSP no nível da consulta, use a dica de consulta DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

• Se a detecção de parâmetros estiver desabilitada pelo Sinalizador de Rastreamento 4136, PARAMETER_SNIFFING pela configuração no escopo do banco de dados ou pela dica de consulta, a USE HINT('DISABLE_PARAMETER_SNIFFING') otimização PSP será desabilitada para as cargas de trabalho associadas e os contextos de execução. Para obter mais informações, confira Dicas (Transact-SQL) – Consulta e ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

• O número de variantes de plano exclusivas por dispatcher armazenado no cache de planos é limitado para evitar a sobrecarga 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 alcançar o número máximo padrão de entradas de plano permitidas. Se a taxa de remoção do cache do plano for observavelmente alta ou os tamanhos dos CACHESTORE_OBJCP repositórios de cache e CACHESTORE_SQLCP forem excessivos, considere aplicar o Sinalizador de Rastreamento 174.

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

  • Um exemplo de como o número de planos exclusivos pode exceder o limite de Repositório de Consultas max_plans_per_query padrão seria um cenário no qual você tem o comportamento a seguir. Vamos supor que você tenha uma consulta com uma ID de consulta de 10, que tem dois planos de dispatcher e cada plano de dispatcher tem 20 variantes de consulta cada (40 variantes de consulta no total). O número total de planos para id de consulta 10 é de 40 planos para as variantes de consulta e os dois planos de dispatcher. Também é possível que a própria consulta pai (ID da consulta 10) possa ter 5 planos regulares (não dispatcher). Isso faz 47 planos (40 de variantes de consulta, 2 dispatcher e 5 planos não relacionados ao PSP). Além disso, se cada variante de consulta também tiver uma média de cinco planos, é possível, nesse cenário, ter mais de 200 planos no Repositório de Consultas para uma consulta pai. Isso também dependeria de uma forte distorção de dados nos conjuntos de dados que esta consulta pai de exemplo pode estar referenciando.

• Para cada mapeamento de variante de consulta de um determinado dispatcher:

  • O query_plan_hash é exclusivo. Essa coluna está disponível no e em sys.dm_exec_query_statsoutras exibições de gerenciamento dinâmico e tabelas de catálogo.
  • O plan_handle é exclusivo. Essa coluna está disponível no sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans e em outras Exibições de gerenciamento dinâmico, funções e tabelas de catálogo.
  • O query_hash é comum a outras variantes com mapeamento para o mesmo dispatcher, portanto, é possível determinar o uso de recursos agregados para consultas que diferem apenas pelos valores do parâmetro de entrada. Essa coluna está disponível no sys.dm_exec_query_stats, sys.query_store_query e em outras Exibições de gerenciamento dinâmico e tabelas de catálogo.
  • A opção sql_handle é exclusiva devido a identificadores especiais de otimização do PSP que são adicionados ao texto da consulta durante a compilação. Essa coluna está disponível no sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans e em outras Exibições de gerenciamento dinâmico, funções e tabelas de catálogo. As informações de identificador disponíveis no Repositório de Consultas são as mesmas que as da coluna last_compile_batch_sql_handle na tabela de catálogo sys.query_store_query.
  • O query_id é exclusivo 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.

Imposição de plano no Repositório de Consultas

Usa os mesmos procedimentos armazenados sp_query_store_force_plan e sp_query_store_unforce_plan para operar em planos de dispatcher ou variante.

Se uma variante for forçada, o dispatcher pai não será forçado. Se um dispatcher for forçado, somente variantes desse dispatcher serão consideradas qualificadas para uso:

• Variantes forçadas anteriormente de outros dispatchers tornam-se inativas, mas mantêm a status forçada até que seu dispatcher seja forçado novamente
• Variantes forçadas anteriormente no mesmo dispatcher que havia se tornado inativo serão forçadas novamente

Repositório de Consultas comportamento da dica de consulta

• Quando uma dica de 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 dicas de variante de consulta terão precedência maior se uma dica também tiver sido aplicada à consulta pai no Repositório de Consultas.

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

• Se uma dica de consulta 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 dica for adicionada à consulta pai, o sistema gerará planos não PSP depois que qualquer plano de variante de consulta existente tiver sido removido do cache de planos, pois o recurso PSP não opera em consultas que têm uma RECOMPILE dica.

• Repositório de Consultas resultados da dica podem ser observados usando eventos e query_store_hints_application_failed eventos query_store_hints_application_success estendidos. 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 em uma consulta pai, o catálogo do sistema conterá as informações de dica para a consulta pai, mas não para suas consultas filho, embora as consultas filho herdem a dica da consulta pai.

PSP com dicas de consulta e comportamento de imposição de plano pode ser resumido na tabela a seguir:

Dica ou plano de variante de consulta	Pai tem dica aplicada pelo usuário	O pai tem uma dica aplicada por comentários	O pai forçou manualmente o plano	O pai tem um plano forçado do APC 1
Dica por meio do usuário	Dica de variante de consulta	Dica de variante de consulta	Dica de variante de consulta	N/D
Dica por meio de comentários	Dica de variante de consulta	Dica de variante de consulta	Dica de variante de consulta	N/D
Planejar forçado pelo usuário	Variante de consulta plano forçado	Variante de consulta plano forçado	Variante de consulta plano forçado	Variante de consulta plano forçado
Planejar forçado pelo APRC	Variante de consulta plano forçado	Variante de consulta plano forçado	Variante de consulta plano forçado	Variante de consulta plano forçado
Nenhuma dica ou plano forçado	Dica do usuário pai	Nenhuma dica	Nenhuma ação	Nenhuma ação

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

Eventos estendidos

• parameter_sensitive_plan_optimization_skipped_reason: ocorre quando o recurso do plano com sensibilidade de parâmetros é ignorado. Use esse evento para monitorar o motivo pelo qual a otimização PSP é ignorada.

  A consulta a seguir mostra todos os possíveis motivos pelos quais o PSP foi ignorado:

  SELECT name, 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. Somente canal de depuração. Alguns campos de interesse podem ser:

  • is_query_variant: descreve se este é um plano de dispatcher (pai) ou um plano de variante de consulta (filho)
  • predicate_count: número de predicados selecionados pelo PSP
  • query_variant_id: exibe a ID da variante da consulta. Um valor de 0 significa que o objeto é um plano de dispatcher (pai).

SQL Server Comportamento de auditoria

A otimização PSP fornece dados de auditoria para a instrução de plano dispatcher e quaisquer variantes de consulta associadas ao dispatcher. A additional_information coluna no SQL Server Audit também fornece as informações de pilha T-SQL apropriadas para variantes de consulta. Usando o MyNewDatabase banco de dados como exemplo, se esse banco de dados tiver uma tabela chamada T2 e um procedimento armazenado com o nome de usp_test, após a execução do usp_test procedimento armazenado, o log de auditoria poderá conter as seguintes entradas:

action_id	object_name	instruçã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	exec usp_test 300
SL	T2	selecione * de dbo.t2 em que 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, 1000000.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	selecione * de dbo.t2 em que 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, 1000000.0)))	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>

Problemas conhecidos

Problema	Data descoberta	Status	Data resolvida
A exceção de Violação de Acesso ocorre em Repositório de Consultas no SQL Server 2022 (16.x) em determinadas condições. Você pode encontrar exceções de violação de acesso quando a otimização PSP Repositório de Consultas integração estiver habilitada.	Março de 2023	Tem solução alternativa

Tem solução alternativa

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

Observação

A partir do SQL Server 2022 (16.x) Atualização Cumulativa 4, várias correções para uma condição de corrida que pode levar a uma violação de acesso foram lançadas. Se as violações de acesso relacionadas à otimização PSP com Repositório de Consultas integração ocorrerem após a aplicação da Atualização Cumulativa 4 para SQL Server 2022 (16.x), considere a seção alternativa a seguir.

Esse problema ocorre devido a uma condição de corrida que pode ser causada quando as estatísticas de runtime de uma consulta executada estão sendo mantidas da representação na memória do Repositório de Consultas (encontrada no funcionário de MEMORYCLERK_QUERYDISKSTORE_HASHMAP memória) para a versão em disco do Repositório de Consultas. As estatísticas de runtime, mostradas como Estatísticas de Runtime, são mantidas na memória por um período de tempo, definido pela opção DATA_FLUSH_INTERVAL_SECONDS da SET QUERY_STORE instrução . 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 runtime poderão ser liberadas para o disco mais cedo do que o definido com a opção DATA_FLUSH_INTERVAL_SECONDS . Quando outros Repositório de Consultas threads em segundo plano relacionados à limpeza do plano de consulta Repositório de Consultas (ou seja, STALE_QUERY_THRESHOLD_DAYS e/ou MAX_STORAGE_SIZE_MB Repositório de Consultas opções), consultas do Repositório de Consultas , há um cenário em que uma variante de consulta e/ou sua instrução dispatcher associada podem ser desreferenciadas 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 Repositório de Consultas coleta dados para obter mais informações sobre operações de Repositório de Consultas.

Solução alternativa: as variantes de consulta que estão no Repositório de Consultas podem ser removidas ou o recurso PSP pode ser temporariamente desabilitado no nível da consulta ou do banco de dados até que correções adicionais fiquem disponíveis se o sistema ainda estiver enfrentando violações de acesso em Repositório de Consultas com a integração PSP habilitada após a aplicação da Atualização Cumulativa 4 para SQL Server 2022 (16.x).

• Para desabilitar a otimização do PSP no nível do banco de dados, use a configuração com escopo do banco de dados ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.
• Para desabilitar a otimização do PSP no nível da consulta, use a dica de consulta DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

Para remover todas as variantes de consulta do Repositório de Consultas, não apenas as variantes de consulta que aparecem na exibição de catálogo sys.query_store_query_variant (Transact-SQL), uma consulta semelhante à seguinte pode ser usada. Substitua [<database>] pelo banco de dados apropriado que estava enfrentando problemas:

USE master;
GO

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

USE [<database>];
GO

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

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

Se o Repositório de Consultas for grande ou se o sistema tiver uma carga de trabalho substancial e/ou um alto número de consultas não parametrizadas ad hoc que se qualificam para captura por Repositório de Consultas, desativar o Repositório de Consultas pode levar algum tempo. Para desativar à força o Repositório de Consultas nesses cenários, use o ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) comando em vez disso, no T-SQL de exemplo anterior. Para localizar consultas não parametrizadas, consulte Localizar consultas não parametrizadas em Repositório de Consultas.

Confira também

• Recompilando planos de execução
• Reutilização de Parâmetros e Plano de Execução
• Parametrização Simples
• Parametrização Forçada
• Consulta de dicas

Próximas etapas

• Processamento de consulta inteligente
• Sensibilidade de parâmetros
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)