Recentemente, executamos um script em nosso banco de dados de produção que eliminaria e recriaria dinamicamente centenas de índices como índices filtrados. Embora este script tenha sido executado perfeitamente em todos os outros testes anteriores, agora, depois deste, o cache do plano de consulta do SQL Server está se comportando de maneira estranha.
O planejador de execução revela que estão sendo usados índices errados que retornam milhões de linhas, quando apenas algumas deveriam corresponder. Quando os índices estão corretos, o plano de execução mostra que o SQL Server resolve usar o índice Scan em oposição ao índice Seek, fornecendo resultados muito inferiores aos ideais. Com dicas de tabela como WITH(INDEX(indexname)) ou WITH(FORCESEEK) nos locais apropriados, isso pode ser corrigido. INNER LOOP JOIN também corrige alguns deles.
No entanto, o problema é que, mesmo quando esses novos índices filtrados são descartados e recriados como eram antes, o plano de consulta permanece o mesmo. O cache do plano de consulta foi limpo, o banco de dados foi restaurado para um ambiente diferente, as estatísticas foram atualizadas e, obviamente, os índices foram reconstruídos para que não sejam fragmentados.
Atualmente, esse é um problema crítico que ninguém tem ideia de como consertar. Embora possamos forçar o SQL Server a usar os planos corretos, simplesmente não é uma solução para a infinidade de softwares que teriam que ser atualizados com ele e, obviamente, um banco de dados onde você precisa apontar manualmente como lidar com consultas não é uma opção .
Então, qualquer ajuda seria bem-vinda.
Editar: Conseguimos corrigir uma consulta descartando os índices, recriando-os novamente como filtrados e, em seguida, executando um UPDATE STATISTICS tablename WITH FULLSCAN. Isso corrigiu parte do problema e duas junções estavam funcionando corretamente. Depois disso, tivemos que fazer uma alteração separada em um índice de várias colunas que não estava envolvido no script de índice original, para incluir uma das colunas usadas na junção. Essas duas alterações juntas permitiram que o planejador de consulta resolvesse usar os índices corretos com Seek em vez de Scan.
A teoria agora é que, devido a uma falha anterior, o banco de dados aparentemente foi restaurado excluindo o banco de dados subjacente e criando um novo a partir do backup, em vez de apenas usar REPLACE como antes. Isso teria de alguma forma desconectado os metadados do masterdb, como planos de execução, todo o cache e outros enfeites do banco de dados, resultando em um novo banco de dados massivo sem planos existentes para lidar com consultas. Isso, agrupado com uma atualização de estatísticas aparentemente falha nos índices recém-criados, teria produzido um cenário em que o SQL Server não tinha ideia de como resolver as várias consultas com as quais estava sendo bombardeado.
No entanto, não estou convencido de que isso ainda seja suficiente, já que alguns dos comportamentos, como alterar o índice de várias colunas e o fato de que nenhuma atualização de estatísticas foi necessária em nenhum dos ambientes de teste anteriores nos últimos 2 meses de testes, parece deixar transparecer que algo mais deu errado.
Bem, agora o problema está resolvido:
Embora pareça lógico usar índices filtrados (NOT NULL), para reduzir o tamanho do banco de dados e, como dizem muitas fontes na web, aumentar o desempenho, a realidade parece ser algo totalmente diferente.
Em termos leigos, o planejador de consultas do SQL Server resolve até mesmo suas junções internas básicas sem fazer suposições quanto ao conteúdo das colunas. Mesmo que os valores NULL não formem uma junção, eles devem ser incluídos no índice da coluna para que o planejador de consulta o use, a menos que especificado de outra forma com predicados como WHERE joinCol_ID IS NOT NULL. Basicamente, o SQL Server não usa índices filtrados para junções, a menos que as próprias consultas sejam modificadas para contabilizar o valor do filtro. Em vez disso, ele criará novas estatísticas nessas colunas e/ou usará uma varredura de índice clusterizado ou outros índices incluindo a coluna, o que considerar mais eficaz. Usar índices filtrados em chaves estrangeiras é, portanto, uma ideia absolutamente horrível.
Ainda não temos ideia de como meses de teste em vários outros ambientes nunca produziram os mesmos resultados fora deste único banco de dados, mas é assim que deve funcionar. Aparentemente, algo que até onde sabemos não está relacionado a cache, estatísticas ou configurações, fez com que o banco de dados de produção se comportasse de maneira diferente e detectasse e usasse corretamente os índices filtrados, enquanto todos os ambientes de teste simplesmente usavam os índices antigos (visto que os índices foram descartados e recriados com o mesmo nome, isso parece uma teoria válida mesmo que não haja nenhuma prova real).
Portanto, a lição da história: a Web está repleta de exemplos de como os índices filtrados são subutilizados e de como eles podem ser incríveis. Mas essa séria desvantagem nunca apareceu, exceto como um pensamento incômodo no fundo da minha cabeça dizendo "se eles são tão bons, então por que os valores NULL não são filtrados dos índices por padrão, já que eles apenas ocupam espaço e servem apenas a um propósito em circunstâncias especiais"? Bem, agora eu sei por quê. :)