Início / dba / Perguntas / 123863
Accepted
David Faivre
Asked: 2015-12-17 11:10:18 +0800 CST

Índice de coluna computado não usado

  • 772

Eu quero ter uma pesquisa rápida com base em se duas colunas são iguais. Tentei usar uma coluna computada com um índice, mas o SQL Server parece não usá-lo. Se eu apenas usar uma coluna de bits preenchida estaticamente com um índice, obterei a busca de índice esperada.

Parece que há algumas outras perguntas como essa por aí, mas nenhuma se concentrou em por que um índice não seria usado.

Tabela de teste:

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    )

create index ix_DiffPersisted on Diffs (DiffPersisted)
create index ix_DiffComp on Diffs (DiffComp)
create index ix_DiffStatic on Diffs (DiffStatic)

E a Consulta:

select Id from Diffs where DiffPersisted = 1
select Id from Diffs where DiffComp = 1
select Id from Diffs where DiffStatic = 1

E os planos de execução resultantes: Plano de execução

sql-server index-tuning

2 respostas

  1. Best Answer

    Tente com COALESCEem vez de ISNULL. Com ISNULL, o SQL Server não parece capaz de enviar um predicado contra o índice mais restrito e, portanto, precisa verificar o cluster para localizar as informações.

    CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS COALESCE(convert(bit, case when [DataA] is null 
      and [DataB] is not null then 1 when [DataA] <> [DataB] 
      then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS COALESCE(convert(bit, case when [DataA] is null 
      and [DataB] is not null then 1 when [DataA] <> [DataB] 
      then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    );

    Dito isso, se você ficar com uma coluna estática, um índice filtrado pode fazer mais sentido e terá custos de E/S mais baixos (tudo dependendo de quantas linhas normalmente correspondem ao predicado do filtro), por exemplo:

    CREATE INDEX ix_DiffStaticFiltered 
  ON dbo.Diffs(DiffStatic)
  WHERE DiffStatic = 1;
    • 11

  2. Esta é uma limitação específica da lógica de correspondência de coluna computada do SQL Server, quando um mais externo ISNULLé usado e o tipo de dados da coluna é bit.

    Relatório de erro

    Para evitar o problema, qualquer uma das seguintes soluções alternativas pode ser empregada:

    1. Não use um extremo ISNULL(a única maneira de criar uma coluna computada NOT NULL).
    2. Não use o bittipo de dados como o tipo final da coluna computada.
    3. Faça a coluna calculada PERSISTEDe ative o sinalizador de rastreamento 176 .

    Detalhes

    O cerne da questão é que, sem o sinalizador de rastreamento 176, todas as referências de coluna computadas em uma consulta (mesmo persistidas) são sempre expandidas na definição subjacente muito cedo na compilação da consulta.

    A ideia da expansão é permitir simplificações e reescritas que funcionam apenas na definição, não apenas no nome da coluna. Por exemplo, pode haver predicados na consulta que faz referência a essa coluna computada que pode tornar parte do cálculo redundante ou, de outra forma, mais restrito.

    Depois que as simplificações e reescritas iniciais são consideradas, a compilação da consulta tenta corresponder as expressões na consulta às colunas computadas (todas as colunas computadas, não apenas aquelas originalmente encontradas no texto da consulta).

    As expressões de coluna computadas inalteradas correspondem à coluna computada original sem problemas na maioria dos casos. Parece haver um bug quando específico para corresponder a uma expressão do bittipo, com um ISNULL. A correspondência não é bem-sucedida neste caso específico, mesmo quando um exame detalhado dos componentes internos mostra que deve ser bem-sucedida.

    • 6

relate perguntas