Início / dba / Perguntas / 346346
Accepted
J. Mini
Asked: 2025-04-30 02:19:23 +0800 CST

OPTIMIZE FOR UNKNOWN faz alguma coisa para variáveis/parâmetros com valor de tabela?

  • 772

Tenho uma consulta propensa à sensibilidade de parâmetros e que sofre com seu parâmetro com valor de tabela. Sou preguiçoso e só quero resolver isso com dicas de consulta. Quando estou preguiçoso, consigo resolver a sensibilidade de parâmetros com OPTION (OPTIMIZE FOR UNKNOWN). Isso me gera três perguntas muito relacionadas:

  1. Tem algum impacto em variáveisOPTION (OPTIMIZE FOR UNKNOWN) ​​com valor de tabela ?
  2. Tem algum impacto nos parâmetrosOPTION (OPTIMIZE FOR UNKNOWN) com valor de tabela ?
  3. Quais outras dicas de consulta podem ser usadas para influenciar estimativas de cardinalidade para parâmetrosOPTION(RECOMPILE) com valores de tabela ?

Tanto meus próprios experimentos quanto a documentação sugerem que essa é uma causa perdida. Parâmetros com valor de tabela estão sujeitos à detecção de parâmetros, mas tudo o que é detectado é a cardinalidade, então OPTION (OPTIMIZE FOR UNKNOWN)não deve fazer nada.

Quanto à questão de usar outras dicas de tabela, sei que posso alterar a estimativa para operações no parâmetro com valor de tabela (por exemplo, OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')), mas não acho que as dicas possam influenciar a estimativa de cardinalidade para o parâmetro em si.

sql-server

1 respostas

  1. Best Answer

    opa

    Parece que OPTIMIZE FOR UNKNOWNnão tem efeito algum nas variáveis ​​ou parâmetros da tabela. Na verdade, se você tentar especificar OPTIMIZE FOR (@data UNKNOWN)ou OPTIMIZE FOR (@data = NULL)então você obtém

    Msg 137 Level 16 State 1 Line 7
Must declare the scalar variable "@data".

    o que nos diz que essa dica é destinada apenas para variáveis ​​escalares, totalmente não documentadas.

    Historicamente, as variáveis ​​de tabela não recebiam nenhuma estimativa de cardinalidade e tinham uma estimativa de 1 ou 100 linhas. A Compilação Diferida de Variáveis ​​de Tabela foi introduzida usando o sinalizador de rastreamento 2453 e, a partir de 2019, como um banco de dados que você pode desativar usando uma dica. Isso geralmente fará com que as estimativas estejam corretas, o que, no seu caso, parece ser indesejado.

    entendeu a dica

    • Desativar o TF2453 ou usar a dica de consulta USE HINT (N'DISABLE_DEFERRED_COMPILATION_TV')só funciona em variáveis ​​de tabela, não em parâmetros.
    • ROBUST PLANe FAST 1não afetam a estimativa de cardinalidade aqui.

    o mal

    Você pode inserir tudo em uma variável de tabela. Por padrão, isso obtém uma estimativa de 1 ou 100 linhas, dependendo da versão.

    Você também precisa desativar o TF2453, se estiver ativado. Em 2019 e versões posteriores, você precisa desativar a Compilação de Variáveis ​​de Tabela Adiada. Você poderia usar QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100, mas isso é um pouco genérico.

    CREATE OR ALTER PROCEDURE p
  @data dbo.t READONLY
AS

DECLARE @data2 dbo.t;
INSERT @data2
SELECT *
FROM @data;

SELECT COUNT(*) AS c
FROM @data2
OPTION (USE HINT (N'DISABLE_DEFERRED_COMPILATION_TV'));

    Isso tem a desvantagem óbvia de precisar copiar todo o TVP, que pode ser muito grande.

    o feio

    Você pode usar sp_preparee sp_executecom SQL dinâmico para forçar a compilação sem estimativa de cardinalidade, conforme observado no artigo de @PaulWhite . Não há cópia neste caso.

    CREATE OR ALTER PROCEDURE p
  @data dbo.t READONLY
AS

DECLARE @handle int;
EXEC sp_prepare @handle OUT,
  N'@data dbo.t READONLY',
  N'
SELECT COUNT(*) AS c
FROM @data;
';
EXEC sp_execute @handle, @data = @data;

    Isso só funciona com instruções simples, caso contrário a compilação será adiada e o parâmetro será detectado.

    Mas isso torna o procedimento um pouco confuso para escrever e depurar.

    o bom

    Resolva sua dúvida. Por que OPTION (RECOMPILE)o problema não é resolvido ou o que está atrapalhando o otimizador, mesmo que ele tenha a estimativa correta? Resolva isso.

    Ou simplesmente tenha um bom plano e force-o usando o Query Store.

    Você pode ver as opções neste violino .

    • 7

relate perguntas