Eu herdei um aplicativo (e banco de dados MS SQL associado) que foi instalado por um fornecedor de software há muitos anos. Não temos nenhum tipo de suporte de fornecedor neste momento. Além de usar o aplicativo, também acessamos o banco de dados independentemente do aplicativo, muitas vezes fazendo atualizações no banco de dados diretamente e consultando dados para geração de relatórios.
Em um esforço para melhorar o desempenho, excluí alguns índices desnecessários e criei outros. Isso causava bugs no aplicativo ao executar determinadas tarefas. O aplicativo não exibe nenhuma mensagem de erro de qualquer tipo e parece funcionar normalmente, mas o banco de dados não é atualizado pelo aplicativo conforme o esperado. Não tenho acesso ao código fonte.
Minha teoria é que o aplicativo especifica explicitamente um índice excluído em uma instrução with(index) em uma de suas consultas. Isso faria com que o servidor retornasse um erro em vez de concluir a consulta e, se o aplicativo suprimisse o erro, o usuário não saberia que algumas tabelas foram atualizadas e outras não. Que outras ideias poderiam causar esse comportamento?
Supondo que minha teoria esteja correta, ainda acredito que o índice seja realmente desnecessário e o aplicativo nunca deveria ter especificado o índice. Talvez uma solução seja criar um novo índice com o mesmo nome, mas com a menor quantidade de colunas possível. Existe alguma maneira de criar um índice sem colunas? Criar um índice não clusterizado com as mesmas colunas do índice clusterizado seria o mais eficiente? O que o otimizador faz neste caso - ele ainda planeja incluir esses índices "ruins" que apenas fazem referência ao índice clusterizado ou saberá ignorar a solicitação with(index)?
Eu executei um rastreamento para ver quais consultas o aplicativo estava executando. Recebi várias chamadas RPC como "exec sp_cursorfetch" e "exec sp_cursorexecute" (que não entendo) em vez do SQL real. Talvez eu pergunte sobre isso em uma pergunta separada, mas se você souber como posso interpretar ou decodificar essas instruções em SQL regular, isso me permitiria pelo menos confirmar minha teoria.
Para verificar sua teoria sobre os erros de ocultação do código do aplicativo, execute um rastreamento XE para capturar
error_reportedeventos.As
sp_cursor*chamadas RPC são procs de cursor do sistema , geralmente chamadas pelo driver cliente para cursores do lado do servidor. Deve haver umasp_preparechamada RPC precedente ou semelhante na mesma conexão que transmite a instrução SQL e retorna o identificador para uso subsequente.As alterações de índice não alteram o comportamento de aplicativos bem escritos, mas pode haver efeitos colaterais inesperados para aqueles que fazem suposições ruins. Por exemplo, considere que a falta
ORDER BYna consulta abaixo pode retornar qualquer linha arbitrária. A linha retornada varia de acordo com os índices usados no plano de consulta.Sugiro que você faça o seguinte, em um ambiente de teste, se possível:
Além do que todos disseram, você pode achar a ferramenta SQL Search do RedGate útil para encontrar referências
WITH(ouWITH(DroppedIndexName)no código do banco de dados para o aplicativo.Se sua teoria estiver correta, isso provavelmente faria parte de alguma pilha de chamadas não transacionais, onde ocorre um erro, não é totalmente/adequadamente revertido e suprimido no lado do aplicativo.
Para imitar adequadamente o índice clusterizado dessa maneira, você precisaria de
INCLUDEtodas as colunas não indexadas, além de cobrir as mesmas. Isso será ineficiente em termos de espaço, pois você está adicionando uma cópia extra completa de todos os dados da tabela nesse índice e afetará o desempenho de inserção/atualização (embora para muitos casos de uso, que são pesados para leitura, isso não é tão significativo quanto você pode pensar).Se você não fizer
INCLUDEtudo com seu índice clusterizado falso, provavelmente prejudicará o desempenho de todas as consultas que o referenciam especificamente, porque agora você tem pesquisas de volta ao índice clusterizado para colunas não cobertas pelo novo.Presumo que ele fará o que é dito e usará o índice especificado, referenciando apenas o CI se você não fizer
INCLUDEtudo o que for necessário. Deve ser fácil configurar um ambiente de teste de amostra para verificar.A remoção de índices desnecessários geralmente ajuda com problemas de desempenho inerte/atualizado (e reduz a carga de manutenção ao não ocupar espaço em backups ou ser reconstruído/reorganizado se você fizer isso regularmente com todos os índices), portanto, se o desempenho de leitura for sua preocupação, então apenas coloque-os de volta como estavam.
Se não houver outros fatores complicadores, colocar os índices originais de volta como estavam seria sua solução IMO mais eficiente, deixando ou não os novos no lugar (pois eles podem ajudar outros acessos).
Um plano forçado que faz referência a um índice descartado ainda seria usado e, portanto, causaria erros quando o plano fosse executado? Este é um recurso que eu não usei, então apenas joguei-o como uma possível causa para pesquisar.
Um caso que vi em que um índice aparentemente desnecessário é explicitamente referenciado é onde uma varredura vai acontecer de qualquer maneira, então um índice contendo apenas as colunas necessárias é criado para que algo menor do que a tabela completa seja varrido, mas o planejador de consulta tenta outra coisa de qualquer maneira (atingir outras tabelas primeiro e substituir a varredura por muitas buscas nesta tabela) acaba consumindo mais tempo. Isso geralmente é um sinal de design de consulta ruim (algo não sargável que pode ser sargável) ou apenas uma correspondência incorreta da estrutura de dados e quais relatórios são desejados, embora possa indicar algo que faz com que as estatísticas de índice ruins sejam prováveis , mas sem referência ao código, você não pode verificar isso (e provavelmente não poderia fazer nada com segurança a respeito). Também pode ser algo que não é mais necessário,
Além disso, como apontado na resposta de Dan, alguém pode estar usando o terrível antipadrão de tentar controlar a ordenação por escolha de índice em vez de um explícito,
ORDER BYou inconscientemente confiou na ordem produzida pelo uso de índice específico (sem uma dica explícita) onde um explícitoORDER BYdeve realmente ser dado, mas nunca foi necessário antes (neste último caso, as coisas podem quebrar de forma semelhante no futuro, mesmo se você colocar os índices originais de volta, agora pode ser a hora de considerar o pôr do sol e substituir esse pedaço de software!) .