Eu tenho um aplicativo instalado em todo o mundo que usa versões do SQL do SQL Server 2008 Express para o SQL Server 2017 Enterprise Edition.
Eu tentei várias maneiras de criar um índice (3 colunas com uma inclusão de 3 colunas) em uma grande tabela onde o banco de dados geralmente está em modelo de recuperação simples, mas pode ser como modelo de recuperação completa... Hardware pode variar drasticamente de um cliente para cima outro:
- Alterar tabela criar índice...
- Crie uma tabela tmp com o novo índice e copie os dados com um "inserir em" seguido de uma tabela de descarte da tabela original e uma renomeação da tabela tmp para substituir a original.
- Crie uma tabela tmp com o novo índice e use um script do PowerShell para copiar os dados seguido por uma tabela de descarte da tabela original e uma renomeação da tabela tmp para substituir a original.
A mais rápida das 3 foi a 1ª opção (a forma tradicional de criar um índice não clusterizado) que levou 1h15m para um pouco mais de 106 milhões de registros (a tabela tem 200 colunas... não é o ideal mas é com o que estou trabalhando ) as outras opções levaram o triplo do tempo e mais)
Infelizmente, não posso usar o recurso online, pois há uma variedade de versões do SQL Server e o online só é bom para a edição corporativa.
Uma hora e 15 minutos no meu laboratório é muito tempo e eu gostaria de tentar encontrar outra maneira de diminuir isso, especialmente que em uma de nossas próximas atualizações de aplicativos, precisaremos criar 23 índices ... e eu ainda não vi uma atualização que leva de 5 a 6 horas e os clientes não vão querer esperar de 5 a 6 horas por uma atualização de software.
Além disso, a atualização não exigirá nenhuma atividade no banco de dados durante a atualização.
Tudo o que estou procurando são ideias que eu possa tentar criar meus índices em um período de tempo razoável. Nenhum código! só precisa de ideias teóricas.
Qualquer ideia seria muito apreciada.
Deixo isso como resposta, embora seja principalmente um Extended Comment®.
Você não menciona as definições de índice ou os tipos de colunas que está tentando indexar. Você diz que a tabela tem 200 colunas, mas, a menos que esteja criando um índice clusterizado, isso não importa. A menos que você esteja tentando criar um índice não clusterizado em 200 colunas. Se sim, por favor, reconsidere.
Dado que você está fazendo isso em muitas versões diferentes, é provável que elas também estejam em hardware diferente. No caso da criação de índices, o hardware e os índices existentes terão um impacto dramático no tempo de conclusão. Não é como se você pudesse ajustar a consulta por trás da criação do índice. Da mesma forma, a capacidade do Enterprise Edition de paralelizar a criação de índice pode ser um fator importante.
Além do hardware, a simultaneidade do sistema também pode desempenhar um papel, seja por meio de bloqueio ou uso geral de recursos. Você não menciona se esses são problemas, então, novamente, é um ponto cego para quem tenta responder.
Em versões mais recentes do SQL Server, você pode optar por classificar a criação de índice em tempdb . Isso pode ajudar, desde que tempdb não seja Full Metal Potato© em seus diferentes ambientes.
Finalmente, você não menciona o modelo de recuperação de seus bancos de dados. Como
CREATE INDEXpode ser minimamente registrado , pode valer a pena alternar paraSIMPLEoBULK LOGGEDmodelo de recuperação, se possível. Isso dependerá de seus objetivos de RPO e RTO e se eles podem ser dispensados para uma janela para criar os índices.Um comentário rápido sobre a etapa 2 que se relaciona um pouco com o comentário acima: Se você criar uma tabela sem índices, carregar seus dados e criar índices, provavelmente será mais rápido. Especialmente em
SIMPLEouBULK LOGGED, você pode usar aTABLOCKdica para obter o log mínimo na inserção, bem como na criação do índice.Sua pergunta provavelmente será fechada como muito ampla, mas espero que você ache este Comentário Estendido® útil.