Início / dba / Perguntas / 127096
Accepted
user541852587
Asked: 2016-01-24 05:29:20 +0800 CST

O otimizador de consulta irá ignorar um índice fragmentado?

  • 772

Cenário: Tenho uma tabela OLTP pesada com um índice. Vejo muitas inserções, atualizações e exclusões e o índice fragmenta-se fortemente em um dia ou menos. Enquanto no primeiro dia da criação do índice, o otimizador usa o índice, no segundo ou terceiro dia, o otimizador o ignora totalmente. Isso é exatamente para a mesma consulta.

Pergunta na minha cabeça: por que alguns planos de consulta pulariam o índice, já que o índice é criado para ajudar a otimizar esses planos?

Pergunta para esta postagem: o otimizador pode ignorar um índice altamente fragmentado, como um cenário em que temos 1 bilhão de registros e um índice é criado e, duas horas depois, todos os bilhões de registros são removidos e temos quinhentos milhões de novos registros?

Estou começando a pensar que adicionar um índice a esta tabela não ajudará em nada, devido à natureza da tabela (dados entram rapidamente, dados saem rapidamente), mas só quero entender por que um dia, o otimizador irá usar o índice em seus planos, mas dia dois, não.

sql-server sql-server-2012

2 respostas

  1. Best Answer

    AFAIK, o otimizador não está ciente da fragmentação do índice. Isso pode ser um problema se ele escolher um plano que verifique um índice fragmentado.

    No entanto, o otimizador está ciente do tamanho dos dados alocados. Se as páginas de índice tiverem muito espaço livre (possivelmente devido à fragmentação interna), isso torna o índice menos provável de ser usado. 50% de espaço vazio significa o dobro da quantidade de E/S para escanear. No entanto, para acesso aleatório, isso não deve importar de forma significativa.

    Este não é um grande efeito, no entanto. Isso pode explicar o que você está vendo.

    Se esse pequeno efeito inverter o plano de consulta para não usar o índice, o índice nunca foi muito bom aos olhos do otimizador de consulta. Isso pode ser uma dica de que você pode melhorá-lo.

    Além disso, o otimizador parece ter uma estimativa de quanto do índice é armazenado em cache no buffer pool. Existem algumas referências a isso nos planos de execução do XML. Não tenho conhecimento detalhado disso.

    Estou começando a pensar que adicionar um índice a esta tabela não ajudará em nada

    Eu não iria tão longe. Talvez tudo o que você precisa seja uma reconstrução ou uma sequência drop-DML-create nos lugares certos? Ou talvez seja apenas um problema de ajuste de consulta (faça uma nova pergunta com o plano de execução real incluído).

    • 6

  2. A primeira coisa que vem à mente são as estatísticas desatualizadas, não a fragmentação do índice como tal.

    Logo após o índice ser (re)construído, as estatísticas associadas ao índice são precisas; o intervalo do histograma abrange todos os valores. À medida que os dados mudam na tabela, as estatísticas não são atualizadas imediatamente. Não me lembro agora dos limites exatos, ou seja, quantas linhas devem ser excluídas/inseridas antes que a atualização automática das estatísticas seja realizada.

    Observei um comportamento semelhante em nosso sistema. O fluxo de trabalho simplificado em nosso sistema é o seguinte.

    Temos uma tabela com cerca de 100 milhões de linhas que contém dados de N dias. Durante o dia, novas linhas são adicionadas com datetimevalores crescentes em uma coluna indexada. Os dados são adicionados ao longo do dia em lotes (geralmente 1K-10K por vez). À meia-noite, o procedimento de manutenção exclui todos os valores anteriores a N dias e reconstrói o índice.

    Além disso, durante o dia, a cada 10 minutos, outro procedimento resume os dados e atualiza o resumo em outra tabela que contém dados menos detalhados, mas que são mantidos por mais tempo.

    Percebi que o desempenho do procedimento de resumo era bom pela manhã, mas piorava no final do dia. Verifiquei os planos de execução e vi que eram diferentes. A mesma consulta que roda de manhã e à tarde tinha planos diferentes (usei OPTION(RECOMPILE)).

    Portanto, adicionei um procedimento para atualizar as estatísticas relevantes ao longo do dia sem depender de limites integrados.

    CREATE PROCEDURE [dbo].[RebuildStatisticsOnMyTable]
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        UPDATE STATISTICS [dbo].[MyTableStats] ([IX_ImportantIndex], [IX_AnotherIndex]);
    END TRY
    BEGIN CATCH
        -- handle errors
        ...
    END CATCH;
END

    Com essas atualizações periódicas de estatísticas ao longo do dia, o desempenho do procedimento de resumo é bom e estável. Tive que experimentar um pouco e encontrei o período adequado para atualizar as estatísticas.

    Isso é no SQL Server 2008 e, até onde eu sei, também se aplica a 2012. 2014 tem um estimador de cardinalidade diferente e aprimorado, que (pelo que entendi) pode efetivamente extrapolar as estatísticas e produzir previsões decentes em casos de linhas adicionadas com registros de data e hora crescentes que vão além do intervalo do histograma de estatísticas. Não me lembro agora onde vi a descrição detalhada disso. Provavelmente foi uma postagem de blog de Paul White ou Aaron Bertrand. Portanto, é provável que, se atualizarmos para 2014, não haja necessidade dessas atualizações forçadas de estatísticas ao longo do dia.

    • 3

relate perguntas