Acabei de atualizar nosso Data warehouse para o SQL 2016. Estou vendo alguns gráficos realmente interessantes no Query Store (adoro esse recurso!). Abaixo está o exemplo mais estranho que já vi. 22 planos para a mesma consulta.
Isso está me fazendo considerar o ajuste de desempenho do meu processo ETL e os prós e contras das tabelas temporárias e como você pode influenciar o comportamento do plano de execução.
Meu processo ETL usa vários procedimentos armazenados que usam uma mistura de #tabelas padrão e temporárias como tabelas de preparação. As #tabelas são normalmente usadas uma vez e depois descartadas. Alguns são apenas alguns milhares de linhas. Alguns são milhões. O SSMS informa que há índices ausentes, mas em tabelas menores eles fariam diferença suficiente para valer o esforço de adicioná-los? Estatísticas melhores são suficientes?
Acabei de ler esta postagem no blog de Brent Ozar sobre estatísticas em tabelas temporárias e o artigo de Paul White sobre tabelas temporárias em procedimentos armazenados
Ele diz que as estatísticas são criadas automaticamente, quando a #table é consultada e, presumivelmente, usada pelo otimizador.
Minhas perguntas são: há muito sentido ou benefício em criar um índice em uma #table. E/ou: vale a pena atualizar explicitamente as estatísticas como uma etapa no procedimento armazenado antes de usá-lo em consultas, uma vez que são usadas apenas uma vez.
As etapas adicionais e despesas gerais valem a pena? Isso resultaria em planos de execução significativamente melhores ou diferentes?
Pode haver benefícios na criação de índices em tabelas temporárias, mas talvez não para uma tabela intermediária. É uma resposta "depende", infelizmente. Você vai precisar testar. Se você postou o código de como está interagindo com a tabela de preparação, podemos ajudar a determinar se algum índice ajudaria. Um exemplo de onde um índice pode ajudar é se você estiver unindo a tabela temporária a outra tabela. Se você indexasse a coluna unida, poderia haver ganhos de desempenho, especialmente se houver muitas linhas na tabela temporária.
Você provavelmente não precisa atualizar as estatísticas nas tabelas temporárias. Também é uma resposta "depende", embora eu nunca tenha visto uma atualização de estatísticas em tabelas temporárias em nenhum dos milhares e milhares de procedimentos armazenados que examinei, nem precisei adicioná-la para resolver um problema de desempenho.
As estatísticas por si só não são suficientes. O mecanismo de armazenamento precisa ter alguma maneira de obter as linhas que correspondem ao predicado da consulta. Não há valor em saber que, digamos, três linhas correspondem à condição de um milhão na tabela se não puder determinar quais são elas. Sem um índice, a única estratégia é uma varredura de tabela. Um milhão de linhas serão lidas. 99,9997% serão descartados. Com um índice correspondente, os ponteiros podem ser seguidos para selecionar apenas as três linhas necessárias.
Com tabelas pequenas que precisam de apenas algumas páginas, deve-se levar em consideração o esforço para ler as páginas do índice. Digamos que um índice não clusterizado que corresponda exatamente à consulta precise apenas de dois níveis. São duas leituras de página para seguir as chaves. Em seguida, o índice clusterizado é seguido. Isso poderia muito bem ser mais duas leituras de página. Portanto, se a tabela inteira tiver menos de 4 páginas, é improvável que o índice não clusterizado seja usado.