SHRINKDATABASE, SHINRKFILE, AUTO-SHRINK, saiba porque evitá-los!



Olá pessoal, tudo certo? Espero que sim!

 Em minhas passagens pelos fóruns, tenho visto alguns tópicos semelhantes com relação à liberação de espaço não utilizado dos arquivos de dados das bases para o disco através do comando DBCC SHRINKFILE. No post de hoje vou tentar explicar o porquê de sempre se tentar evitar esta prática considerada prejudicial para a performance do SQL Server. 

 Existem muitos links na Internet falando sobre o assunto mas basicamente me apoiei nos links do Paul S. Randal, que pra quem não sabe, é o “pai” dos comandos DBCC: 


http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/  
 
 O objetivo do shrink é trabalhar num arquivo de cada vez, quando executado, ele movimenta as páginas alocadas mais para o final do arquivo para o início, de modo a liberar o máximo do espaço requisitado pelo DBA para o sistema operacional. Como o espaço é alocado em extents, fica a impressão de que extents são movidos, mas na verdade a liberação de espaço é obtida com a movimentação de páginas, nesta operação não existe cuidados com a ordem lógica das páginas e dos índices, o resultado disto? Aumento no consumo das CPU’s, mais I/O, cada página movimentada será registrada no transaction log e o mais grave, muitos índices na base ficarão extremamente fragmentados. Para um melhor entendimento recomendo que você faça o “demo” proposto pelo Paul S. Randal no primeiro link.

 A única situação em que o shrink em um arquivo de dados não causaria problemas, seria utilizando a opção TRUNCATEONLY. Usando esta opção e tendo espaço livre ao final do arquivo, o shrink simplesmente liberaria espaço sem movimentar as páginas. 

 Este modo de operação é o mesmo para os comandos DBCC SHRINKDATABASE, DBCC SHRINKFILE e para a opção das base de dados chamada AUTO-SHRINK. 

 Você pode utilizar a query abaixo para verificar a fragmentação de todos os índices num banco de dados: 

SELECT
     convert(varchar(25),db_name()) as DatabaseName,
convert(varchar(50),OBJECT_NAME (a.object_id)) as ObjectName, 
a.index_id,
convert(varchar(50),b.name) as IndexName, 
avg_fragmentation_in_percent, index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b   
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <> 0
 
E quanto ao shrink nos arquivos de log?

 O SQL Server não pode movimentar os registros no final do arquivo de log assim como faz nos arquivos de dados. Somente é possível reduzir o tamanho do arquivo de log se ele estiver vazio no final. O shrink dos arquivos de log é feito eliminando virtual log files (VLFs). Os VLFs podem ser vistos através do comando DBCC LOGINFO, cada linha retornada pelo comando é um VLF. Na saída do comando, status = 0 indica que o VLF não está em uso e status = 2 indica que ele está em uso. Ou seja, se o status do último VLF do arquivo de log for 2, não será possível realizar o shrink. Nestes casos, para reduzir o tamanho do arquivo de log, você pode tentar rodar um CHECKPOINT ou realizar backups do log para liberar VLFs e viabilizar os shrinks. Ao contrário dos arquivos de dados, o shrink dos arquivos de log não causa problemas de performance simplesmente com a sua execução: o problema com o arquivo de log são auto-grows sucessivos em pequenos incrementos. Neste cenário, a quantidade de VLFs pode aumentar muito, prejudicando funcionalidades como replicação e recovery. Em particular, com relação ao processo de recovery, também é importante contar com as últimas correções do engenho, apenas para evitar surpresas: 

 
 Minha sugestão é que você sempre busque dimensionar da melhor forma possível os discos em seu ambiente para suportar com tranquilidade a expansão das bases de dados. Além disto, procure ter uma rotina periódica que faça a reindexação das tabelas.

 Espero que tenham gostado, até a próxima!

Nenhum comentário:

Postar um comentário