Otimização do plano confidencial de parâmetro

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Banco de dados SQL no Microsoft Fabric

A otimização do PSP (plano de sensibilidade de parâmetro) faz parte da família de recursos de processamento de consulta inteligente. Ela trata o cenário em que um só plano armazenado em cache de 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.

Veja mais informações sobre soluções alternativas para esse cenário de problema em:

• Investigue e resolva problemas de sensibilidade de 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 comportam tamanhos de dados diferentes com base nos valores de parâmetro de runtime fornecidos pelo cliente.

Noções básicas sobre parametrização

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

Você também pode substituir o comportamento padrão da parametrização simples do SQL Server especificando que todas as instruções SELECT, INSERT, UPDATE e DELETE em um banco de dados parametrizado e sejam 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 até três, de todos os predicados disponíveis, indicando os predicados parametrizados com maior risco. 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.

Nos planos qualificados, a compilação inicial produz um plano de dispatcher que contém a lógica de otimização do PSP chamada 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 do 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 de dispatcher

Um plano que contém a expressão do dispatcher é armazenado em cache para a consulta original. O plano de dispatcher é basicamente uma coleção dos predicados selecionados pelo recurso com alguns detalhes adicionais. Para cada predicado selecionado, alguns dos detalhes incluídos no plano de dispatcher são os valores de limite alto e baixo. Esses valores são usados para dividir os 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

Enquanto o plano de dispatcher avalia a cardinalidade de predicados com base nos valores de parâmetro de runtime, ele classifica em buckets esses valores e gera consultas filho separadas para compilação e execução. 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 do predicado

Em runtime, a cardinalidade de cada predicado é avaliada com base nos valores de parâmetro de runtime. O dispatcher agrupa em buckets 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 do dispatcher tem a lógica que classifica em buckets as 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 a expressão do dispatcher em relação ao 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 do intervalo de cardinalidade podem ser vistos no XML ShowPlan de um plano de dispatcher:

<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 do PSP é anexada à instrução SQL no XML ShowPlan de uma variante de consulta. A dica não pode ser usada diretamente e não será analisada se for 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, supondo-se que a instrução foi gerada com base em 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 a 0.
• QueryVariantID equivale aproximadamente à combinação de intervalos de todos os predicados que a otimização do PSP selecionou. Por exemplo, se a 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 de predicados gerada com base na expressão do dispatcher.

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

• Atualmente, o recurso de otimização do PSP 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 recompiladores de forma independente conforme necessário, assim como ocorre com qualquer outro tipo de plano de consulta, sujeitos a eventos de recompilação padrão. Para obter mais informações sobre a recompilação, confira Recompilação de planos de execução.

• A exibição de catálogo do sistema do Repositório de Consultas sys.query_store_plan (Transact-SQL) foi alterada para diferenciar 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 (Transact-SQL), contém informações sobre os relacionamentos pai-filho entre as consultas parametrizadas originais (também conhecidas como consultas pai), os planos de dispatcher e suas variantes de consulta filho.

• Quando múltiplos predicados fazem parte da mesma tabela, a otimização do 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, como column1 = @predicate1 e column2 = @predicate2 são da mesma tabela, table1, o recurso avaliará apenas o predicado mais assimétrico. Porém, se a consulta de exemplo envolver um operador como UNION, o PSP avaliará mais de um predicado. Por exemplo, se a 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, porque o sistema trata esse cenário como duas tabelas diferentes. Observamos o mesmo comportamento em consultas que se autoassociam por aliases de tabela.

• O XML ShowPlan para uma variante de consulta seria semelhante ao exemplo a seguir, em que as respectivas informações dos dois predicados selecionados são 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 assimetria atuais usados pelo recurso de otimização PSP, com um ou mais destes métodos:

  • Sinalizadores de rastreamento do CE (avaliador de cardinalidade), como o 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 diminuir o modelo de CE em uso ou influenciar as suposições que o modelo de CE faz em relação à independência de vários predicados. Isso é útil especialmente nos casos em que não existem estatísticas de múltiplas colunas, o que afeta a capacidade do PSP de avaliar a candidatura desses predicados.

  • Para obter mais informações, consulte a seção Pressuposição de correlação aumentada para múltilplos predicados do whitepaper Otimizar planos de consulta com o avaliador de cardinalidade do SQL Server 2014. O novo modelo de EC tenta supor alguma correlação e menor independência para a conjunção e disjunção de predicados. Usar o modelo CE herdado pode afetar a forma de calcular a seletividade dos predicados em um cenário de junção multicoluna. Essa ação só deve ser considerada para cenários específicos, e não é recomendável usar o modelo de CE herdado com a maioria das cargas de trabalho.

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

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

• Como as variantes de consulta PSP são executadas como uma nova instrução preparada, object_id não é automaticamente exposta nas várias DMVs sys.dm_exec_* relacionadas ao cache de planos sem destruir o XML ShowPlan e aplicar técnicas de correspondência de padrão de texto (ou seja, processamento XQuery adicional). Atualmente, apenas os planos de dispatcher de otimização do PSP emitem a ID de objeto pai apropriada. A object_id é exposta no Repositório de Consultas, pois o Repositório de Consultas permite um modelo mais relacional do que a hierarquia de cache de planos fornece. Veja mais informações, na exibição de catálogo do sistema do Repositório de Consultas sys.query_store_query_variant (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 mais insights sobre o recurso de otimização do PSP, recomendamos que a integração do Repositório de Consultas seja habilitada ativando o Repositório de Consultas. Os seguintes exemplos ativam o Repositório de Consultas de 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 já é habilitado por padrão em 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, pela configuração no escopo do banco de dados PARAMETER_SNIFFING ou pela dica de consulta USE HINT('DISABLE_PARAMETER_SNIFFING'), a otimização do PSP será desabilitada para as cargas de trabalho e os contextos de execução associados. 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 atingir o número máximo padrão de entradas de plano permitidas. Se a taxa de remoção de cache de planos for perceptivelmente alta ou se os tamanhos dos armazenamentos de cache CACHESTORE_OBJCP 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, pode haver total de 200 planos por consulta no Repositório de Consultas. Esse número inclui todos os planos de variantes de consulta de todos os dispatchers que pertencem a uma consulta pai. Considere aumentar a opção de configuração do Repositório de Consultas max_plans_per_query.

  • Um exemplo de como o número de planos exclusivos pode exceder o limite de max_plans_per_query padrão do Repositório de Consultas seria um cenário no qual você teria 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 o 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 de consulta 10) possa ter cinco planos regulares (que não sejam dispatcher). Isso totaliza 47 planos (40 de variantes de consulta, 2 de dispatcher e 5 planos não relacionados a PSP). Além disso, se cada variante de consulta também tiver uma média de cinco planos, nesse cenário é possível ter mais de 200 planos no Repositório de Consultas para uma consulta pai. Isso também dependeria de uma distorção de dados pesada no(s) conjunto(s) de dados que essa 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 sys.dm_exec_query_stats e em outras 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 a 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 ficam inativas, mas retêm o status forçado 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

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 que não é PSP. As dicas de variantes de consulta têm precedência maior se a dica também tiver sido aplicada à consulta pai no Repositório de Consultas.

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

• Se a dica de consulta do Repositório de Consultas for removida da consulta pai, ela também será removida das consultas filho (variantes de consulta).

• Se a dica RECOMPILE for adicionada à consulta pai, o sistema gerará planos não relacionados a PSP depois que os planos de variantes de consulta tiverem sido removidos do cache de planos, pois o recurso PSP não opera em consultas que tenham uma dica RECOMPILE.

• Podemos observar os resultados das dicas do Repositório de Consultas usando eventos estendidos query_store_hints_application_success e eventos query_store_hints_application_failed. Para a tabela sys.query_store_query_hints, contém informações sobre a dica de consulta aplicada. Se a dica for aplicada apenas a uma consulta pai, o catálogo do sistema conterá as informações de dica para a consulta pai, mas não para as consultas filho, embora as consultas filho herdem a dica da consulta pai.

O PSP com dicas de consulta e comportamento de forçar plano pode ser resumido nesta tabela:

Dica ou plano de variante de consulta	O pai tem dica aplicada pelo usuário	O pai tem uma dica aplicada ao feedback	O pai forçou o plano manualmente	O 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/D
Dica via feedback	Dica de variante de consulta	Dica de variante de consulta	Dica de variante de consulta	N/D
Plano 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
Plano forçado pelo APC	Variante de consulta plano forçado	Variante de consulta plano forçado	Variante de consulta plano forçado	Variante de consulta plano forçado
Sem dica nem plano forçado	Dica do usuário pai	Sem dicas	Nenhuma ação	Nenhuma ação

¹ Componente de correção automática do 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 para a otimização do PSP ser ignorada.

  A consulta a seguir mostra todas as possíveis razões para o PSP ter sido ignorado:

  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 do PSP. Somente canal de depuração. Estes podem ser campos relevantes:

  • is_query_variant: descreve se é 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 de consulta. O valor 0 significa que o objeto é um plano de dispatcher (pai).

Comportamento de auditoria do SQL Server

A otimização do PSP fornece dados de auditoria para o extrato do plano de dispatcher e as variantes de consulta associadas ao dispatcher. A coluna additional_information na auditoria do SQL Server também fornece as informações de pilha T-SQL apropriadas para variantes de consulta. Usando o banco de dados MyNewDatabase como exemplo, se esse banco de dados tiver uma tabela chamada T2 e um procedimento armazenado com o nome 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	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	select * from 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 * from 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>

Problemas conhecidos

Problema	Data descoberta	Status	Data resolvida
A exceção de violação de acesso ocorre no Repositório de Consultas no SQL Server 2022 (16.x) em determinadas condições. É possível encontrar exceções de violação de acesso quando a integração do Repositório de Consultas de otimização do PSP está habilitada. Para obter mais informações, consulte a atualização em Parameter Sensitive Plan Optimization, Why?.	Março de 2023	Resolvido	Agosto de 2023 (CU 7)

Resolvido

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

Observação

Desde a atualização cumulativa 7 do SQL Server 2022 (16.x), foram lançadas várias correções para uma condição de corrida que pode gerar violação de acesso. Se houver violações de acesso relacionadas à otimização do PSP com a integração do Repositório de Consultas após a aplicação da atualização cumulativa 7 do SQL Server 2022 (16.x), considere a seção de solução alternativa a seguir.

Esse problema ocorre devido a uma condição de corrida que pode ser causada quando as estatísticas de runtime da consulta executada estão sendo persistentes da representação na memória do Repositório de Consultas (encontrada no administrador de memória MEMORYCLERK_QUERYDISKSTORE_HASHMAP) 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 definido com a opção DATA_FLUSH_INTERVAL_SECONDS da instrução SET QUERY_STORE (o valor padrão é 15 minutos). Você pode usar a caixa de diálogo do Repositório de Consultas do Management Studio para inserir um valor para o Intervalo de liberação de dados (minutos), que é convertido internamente para segundos. Se o sistema estiver enfrentando demanda de memória, as estatísticas de tempo de execução poderão ser liberadas para o disco antes do que foi definido com a opção DATA_FLUSH_INTERVAL_SECONDS. Quando threads adicionais em segundo plano do Repositório de Consultas relacionados ao plano de consulta do Repositório de Consultas limpam (ou seja, opções STALE_QUERY_THRESHOLD_DAYS ou MAX_STORAGE_SIZE_MB do Repositório de Consultas) consultas do Repositório de Consultas, há um cenário em que a variante de consulta ou sua instrução de dispatcher associada podem ser desreferenciadas prematuramente. Isso pode resultar em violação de acesso durante as operações de inserção ou exclusão de variantes de consulta no Repositório de Consultas.

Confira a seção Comentários do artigo Como o repositório de consultas coleta dados para ver mais informações sobre as operações do 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 desabilitado temporariamente no nível da consulta ou do banco de dados até que outras correções estejam disponíveis, se o sistema ainda estiver enfrentando violações de acesso no Repositório de Consultas com a integração do PSP habilitada após a aplicação da atualização cumulativa 7 do 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 exibidas na exibição de catálogo sys.query_store_query_variant (Transact-SQL), você pode usar uma consulta semelhante à mostrada a seguir. Substitua [<database>] pelo banco de dados 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 considerável ou um número alto de consultas ad hoc não parametrizadas que se qualificam para captura pelo Repositório de Consultas, a desativação do Repositório de Consultas poderá demorar. Para desativar o Repositório de Consultas à força nesses cenários, use o comando ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED), no exemplo anterior de T-SQL. Para localizar consultas não parametrizadas, confira Localizar consultas não parametrizadas no Repositório de Consultas.

Conteúdo relacionado

• Recompilar planos de execução
• Reutilização de Parâmetros e Plano de Execução
• Parametrização Simples
• Parametrização Forçada
• Dicas (Transact-SQL) – Consulta
• Processamento inteligente de consultas em bancos de dados SQL
• Sensibilidade de parâmetros
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)