Estou testando a exclusão de dados de um índice columnstore clusterizado.
Percebi que há um grande operador de spool ansioso no plano de execução:
Isso se completa com as seguintes características:
- 60 milhões de linhas excluídas
- 1,9 GiB TempDB usado
- 14 minutos de tempo de execução
- plano de série
- 1 reencadernação no carretel
- Custo estimado para digitalização: 364.821
Se eu induzir o estimador a subestimar, obtenho um plano mais rápido que evita o uso do TempDB:
Custo estimado da varredura: 56.901
(Este é um plano estimado, mas os números nos comentários estão corretos.)
Curiosamente, o spool desaparece novamente se eu liberar os armazenamentos delta executando o seguinte:
ALTER INDEX IX_Clustered ON Fact.RecordedMetricsDetail REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
O spool parece ser introduzido apenas quando há mais do que algum limite de páginas nos armazenamentos delta.
Para verificar o tamanho dos armazenamentos delta, estou executando a seguinte consulta para verificar as páginas in-row da tabela:
SELECT
SUM([in_row_used_page_count]) AS in_row_used_pages,
SUM(in_row_data_page_count) AS in_row_data_pages
FROM sys.[dm_db_partition_stats] as pstats
JOIN sys.partitions AS p
ON pstats.partition_id = p.partition_id
WHERE p.[object_id] = OBJECT_ID('Fact.RecordedMetricsDetail');
Existe algum benefício plausível para o iterador de spool no primeiro plano? Devo presumir que se destina a melhorar o desempenho e não a proteção do Dia das Bruxas, porque sua presença não é consistente.
Estou testando isso no 2016 CTP 3.1, mas vejo o mesmo comportamento no 2014 SP1 CU3.
Publiquei um script que gera esquema e dados e orienta você na demonstração do problema aqui .
A questão é principalmente por curiosidade sobre o comportamento do otimizador neste ponto, pois tenho uma solução alternativa para o problema que gerou a pergunta (um grande spool cheio de TempDB). Agora estou excluindo usando a troca de partição.
Isso depende do que você considera "plausível", mas a resposta de acordo com o modelo de custo é sim. Claro que isso é verdade, porque o otimizador sempre escolhe o plano mais barato que encontra.
A verdadeira questão é por que o modelo de custo considera o plano com o spool muito mais barato do que o plano sem. Considere os planos estimados criados para uma nova tabela (a partir do seu script) antes que quaisquer linhas sejam adicionadas ao armazenamento delta:
O custo estimado para este plano é de 771.734 unidades :
O custo está quase todo associado à exclusão de índice clusterizado, pois espera-se que as exclusões resultem em uma grande quantidade de E/S aleatória. Esta é apenas a lógica genérica que se aplica a todas as modificações de dados. Por exemplo, supõe-se que um conjunto não ordenado de modificações em um índice de árvore b resulte em E/S amplamente aleatória, com um alto custo de E/S associado.
Os planos de alteração de dados podem apresentar uma classificação para apresentar as linhas em uma ordem que promoverá o acesso sequencial, exatamente por esses motivos de custo. O impacto é exacerbado neste caso porque a tabela é particionada. Muito dividido, de fato; seu script cria 15.000 deles. Atualizações aleatórias em uma tabela muito particionada têm um custo especialmente alto, pois o preço para alternar partições (conjuntos de linhas) no meio do fluxo também é alto.
O último fator importante a considerar é que a consulta de atualização simples acima (onde 'atualizar' significa qualquer operação de alteração de dados, incluindo uma exclusão) se qualifica para uma otimização chamada "compartilhamento de conjunto de linhas", em que o mesmo conjunto de linhas interno é usado para varredura e atualizando a tabela. O plano de execução ainda mostra dois operadores separados, mas, mesmo assim, há apenas um conjunto de linhas usado.
Menciono isso porque ser capaz de aplicar essa otimização significa que o otimizador segue um caminho de código que simplesmente não considera os benefícios potenciais da classificação explícita para reduzir o custo de E/S aleatória. Onde a tabela é uma b-tree, isso faz sentido, porque a estrutura é inerentemente ordenada, portanto, compartilhar o conjunto de linhas fornece todos os benefícios potenciais automaticamente.
A consequência importante é que a lógica de custeio para o operador de atualização não considera esse benefício de ordenação (promovendo E/S sequencial ou outras otimizações) onde o objeto subjacente é o armazenamento de colunas. Isso ocorre porque as modificações de armazenamento de coluna não são executadas no local; eles usam uma loja delta. O modelo de custo está, portanto, refletindo uma diferença entre atualizações de conjunto de linhas compartilhadas em b-trees em comparação com columnstore.
No entanto, no caso especial de um columnstore (muito!) particionado, ainda pode haver um benefício para a ordem preservada, pois executar todas as atualizações em uma partição antes de passar para a próxima ainda pode ser vantajoso do ponto de vista de E/S .
A lógica de custo padrão é reutilizada para armazenamentos de coluna aqui, portanto, um plano que preserva a ordem da partição (embora não a ordem dentro de cada partição) tem um custo menor. Podemos ver isso na consulta de teste usando o sinalizador de rastreamento não documentado 2332 para exigir entrada classificada para o operador de atualização. Isso define a
DMLRequestSortpropriedade como true na atualização e força o otimizador a produzir um plano que forneça todas as linhas para uma partição antes de passar para a próxima:O custo estimado para este plano é bem menor, em 52,5174 unidades:
Essa redução no custo se deve ao menor custo de E/S estimado na atualização. O spool introduzido não executa nenhuma função útil, exceto que pode garantir a saída na ordem da partição, conforme exigido pela atualização com
DMLRequestSort = true(a varredura serial de um índice de armazenamento de coluna não pode fornecer essa garantia). O custo do spool em si é considerado relativamente baixo, especialmente em comparação com a redução (provavelmente irrealista) do custo na atualização.A decisão sobre a necessidade de entrada ordenada para o operador de atualização é feita muito cedo na otimização da consulta. A heurística usada nesta decisão nunca foi documentada, mas pode ser determinada por tentativa e erro. Parece que o tamanho de qualquer loja delta é uma entrada para essa decisão. Uma vez feita, a escolha é permanente para a compilação da consulta. Nenhuma
USE PLANdica será bem-sucedida: o destino do plano ordenou entrada para a atualização ou não.Há outra maneira de obter um plano de baixo custo para essa consulta sem limitar artificialmente a estimativa de cardinalidade. Uma estimativa suficientemente baixa para evitar o Spool provavelmente resultará em DMLRequestSort falso, resultando em um custo de plano estimado muito alto devido à E/S aleatória esperada. Uma alternativa é usar o sinalizador de rastreamento 8649 (plano paralelo) em conjunto com 2332 (DMLRequestSort = true):
Isso resulta em um plano que usa varredura paralela em modo de lote por partição e uma troca Gather Streams de preservação de ordem (fusão):
Dependendo da eficácia em tempo de execução da ordenação de partições em seu hardware, isso pode funcionar como o melhor dos três. Dito isso, grandes modificações não são uma boa ideia no armazenamento de colunas, então a ideia de troca de partições é quase certamente melhor. Se você puder lidar com os longos tempos de compilação e escolhas de planos peculiares frequentemente vistas com objetos particionados - especialmente quando o número de partições é grande.
Combinar muitos recursos relativamente novos, especialmente perto de seus limites, é uma ótima maneira de obter planos de execução insatisfatórios. A profundidade do suporte ao otimizador tende a melhorar com o tempo, mas usar 15.000 partições de armazenamento de colunas provavelmente sempre significará que você vive em tempos interessantes.