Início / dba / Perguntas / 281781
Accepted
Greg
Asked: 2020-12-18 17:18:22 +0800 CST

Usando um índice filtrado ao definir uma variável

  • 772

Eu sei de outras perguntas e postagens que, quando o SQL compila um plano de consulta, ele só pode usar um índice filtrado se o índice filtrado for garantido para poder ser usado toda vez que a consulta for executada. Isso significa que você não pode usar uma variável na cláusula where porque às vezes ela pode usar o índice filtrado e às vezes não.

Uma maneira de contornar isso é usar OPTION(RECOMPILE), para que nas vezes em que ele possa usá-lo, ele obtenha o índice filtrado.

Fazendo alguns testes, descobri que esta consulta pode usar um índice filtrado (observe, estou forçando o índice apenas para provar um ponto):

SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

No entanto, se eu quiser atribuir o resultado a uma variável, estou sem sorte:

SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

resulta em:

Msg 8622, Level 16, State 1, Line 15 O processador de consultas não pôde produzir um plano de consulta devido às dicas definidas nesta consulta. Reenvie a consulta sem especificar nenhuma dica e sem usar SET FORCEPLAN.

A consulta pode usar claramente o índice filtrado enquanto executa find quando não quero salvar a saída em uma variável.

Eu tenho maneiras de reescrever essa consulta em código rígido @variablepara remover o problema, mas alguém pode explicar por que a primeira consulta pode usar o índice filtrado, mas a segunda consulta não pode?

sql-server index

4 respostas

  1. Best Answer

    A otimização que permite usar o índice filtrado com uma RECOMPILEdica é chamada de "otimização de incorporação de parâmetros". Esse é um processo em que o analisador de consulta substitui a referência da variável pelo valor literal dentro da variável.

    Veja esta postagem de Paul White pelo motivo pelo qual não funciona no seu segundo caso: Parameter Sniffing, Embedding e RECOMPILE Options

    Há um cenário em que o uso OPTION (RECOMPILE)não resultará na aplicação da otimização de incorporação de parâmetros. Se a instrução for atribuída a uma variável, os valores de parâmetro não serão incorporados:

    • 12

  2. Isso deve funcionar:

    declare @myVariable int
declare @variable int = 9
declare @OutputVariable date

declare @t table(MaxDate date)

insert into @t(MaxDate) 
SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

select @OutputVariable MaxDate from @t

    E você sempre pode usar SQL dinâmico com valores literais em vez de tentar obter um parâmetro para transformar em um literal com OPTION (RECOMPILE). por exemplo

    declare @variable int = 9
declare @OutputVariable date

declare @sql nvarchar(max) = concat(N'
SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = ', @variable)

exec sp_executesql @sql, N'@OutputVariable date out', @OutputVariable = @OutputVariable out
select @OutputVariable
    • 4

  3. A razão pela qual o otimizador não pode gerar um plano é porque ele não tem como garantir que o valor da variável realmente corresponda ao predicado do filtro. O plano de consulta é pré-compilado e armazenado e não é seguro usar esse índice em todos os casos. Infelizmente, o otimizador não contém neste momento lógica para bifurcar um índice filtrado apenas no caso de o filtro corresponder, o que é uma pena. Você pode fazer isso sozinho, como o SQLPro demonstrou.

    Um problema com isso é: os índices filtrados geralmente mudam, com base no que o DBA acha que é melhor. Uma opção que usei com sucesso é armazenar em cache o predicado de índice filtrado do lado do cliente e construir dinamicamente a consulta com base nisso, enquanto ainda passa pela variável. Isso evita problemas de inchaço do cache do plano e injeção de SQL, pois a consulta ainda é parametrizada e não muda, a menos que o índice seja alterado. Você também pode fazer isso sp_executesqlde dentro de um procedimento armazenado.

    Para obter o predicado de filtro, execute esta consulta:

      SELECT ISNULL(
      (SELECT i.filter_definition
      FROM sys.indexes i
      WHERE i.object_id = OBJECT_ID(@tablename) AND
          i.name = @indexname AND has_filter = 1),
      '(1=1)');

    Isso também trata do caso em que o filtro, ou mesmo todo o índice, é descartado.

    • 0

  4. Você pode tentar esta consulta:

    SELECT @OutputVariable =
(SELECT MAX(table1.SomeDateField)
FROM   dbo.table1
WHERE  table1.filteredColumn = @variable
  AND  table1.filteredColumn = the_filtered_value
UNION ALL
SELECT MAX(table1.SomeDateField)
FROM   dbo.table1
WHERE  table1.filteredColumn = @variable
  AND  NOT table1.filteredColumn = the_filtered_value)

    A razão pela qual isso funciona é porque você está bifurcando o índice filtrado, ou seja, usando-o apenas em um caso em que a variável corresponde ao predicado do filtro. O otimizador não pode usar o índice sem isso porque não pode garantir que o valor corresponda ao predicado.

    • -1

relate perguntas