ALTERNATIVA PARA O SHRINK NO SQL SERVER

Olá Pessoal!

Faz um tempo que não público nada e isso se deve as recentes mudanças profissionais que consumiram meu tempo por completo. Para poder retomar o ritmo, vou compartilhar com vocês uma estratégia que utilizei recentemente para reduzir o tamanho do Data File de uma determinada base através do Shrink, mas contornando alguns impactos negativos que essa ação normalmente causa na fragmentação dos índices.

A operação de Shrink no SQL Server reduz o tamanho de um arquivo de dados ou log, podendo retornar esse espaço ao File System.

Veja mais detalhes na documentação oficial da Microsoft:

https://docs.microsoft.com/pt-br/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

O motivo que fez necessário a execução do Shrink no Data File, foi devido a uma tabela excluída recentemente que gerou uma grande quantidade de espaço sem uso no Data File e necessitávamos retornar ao File System esse espaço, além de reduzir a base para um tamanho realista.

Como sabemos, o operação do Shrink contribui para as fragmentações externas dos índices, ou seja, quando a ordem lógica das páginas de dados não batem com sua ordem física. A operação do Shrink localiza a maior página de dados alocada no arquivo baseado no GAM (Global Allocation Map) e a move para o mais na frente possível sem considerar a qual objeto aquela página de dados pertence. É recomendado evitar a operação de Shrink a menos que seja absolutamente necessário.

Além disso, é melhor aplicar a operação de Reorganize nos índices ao invés do Rebuild após a operação do Shrink. O Rebuild de um índice cria uma outra cópia do índice, o que acaba aumentando o tamanho do Data File e derruba a proposta do Shrink.

Antes de executar a operação de Shrink no Data File, lembrei de um procedimento que vi há muito tempo atrás recomendando a utilização de novos Filegroups para reduzir o tamanho da base de forma similar a operação de Shrink, mas sem causar fragmentação após sua execução. Vamos acompanhar a demonstração abaixo para entender melhor o conceito.

Para demonstração foi utilizado o SQL Server 2017 (RTM-CU8) com a base AdventureWorks2017, além de ter sido aplicado um script para aumentar o tamanho de suas tabelas, os links para download estão a seguir:

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

Em todos os passos da demostração foi utilizado apenas a tabela “Sales.SalesOrderDetailEnlarged”, veja a estrutura.

A DMV “sys.dm_db_index_physical_stats” com a opção “DETAILED” aplicada para tabela permite que todo índice seja verificado, trazendo resultados mais precisos relacionados a analise de fragmentação. Veja na imagem que a coluna “avg_fragmentation_in_percent” está com valores baixos.

A tabela tem mais de 4 milhões de registros.

Através da consulta na tabela de sistema “sys.database_files”, é possível ver em nosso exemplo que existe cerca de 1 Gb no Data File livre e será esse espaço disponível que reduziremos.

O comando “DBCC SHRINKFILE” está recebendo o nome da base e o tamanho alvo da operação, que está definido para esse exemplo como 5 Mb.

Veja que após a execução do Shrink no Data File a fragmentação dos índices aumentou consideravelmente.




Agora vou demonstrar a outra estratégia com o uso de um novo Filegroup no processo. Para esse exemplo a base foi restaurada ao ponto inicial, não sendo afetada pelo Shrink executado anteriormente.

O script apresentado na imagem abaixo cria um novo Filegroup com o nome SECONDARY e o associa a um novo Database File “AdventureWorks2017_data02.ndf”.

O próximo passo é mover os índices para o novo Filegroup “SECONDARY”, sendo necessário realizar a sua recriação.

Ao verificar novamente as informações de espaço disponível nos arquivos, veja como a estrutura está distribuída.

Na imagem abaixo é mostrado o resultado da execução da operação de Shrink e apenas o Data File afetado com tamanho reduzido, conforme o esperado.

Na DMV “sys.dm_db_index_physical_stats” a fragmentação dos índices está com valores bem baixos, não tendo sido afetada pelo procedimento de Shrink anterior.

Por último, podemos eliminar o antigo arquivo do Filegroup “PRIMARY” que na teoria não tem nenhuma página de dados alocada nele, entretanto, o arquivo de dados principal não pode ser nunca excluído, isso é devido a estruturas especiais que existem nele e não podem ser realocadas, sendo assim, essa operação de exclusão de arquivos só pode ser feito nos demais casos, com novos arquivos e Filegroups.

Como pode ser visto, o procedimento alternativo a operação de Shrink envolve mais passos e com certeza mais esforço considerando um ambiente real com dezenas de objetos, entretanto, em um cenário controlado a necessidade de um procedimento de Shrink no Data File é muito baixa, sendo aplicado em situações bem específicas, então nesse caso vale a pena o empenho de recriar índice por índice, evitando a fragmentação forçada.

Até a próxima!

Anúncios

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão /  Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão /  Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão /  Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão /  Alterar )

Connecting to %s