Temos duas tabelas com colunas e índices idênticos (sem indexação, basicamente). Executamos a mesma consulta, que no caso da tabela original leva 5 segundos para ser executada; no caso da nova tabela, deixamos rodar por 30 minutos e depois matamos a consulta.
Atualizamos as estatísticas, mas isso não teve resultado. Tentamos reconstruir a nova tabela para ver se a desfragmentação ajudaria, mas também não surtiu efeito.
Em um palpite, exportamos as duas tabelas para o mesmo banco de dados, a fim de ver se algo mudaria ou não, mas temos exatamente os mesmos resultados.
Estou meio perplexo como isso pode ser. Para tornar as coisas ainda mais interessantes, a tabela original contém mais dados do que a nova tabela, o que teoricamente significa que a nova tabela deve concluir a consulta mais rapidamente.
Alguém tem uma possível explicação? Eu trabalhei por anos como DBA (embora não por alguns anos agora) e, francamente, estou perplexo sobre o motivo pelo qual isso poderia acontecer.
Em resposta a alguns dos comentários, aqui está a definição da tabela em questão:
CREATE TABLE [dbo].[Fact_SubscriptionDetail_Test](
[CustomerSellTo_Key] [int] NULL,
[CustomerContactSellTo_Key] [int] NULL,
[CustomerRefTo_Key] [int] NULL,
[WebAuthUser_Key] [int] NULL,
[Country_Key] [int] NULL,
[Date_Key] [int] NULL,
[SnapshotDate_Key] [int] NULL,
[Product_Key] [int] NULL,
[License_Key] [int] NULL,
[Subscription_Key] [int] NULL,
[SubscriptionTypeLostSeatsType_Key] [int] NULL,
[M_SubscriptionDetail_NewSeats] [int] NULL,
[M_SubscriptionDetail_WinbackSeats] [int] NULL,
[M_SubscriptionDetail_RenewedFlexSeats] [int] NULL,
[M_SubscriptionDetail_RenewedCommitSeats] [int] NULL,
[M_SubscriptionDetail_RenewedSeats] [int] NULL,
[M_SubscriptionDetail_ActiveSeatsEndMonth] [int] NULL,
[M_SubscriptionDetail_LostNonPaymentSeats] [int] NULL,
[M_SubscriptionDetail_LostGraceInactiveSeats] [int] NULL,
[M_SubscriptionDetail_LostOtherSeats] [int] NULL,
[M_SubscriptionDetail_LostSeats] [int] NULL,
[M_SubscriptionDetail_GrossBookings] [numeric](38, 20) NULL,
[M_SubscriptionDetail_ActiveFlexSeats] [int] NULL,
[M_SubscriptionDetail_ActiveCommitSeats] [int] NULL,
[M_SubscriptionDetail_ActiveSeats] [int] NULL,
[DateCreated] [datetime] NULL
CONSTRAINT [DF__Fact_Subs__DateC__gtfhjCC] DEFAULT (getdate())
) ON [DATA]
Os dados nas duas tabelas não são totalmente idênticos, mas de natureza muito semelhante. A consulta que executamos está vinculada a algumas outras tabelas (principalmente dimensões do data warehouse).
Eu desfragmentei (o que eu concordo, não faz muito sentido para um heap, mas achei que não faria e não faria mal) simplesmente executando
ALTER TABLE Fact_SubscriptionDetail_Test REBUILD.
Quando verificamos inicialmente o plano de consulta, ele sugeriu adicionar alguns índices na tabela Teste (mas não na tabela rápida original). Também tentamos adicionar um índice clusterizado (PK) na tabela Teste, mas nem isso, nem o índice sugerido pelo Plano de Execução teve qualquer efeito.
Seguem os planos de execução:
http://pastebin.com/XagvSxjj (Tabela original; rápido)
http://pastebin.com/LSgCsvUe (Nova tabela; lento)
Há uma diferença nos caminhos de execução, o que me faz pensar que a cardinalidade dos dados na tabela original é um pouco melhor. A tabela original contém cerca de 1,4 milhão de linhas (230 MB), das quais 400 mil são processadas pela consulta. A nova tabela contém 400 mil linhas (52 MB).
Esta é a consulta completa: http://pastebin.com/bYiaGW1d (ligeiramente editada para remover algumas informações confidenciais).
O valor para "limite de custo para paralelismo" no servidor é 5.
Se os planos de execução fossem os mesmos, isso provavelmente seria verdade, mas não é. O número de linhas esperado (e a distribuição dos dados de acordo com as estatísticas) afeta a estratégia escolhida pelo otimizador de consulta.
Pequena mesa
Quando a tabela heap contém 398.399 linhas, o otimizador escolhe um plano serial usando loops aninhados para todas as operações de junção, exceto aquelas que afetam diretamente a tabela heap; essas junções empregam uma junção de hash.
A complexidade da consulta é tal que as estimativas de cardinalidade (contagem de linhas) provavelmente serão imprecisas, de modo que a estratégia de loop aninhado acaba sendo um desastre. O otimizador considerou um plano paralelo, mas o rejeitou por ser mais caro do que a opção de loops aninhados seriais.
Mesa maior
Quando a tabela heap contém 1.750.640 linhas, as alterações nas estimativas de custo significam que o otimizador avalia que um plano paralelo usando junções de hash e filtros de bitmap otimizados extensivamente será uma estratégia melhor.
Essa forma de plano é muito mais resistente a erros de estimativa de cardinalidade. As entradas de construção de hash join podem vazar para o disco, mas o pior caso é muito melhor do que executar uma subárvore inteira um grande número de vezes (usando loops aninhados).
Solução 1
Se você sabe que um plano paralelo com filtros de bitmap é a melhor escolha em geral, pode usar um Guia de Plano para impor isso ou incentivar esse plano com uma
OPTION (HASH JOIN, MERGE JOIN)dica de consulta. Você pode nem sempre obter paralelismo se as tabelas forem pequenas, mas o desempenho ainda deve melhorar (e ser mais previsível em geral).Solução 2
Você também pode explorar a divisão da consulta em seções mais simples, inicialmente ao longo das linhas dos limites da Expressão de Tabela Comum. Materializar resultados intermediários de tamanho razoável em tabelas temporárias significa: