Convertendo as chaves primárias non-clustered em clustered no SQL Server

Em alguns dias atrás, tive uma situação em alguns bancos de dados para tratar, nas quais as chaves primárias das tabelas não eram clustered. Como já sabemos (ou não), índices clustered nas tabelas dos bancos de dados são fundamentais em 99,99% dos casos, principalmente para a performance dos nossos bancos de dados, bem como para possibilidade de trabalharmos com a compressão de dados de forma mais eficiente.

Sobre a performance, ele é fundamental para que não apareça o “temível” operador Table Scan em nossos planos de execução. Como o nome já diz, Table Scan nada mais é que uma pesquisa em nível de tabela inteira (registro a registro), o que pode nos custar um I/O, tempo de execução e consumo de CPU consideráveis com diferentes magnitudes dependendo do quão grande é a tabela, o que está escrito na query e o hardware disponibilizado para executar a requisição. Também vale citar, casos nos quais não haja um índice adequado para a query, o SQL Server utiliza o índice clustered para segurar o “tranco” usando o operador de Lookup.
Mas como, por quê?
O índice clustered quando é criado, em termos de plano de execução, se torna a tabela por si só. Inclusive, ele é único por tabela.
Voltando… Quando falamos de Lookup, nada mais é de quando não temos determinada(s) coluna(s) em um índice (índices non-clustered), o SQL Server utilizará do índice cluster ou da tabela para encontrar as que estão faltando. Resumindo: quando uma tabela não possuir o índice clustered, se chama Heap e caso houver um Lookup, o operador que aparecerá no plano é um RID Lookup e quando uma tabela possui o índice clustered, a tabela se chama Clustered e quando houver um Lookup em sue plano de execução, o operador que aparecerá é o Key Lookup, pesquisando no índice clustered.

Em relação a compressão de dados, uma curiosidade: Para quem acha que as tabelas heaps, uma vez que elas estão comprimidas, estarão sempre comprimidas… Veja lá, não é bem assim que acontece. Novos dados, para que sejam comprimidos, somente com rebuild e dependendo do ambiente que se tenha, é um grande problema por diversos fatores. A ótima notícia é que para o índice Clustered uma vez comprimido, sempre comprimido! Ou seja, seu ambiente agradecerá, a memória da sua instância ficará mais contente, seu usuário mais feliz e seus discos, além de serem menos lidos, mais economia $$ de disco. Sobre este tema, fica a recomendação do vídeo do Fabrício Lima, na qual aprendi esta dica fantástica, obrigado Fabrício!

Após toda essa abordagem explicada bem rapidamente (ou não), vamos ao que de fato interessa…

Na situação citada desta postagem, para iniciar o processo de recriação das chaves primárias (Primary Keys) como Clustered, é necessário que sejam eliminadas as chaves estrangeiras (Foreign Keys) por conta das dependências entre tabelas, eliminar as chaves primárias atuais, recriar as chaves primárias como Clustered e após este processo que pode ser demorado dependendo do seu banco de dados, bem como a recriação das foreign keys. O script estará publicado em meu GitHub, ok?
A execução deste processo é altamente recomendado (e por sua conta em risco), efetuar as simulações em ambiente não produtivo para que você veja o que está sendo executado e tempo para a execução do processo todo. Até porque, uma passagem só de ida ao RH numa situação destas, não é legal né? Para a execução e testes do script, utilizei do recurso do DBCC CLONEDATABASE e posteriormente, executei em cima de um banco de dados similar ao do ambiente produtivo para evitar vários restores, o que pode ser bem demorado e tedioso dependendo do seu banco de dados.
Como executar o script?

Basicamente utilize de duas sessões, uma para gerar o output do script e outra para a execução dos outputs. Após executar a geração dos outputs, copie o resultado da coluna DroparFK e execute na outra sessão. Depois a DropConstraint, copie e execute na outra sessão. Com tudo removido, então vamos recriar tudo novamente, iniciando com a coluna CreateConstraint na nova sessão e finalizando com a CriarFK. O processo de criação é onde pode demorar horas. Como tudo criado, os testes em seu banco de dados é fundamental. O script foi executado no SQL Server 2016 SP1+. Logo pode ser em que versões e atualizações da mesma ou até edições, a compressão não esteja disponível. Portanto, pode-se apenas remover a parte textual do script “WITH (DATA_COMRPESSION = PAGE)”.

Espero que se você chegou até aqui, tenha gostado do post.
Dicas e sugestões, sempre serão bem vindas.

Um abraço!