Início / dba / Perguntas / 269888
Accepted
J.D.
Asked: 2020-06-26 09:09:10 +0800 CST

A adição de um índice columnstore a uma tabela pode afetar o desempenho de leitura de uma consulta que usa um índice rowstore na mesma tabela?

  • 772

Estou fazendo alguns testes de indexação columnstore em uma única tabela que tem cerca de 500 milhões de linhas. Os ganhos de desempenho em consultas agregadas foram incríveis (uma consulta que antes levava cerca de 2 minutos para ser executada agora é executada em 0 segundos para agregar a tabela inteira).

Mas também notei que outra consulta de teste que aproveita a busca em um índice rowstore existente na mesma tabela agora está sendo 4x mais lenta do que antes antes de criar o índice columnstore. Posso demonstrar repetidamente ao descartar o índice columnstore que a consulta rowstore é executada em 5 segundos e, adicionando novamente no índice columnstore, a consulta rowstore é executada em 20 segundos.

Estou de olho no plano de execução real para a consulta de índice rowstore, e é quase exatamente o mesmo em ambos os casos, independentemente de o índice columnstore existir. (Ele usa o índice rowstore em ambos os casos.)

A consulta de teste do rowstore é:

SELECT *
INTO #TEMP
FROM Table1 WITH (FORCESEEK)
WHERE IntField1 = 571
    AND DateField1 >= '6/01/2020'

O índice rowstore usado nesta consulta é:CREATE NONCLUSTERED INDEX IX_Table1_1 ON Table1 (IntField1, DateField1) INCLUDE (IntField2)

A consulta de teste columnstore é:

SELECT COUNT(DISTINCT IntField2) AS IntField2_UniqueCount, COUNT(1) AS RowCount
FROM Table1
WHERE IntField1 = 571 -- Some other test columnstore queries also don't use any WHERE predicates on this table
    AND DateField1 >= '1/1/2019'

O índice columnstore é:CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Table1_2 ON Table1 (IntField2, IntField1, DateField1)

Aqui está o plano de execução para a consulta de índice rowstore antes de criar o índice columnstore: Plano de Execução - Índice Rowstore - Criação de Índice Pré-Columnstore

Aqui está o plano de execução para a consulta de índice rowstore depois de criar o índice columnstore: Plano de Execução - Índice Rowstore - Criação de Índice Pós-Columnstore

As únicas diferenças que noto entre os dois planos é que o aviso da operação Sort desaparece após a criação do índice columnstore e os operadores Key Lookup e Table Insert (#TEMP) demoram significativamente mais.

Aqui estão as informações da operação Sort com o aviso (antes de criar o índice columnstore): Operação de classificação - Aviso

Aqui estão as informações da operação Sort sem o aviso (depois de criar o índice columnstore): Operação de classificação

Eu teria pensado que uma consulta de leitura que está aproveitando especificamente o mesmo índice de armazenamento de linhas e plano de execução em ambos os casos deveria ter aproximadamente o mesmo desempenho em cada execução, independentemente de quais outros índices existem nessa tabela. O que dá aqui?

Edit: Aqui estão as estatísticas de TIME e IO antes de criar o índice: Estatísticas - Antes da criação do índice Columnstore

Aqui estão as estatísticas depois de criar o índice columnstore: Estatísticas - Após a criação do índice Columnstore

sql-server index

1 respostas

  1. Best Answer

    Adicionar o índice columnstore não clusterizado permite uma classificação no modo de lote no segundo plano de execução. Isso faz com que todo o processamento seja feito em um thread - portanto, mesmo que a consulta tenha um plano paralelo, ela está essencialmente sendo executada em série. Você pode ver isso observando os detalhes dos diferentes operadores.

    Reproduzi seu problema localmente, aqui está o operador de classificação com contagens por thread - como você pode ver, tudo está no thread 1:

    captura de tela do SSMS mostrando a inclinação do encadeamento e o modo de execução para o operador de classificação

    Observe que o "Modo de Execução Real" é "Lote".

    Tudo após a classificação (a junção de loops aninhados, pesquisa de chave etc.) é essencialmente serial, que é o que diminui a velocidade da consulta.

    Consulte este artigo da base de conhecimento para obter detalhes e possíveis soluções:

    Adiciona o sinalizador de rastreamento 9358 para desabilitar as operações de classificação do modo em lote em uma consulta paralela complexa no SQL Server 2016

    As classificações de modo de lote foram introduzidas no SQL Server 2016 no nível de compatibilidade 130. Se um plano de execução de consulta contiver classificações de modo de lote paralelo em conjunto com operadores paralelos diretamente upstream, você poderá encontrar desempenho degradado em comparação com os equivalentes do plano de classificação de modo de linha.

    Isso ocorre devido a uma classificação em lote paralela que gera dados totalmente classificados por meio de um único thread para o operador paralelo upstream (por exemplo, um operador de junção de mesclagem paralela). A degradação do desempenho ocorre quando o operador paralelo upstream usa processamento de thread único devido ao operador de classificação do modo de lote de thread único de entrada.

    Para completar, as opções descritas são:

    • habilitar TF 9358
    • habilitar hotfixes do otimizador de consulta (através do TF 4199, a QUERY_OPTIMIZER_HOTFIXESopção de banco de dados ou a ENABLE_QUERY_OPTIMIZER_HOTFIXESdica de consulta)

    Livrar-se do tipo é outra solução para esse problema. A classificação está presente apenas para tentar evitar muita E/S aleatória da junção de loops aninhados, que está usando pré-busca não ordenada, conforme mencionado neste artigo por Craig Freedman:

    Otimizando o desempenho de E/S classificando - Parte 1

    O plano usa o índice não clusterizado para evitar tocar desnecessariamente em muitas linhas. No entanto, executar 64.000 I/Os aleatórios ainda é bastante caro , então o SQL Server adiciona uma classificação. Ao classificar as linhas na chave de índice clusterizado, o SQL Server transforma as E/Ss aleatórias em E/Ss sequenciais.

    Você pode se livrar do tipo por:

    • eliminando a necessidade de pesquisa de chave (selecionando menos colunas ou criando um índice não clusterizado de cobertura)
    • desabilitando a pré-busca de loops aninhados adicionando (sinalizador de rastreamento não documentado e não suportado) OPTION (QUERYTRACEON 9115)à consulta
    • 6

relate perguntas