user2864740's questions

Usamos algumas visualizações 'agregadas' para selecionar várias tabelas usando um discriminador (observação: essas visualizações não são visualizações particionadas, porque o discriminador não está nas tabelas base). Isso normalmente funciona bem ao usar option(recompile), pois o planejador de consulta eliminaráunion all os caminhos não alcançáveis ​​antes da seleção de um plano de consulta.

No entanto, essa otimização de dobra constante parece derrotada ao selecionar o resultado em uma variável escalar. Selecionar o resultado em uma variável de tabela temporária não desotimiza a recompilação.

Aqui está um caso de reprodução no SQL Server 2017:

-- A table, don't need any data.
create table [test].test_table (col1 int, primary key (col1));

-- A simple 'aggregate' view. Using the same table here is irrelevant and,
-- while the view shows the scenario, it might not be required to reproduce the issue.
create view [test].test_view as
select col1, descrim = 1 from [test].test_table
union all
select col1, descrim = 2 from [test].test_table

Consulta normal, que resulta em um plano de consulta otimizado tocando apenas uma das union allramificações:

declare @descrim int = 2;

select count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here "works"

No entanto, assim que uma "selecionar na variável escalar" é usada, o plano se torna desotimizado , pois não elimina a união não utilizada. (O plano ainda é otimizado corretamente ao usar um valor literal no texto da consulta.)

declare @descrim int = 2;
declare @brokeit int;

select @brokeit = count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here does NOT optimize plan for @descrim!

1. Essa desotimização é "esperada"?

2. Onde esse comportamento significativo de desotimização em relação a option(recompile)e/ou seleção em uma variável escalar está documentado ou discutido em profundidade?

3. Existe uma maneira simples de obter um plano otimizado para recompilação select @x = ..sem usar uma tabela temporária (variável)?

Embora durante a execução da consulta union allisso impeça o acesso de E/S real ao artefato secundário, esse ainda é um problema com a geração do plano de consulta. No caso de erro específico que gera essa pergunta, deixar várias tabelas para consideração impede o SQL Server de escolher um plano de busca apropriado e as opções de plano resultantes são escolhas muito ruins no domínio fornecido.

O primeiro plano "bom":

O segundo e "ruim" plano:

Esse plano "ruim" também tem um aviso de conversão implícito, me fazendo suspeitar que a seleção em uma variável escalar pode estar ignorando muitas otimizações diferentes - ou até mesmo ignorando a option(recompile)dica completamente.

As declarações executadas com sp_executesqlparecem ser geralmente "desvinculadas" do procedimento em que aparecem. Por "bind", quero dizer simplesmente "associado ao objeto de chamada".

O objetivo é simplificar a associação de instruções no Query Store:

select object_name(q.object_id) as [Statement Context]
from sys.query_store_query q
where 1=1
    and object_name(q.object_id) like 'This will be the procedure name for ''normal'' statements'

Embora seja possível incorporar comentários nas declarações que aparecerão no query_sql_text, isso parece um pouco mais hack.

Além disso, parece que precisariasp_executesql de alguma forma de ligação de contexto, pois o SQL dinâmico pode acessar tabelas temporárias não globais no escopo circundante: sem uma ligação, como o SQL Server pode garantir a validade e estabilidade do esquema da tabela temporária nos planos criados ?