Início / dba / Perguntas / 208897
Accepted
Mattia Nocerino
Asked: 2018-06-07 05:10:34 +0800 CST

Consulta usando um índice filtrado, mas um número estimado incorreto de linhas

  • 772

Eu tenho uma tabela com um nonclustered indexem um datetime2campo.

Nessa mesma tabela tenho um campo (char[1])que é usado para deletar registros logicamente e pode ter 2 valores distintos: A(ativo) ou D(excluído).

Existem 451.047 registros com o datetime2campo definido como, NULLmas apenas 7.095 estão marcados como A.

Toda consulta na aplicação procura apenas registros ativos , então, toda consulta que está procurando NULLsno campo datetime estava recebendo estimativas muito ruins e, com isso, planos de execução ruins.

Exemplo simples:Plano sem índice filtrado: veja as linhas estimadas versus as linhas reais

Decidi então criar um índice não clusterizado filtrado, mas as estimativas ainda estavam incorretas:Planejar usando um índice filtrado, ainda recebendo estimativas erradas

Parece que ainda recebo as estimativas antigas, mesmo que a consulta esteja usando corretamente o índice filtrado. Alguém sabe o motivo deste comportamento?

Estas são as novas estatísticas do índice filtrado:Estatísticas de índice filtradas

Definição da tabela:

CREATE TABLE [TYDATPRD].[HAND00F](
    [STDRECSTS] [char](1) NULL,
    [HDHAND] [numeric](14, 0) IDENTITY(1,1) NOT NULL,
    [HDCHKINDT] [datetime2](7) NULL,
    --lots of other columns which I don't think are needed

    CONSTRAINT [PK_TYDATPRD_HAND00F] PRIMARY KEY CLUSTERED (
        [HDHAND] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

Definição de índice filtrado não clusterizado:

CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] ON [TYDATPRD].[HAND00F] (
    [HDCHKINDT] ASC
)
WHERE [STDRECSTS]='A'
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO
sql-server-2016 index-tuning

1 respostas

  1. Best Answer

    Isso não é intuitivo, mas tente adicionar a char[1]coluna à definição do índice filtrado:

    CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] 
ON [TYDATPRD].[HAND00F] 
(
    [HDCHKINDT], STDRECSTS
)
WHERE [STDRECSTS] = 'A'
WITH (DROP_EXISTING = ON) ON [INDEXES];

    Em meus testes, isso levou às estimativas corretas.

    A propósito, notei que os planos em sua pergunta eram diferentes - um estava devidamente parametrizado e o outro tinha uma constante. Tenha muito cuidado sobre como você está fazendo testes locais com variáveis ​​locais e constantes digitadas, especialmente se no mundo real isso for feito por parâmetros para procedimentos armazenados.

    Além disso, tenha muito cuidado com os literais case - na cláusula where como = 'a'e = 'A'gerarão planos diferentes se não forem parametrizados, porque o texto da consulta é diferente. Não importa que em alguns agrupamentos eles não produzam os mesmos resultados. (E essa char(1)coluna precisa permitir NULL?)

    • 3

relate perguntas