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 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 a0. - 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,
 AgentId,
 MLSLinkId,
 ListingPrice,
 ZipCode,
 Bedrooms,
 Bathrooms
FROM dbo.Property
WHERE AgentId = @AgentId
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, comocolumn1 = @predicate1ecolumn2 = @predicate2são da mesma tabela,table1, o recurso avaliará apenas o predicado mais assimétrico. Porém, se a consulta de exemplo envolver um operador comoUNION, o PSP avaliará mais de um predicado. Por exemplo, se a consulta tiver características semelhantes aSELECT * 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, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty a join PropertyDetails b on a.PropertyId = b.PropertyId
WHERE AgentId = @AgentId and Property_id=@Property_id
UNION
 SELECT c.PropertyId, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty c join PropertyDetails d on c.PropertyId = d.PropertyId
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_idse o plano de dispatcher se basear em um módulo (ou seja, procedimento armazenado, gatilho, função, exibição etc.). Como instrução preparada, oobject_idnão é nada que possa ser mapeado para um objeto diretamente emsys.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 DMVsys.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á oobject_iddo 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 à DMVsys.dm_exec_query_stats, ainda contêm dados para variantes de consulta, mas a associação entreobject_iddas variantes de consulta e os objetos da tabelasys.objectsnã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_idnão é automaticamente exposta nas várias DMVssys.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. Aobject_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_SNIFFINGou pela dica de consultaUSE 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_OBJCPeCACHESTORE_SQLCPforem 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 Consultasmax_plans_per_query.- Um exemplo de como o número de planos exclusivos pode exceder o limite de
max_plans_per_querypadrã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.
- Um exemplo de como o número de planos exclusivos pode exceder o limite de
Para cada mapeamento de variante de consulta de um determinado dispatcher:
- O
query_plan_hashé exclusivo. Essa coluna está disponível nosys.dm_exec_query_statse em outras exibições de gerenciamento dinâmico e tabelas de catálogo. - O
plan_handleé exclusivo. Essa coluna está disponível nosys.dm_exec_query_stats,sys.dm_exec_sql_text,sys.dm_exec_cached_planse 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 nosys.dm_exec_query_stats,sys.query_store_querye 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 nosys.dm_exec_query_stats,sys.dm_exec_sql_text,sys.dm_exec_cached_planse 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 colunalast_compile_batch_sql_handlena tabela de catálogosys.query_store_query. - O
query_idé exclusivo no Repositório de Consultas. Esta coluna está disponível nosys.query_store_querye em outras tabelas de catálogo do Repositório de Consultas.
- O
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
RECOMPILEfor 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 dicaRECOMPILE.Podemos observar os resultados das dicas do Repositório de Consultas usando eventos estendidos
query_store_hints_application_successe eventosquery_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 |
1 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 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 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
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de