Estou testando alguns índices e quero fazer um pouco de teste AB em quais índices têm melhor desempenho (principalmente a ordem dos campos no índice e campos adicionais especificados).
Tenho um backup do banco de dados no qual estou testando, mas esse backup leva muito tempo para ser restaurado. Eu preferiria se pudesse apenas criar o índice foo, reunir métricas de desempenho e, em seguida, excluir foo para retornar o banco de dados ao estado pré-índice e criar a barra de índice. É assim que a criação de índices funciona ou a criação do índice altera a tabela de alguma forma que a exclusão do índice não pode ser desfeita?
Mais longo
As alterações de índice (criações, reconstruções, eliminações, etc.) em uma tabela invalidam quaisquer planos relevantes no cache do plano e fazem com que um novo plano seja recompilado na próxima vez que a consulta relacionada for executada.
É possível que um plano diferente seja gerado do que estava originalmente lá, em um banco de dados cujos dados foram alterados entre quando o plano original foi armazenado em cache e o novo foi recompilado.
Na prática, já que você está apenas fazendo testes A/B em uma cópia do banco de dados que presumo que não esteja mudando ativamente de outra forma, então sim, Erik está sucintamente no local. É altamente provável que a eliminação do índice resulte no mesmo plano de execução original para ser armazenado em cache para suas consultas em suas execuções subsequentes - o que significa que seu banco de dados retornará efetivamente ao seu estado anterior.
Alternativa
Para esse tipo de teste, gosto de usar instantâneos de banco de dados .
Consulte a documentação vinculada para obter a sintaxe exata e as coisas a serem observadas.
Antes do SQL Server 2016 Service Pack 1, esse recurso exigia a Enterprise Edition. Desde então, está disponível em todas as edições.
curto
A eliminação do índice é suficiente para redefinir a tabela para seu estado anterior. Se você desativar o índice, as estatísticas criadas para ele ainda poderão ser usadas para estimativa de cardinalidade.
Existem algumas pequenas armadilhas que você terá que prestar atenção, porque depende dos índices que você está criando e/ou descartando.
Índices Agrupados Along Come
Existem dicas por aí que sugerem criar índices clusterizados em heaps (dados não classificados) para permitir que sejam classificados (devido à criação do índice clusterizado) e, em seguida, remover o índice clusterizado para ter um heap novamente. Reconstruir a tabela depois de descartar o índice liberará algum espaço não utilizado devido à exclusão de registros nas páginas.
Heaps (tabelas sem índices clusterizados) (Microsoft | Learn).
Há um pequeno aviso no artigo acima informando que criar e descartar um índice clusterizado em um heap onde outros índices estão presentes exigirá uma reconstrução total de todos os índices não clusterizados. CUIDADO!
Você pode ter um grande impacto no desempenho se criar um índice clusterizado em um heap contendo índices não clusterizados e, em seguida, remover o índice clusterizado novamente.
Tabelas clusterizadas do SQL Server versus tabelas heap (MSSQLTips)
A remoção de um índice clusterizado de um heap (tabela) não retornará os dados agora classificados (devido à criação do índice clusterizado) para um estado não classificado.
Respondendo sua pergunta
Não em todos os casos, como dito acima. Adicionar e remover um índice clusterizado de um heap pode ter um grande impacto no desempenho e pode fazer uma grande diferença em como seu banco de dados reage (plano de execução) a essas alterações.
Exemplo usando db<>fiddle
Aqui está um pequeno exemplo que mostra que criar um índice clusterizado em um heap e, em seguida, descartar o índice clusterizado não retornará os dados ao seu estado anterior. Mesmo depois de um
ALTER TABLE ... REBUILD
...db<>exemplo de fiddle
violino
Os dados foram alterados!
Soluções possíveis
...e não crie índices no ambiente de produção para testes rápidos.