Nosso projeto executa um banco de dados muito grande e muito complicado. Então, cerca de um mês atrás, notamos que o espaço usado por colunas indexadas contendo valores nulos estava ficando muito grande. Como resposta a isso, escrevi um script que pesquisaria dinamicamente todos os índices de coluna única contendo mais de 1% de valores nulos e, em seguida, eliminaria e recriaria esses índices como índices filtrados com a condição de que o valor NÃO fosse NULO. Isso eliminaria e recriaria centenas de índices em todo o banco de dados e normalmente liberaria quase 15% do espaço usado por todo o banco de dados.
Agora tenho duas perguntas sobre isso:
A) Quais são as desvantagens de usar índices filtrados dessa maneira? Eu diria que isso apenas melhoraria o desempenho, mas há algum risco de desempenho envolvido?
B) Recebemos erros ( 'não é possível descartar o índice XYZ porque ele não existe ou você não tem permissão' ) ao descartar e recriar os índices, mesmo quando verificados posteriormente, tudo ocorreu exatamente como esperado. Como isso pode acontecer?
Obrigado por qualquer ajuda!
Editar: Em resposta a @Thomas Kejser
Oi e obrigado, mas acontece que isso foi um desastre. Na época não entendíamos várias coisas como:
- Durante uma consulta, o SQLOS faz planos de índice antes de determinar que não pode usar valores NULL para unir as colunas da tabela. Ou seja, você realmente precisa ter um filtro de cláusula WHERE ajustando o índice para cada índice filtrado usado na consulta, ou o índice não será usado.
- Eliminar e criar índices e atualizar redundantemente suas estatísticas mais uma vez ainda pode não ser suficiente para produzir os planos atualizados, o que presumimos que aconteceria. Parece que, em alguns casos, apenas uma carga de trabalho alta o suficiente forçará o SQL Server a reavaliar os planos.
- Existem alguns aspectos exóticos na funcionalidade do planejador de execução que são difíceis de determinar apenas pelo bom senso e pela lógica. Mesmo com milhares de variações geradas por code-behind de diferentes consultas, índices aparentemente inúteis podem ajudar em algumas estatísticas e planos de consulta que acabam sendo usados em consultas críticas.
No final, essas mudanças foram revertidas. Portanto, os índices filtrados são uma ferramenta poderosa, mas você precisa realmente entender exatamente quais dados estão sendo buscados nessas colunas. Onde os índices normais além dos problemas de espaço são bastante fáceis de aplicar, os índices filtrados representam soluções muito personalizadas. Eles certamente não são um substituto para um índice regular, mas sim uma extensão deles nas circunstâncias especiais em que são necessários.
Abordagem muito interessante. Meu upvote para a criatividade.
Como você recuperou o espaço, presumo que os índices originais não estejam mais no lugar. As desvantagens dos índices filtrados são:
Em termos práticos, isso significa que você deve ter muito cuidado com os índices filtrados, pois eles geralmente resultam em planos de consulta horríveis. Eu não iria tão longe a ponto de chamá-los de inúteis, mas os vejo como uma adição aos índices tradicionais, não como uma substituição (como você está tentando fazer).
Thomas Kejser respondeu a este tópico bem acima.
Só pensei em adicionar 2 centavos.
Eu vi alguns índices filtrados sendo usados apenas (mostrado no plano de execução) quando você corresponde exatamente à cláusula where em sua consulta como where no índice filtrado.
você já tentou usar exibições indexadas ? colunas esparsas ?
Acredito que, desde que você tenha apenas juntas internas, você pode criar uma exibição indexada contendo a (s) cláusula (s) where de seus índices filtrados e, em seguida, poderá usar a exibição.
Pode haver mais de uma visualização. Mas, da mesma forma que com os índices não clusterizados, muitos irão retardar sua escrita.
Na minha experiência, você teria bons ganhos na leitura, mas teria que monitorar as gravações (inserções e atualizações), especialmente se as tabelas estivessem envolvidas na replicação.
No entanto, como entendo sua principal preocupação
the null values, sugiro colunas SPARSE em seus índices .Colunas esparsas são especialmente apropriadas para índices filtrados
Como anunciei colunas esparsas, não me sentiria bem se também não falasse sobre suas limitações:
Como resultado disso
Considere o exemplo > de uma tabela que possui 600 colunas esparsas do tipo bigint.
mais detalhes no link acima, porém prefiro postar aqui também este aviso:
O SQL Server Database Engine usa o seguinte procedimento para realizar essa alteração:
1 - Adiciona uma nova coluna à tabela no novo tamanho e formato de armazenamento.
2 - Para cada linha da tabela, atualiza e copia o valor armazenado na coluna antiga para a nova coluna.
3 - Remove a coluna antiga do esquema da tabela.
4 - Reconstrói a tabela (se não houver índice clusterizado) ou reconstrói o índice clusterizado para recuperar o espaço usado pela coluna antiga.