SQL Server: Shrink vs. fragmentação
Galera, hoje resolvi escrever um artigo sobre um tema que somente este ano já fui perguntado pelo menos umas 10 vezes e que diversos profissionais muitas vezes não conhece e digo profissionais com anos de experiência com SQL Server. Hoje pretendo falar sobre SHRINK e porque é fácil encontrar diversos artigos de diversos autores falando para não utilizá-lo, ou seja, vou tentar esclarecer um pouco sobre este mito e ajudar muitos profissionais a entenderem o que ele faz.
Introdução ao SHRINK
Antes de iniciarmos as criticas vamos conhecer o que o SHRINK é capaz, alertando que não posso explicar porque o SQL Server ainda mantém o SHRINK. O SHRINK é capaz de diminuir os arquivos de dados e de log eliminando espaços "não alocados".
Quando falamos em SHRINK temos duas opções, uma delas recomendada para o banco de dados e outra para os arquivos de dados e de log separadamente, segue abaixo alguns detalhes sobre o SHRINKDATABASE e SHRINKFILE.
SHRINKDATABASE - Reduz os arquivos de data e log, aplica-se para todos os arquivos do banco de dados (mdf, ndf ou ldf) em um só momento.
SHRINKFILE - Reduz os arquivos de banco de dados tendo a necessidade de especificar a qual arquivo se destina seja ele mdf, ndf ou ldf.
OBS: SHRINK não pode ser realizado junto com backup.
O SHRINK realiza a realocação das paginas não alocadas ou sujas, ele pega essas páginas e joga para frente, logo após isso realiza alguns cortes quando necessário, desta forma as páginas que contem limpas ficam no final do arquivo.
É possível recuperar muito espaço em disco com a utilização do SHRINK, então porque o SHRINK seria tão perseguidos por DBA'S?
O lado ruim de se utilizar o SHRINK é que no momento da realocação de páginas o SQL Server não leva em conta a ordenação dos dados e isso faz com que aumente a fragmentação dos índices.
Fragmentação dos índices SQL Server
Quando falamos em fragmentação estamos falando de aumento de custo de I/O e CPU, lembrando que o SQL Server leva em conta estes dois custos para gerar um plano de execução sempre que temos um aumento nestes custos temos uma perda em performance.
O fato do SHRINK realocar as páginas sem levar em conta a ordenação das páginas faz com que tenhamos uma fragmentação externa. Podemos avaliar a fragmentação em dois tipos interna e externa, vamos ver a diferença de cada um deles:
Fragmentação interna - É quando não temos a ordenação dos dados nas páginas de dados, o que ocorre muito no dia a dia quando temos muitos DELETE, INSERT e UPDATE fazendo com que aumente o numero de espaços não alocados na página de dados.
Fragmentação externa - É quando não temos uma ordenação das páginas em disco físico e é muito encontrada quando realizamos o SHRINK.
Todas as duas fragmentações aumentam o consumo de CPU, I/O e faz com que o cache do SQL Server contenha dados/planos ruins.
Neste artigo veremos duas formas de verificarmos a fragmentação, vamos ver como isso funciona:
DBCC SHOWCONTIG - basicamente vamos acompanhar os dados do LogicalFragmentation que como o nome diz trás o percentual de fragmentação lógica mas pode trazer muito mas informações como paginas ocupadas pelo índice, percentual de densidade, percentual de fragmentação de extend.
Observações: LogicalFragmentation quanto menor for, melhor.
DBCC SHOWCONTIG está para ser retirado das próximas versões do SQL Server, mas nos tempos atuais ainda podemos utilizá-lo.
SYS.DM_DB_INDEX_PHYSICAL_STATS - Uma função nativa do SQL Server que também nos trás algumas informações de fragmentação, em nossos scripts de cenário estarei me baseando nos dados da coluna AVG_FRAGMENTATION_IN_PERCENT que trás o percentual de fragmentação mas também trás muitas outras informações importantes.
Observações: Na documentação nova do SQL Server já é solicitado que se você utiliza DBCC SHOWCONTIG passe a utilizar a SYS.DM_DB_INDEX_PHYSICAL_STATS.
SHRINK X FRAGMENTAÇÃO
Bom acho que com está introdução já podemos avançar um pouco e começar a ver alguns cenários onde isto se aplica, eu criei um banco de dados DB_TESTE para está demonstração e vou trabalhar com 6 tabelas sendo 5 delas com dados e uma HEAP guardando o histórico do primeiro SYS.DM_DB_INDEX_PHYSICAL_STATS antes do SHRINK.
Antes de iniciar eu verifico o espaço usado pelo banco de dados com SP_SPACEUSED:
/*
VERIFICANDO TAMANHO DO BANCO E ESPAÇO UTILIZADO EM DISCO
DATABASE_SIZE = TAMANHO DO BANCO DE DADOS
UNALLOCATED SPACE = ESPAÇO NÃO ALOCADO
*/
SP_SPACEUSED
http://guiadba.com.br/wp-content/uploads/2016/04/sp_spaceused.pngNa imagem acima estamos vendo o espaço ocupado pelas 5 tabelas onde já realizei algumas inserções e deleções causando uma fragmentação interna.
Ciente de que minha tabela já se encontra fragmentada vou criar a minha tabela que guardará meu primeiro histórico de fragmentação, logo no script abaixo faço um SELECT INTO na SYS.DM_DB_INDEX_PHYSICAL_STATS e vamos ver como fica este resultado.
/*
CRIANDO TABELA PARA GUARDAR DADOS DE FRAGMENTAÇÃO DE INDICES
AVG_FRAGMENTATION_IN_PERCENT = FRAGMENTAÇÃO LOGICA MÉDIA DO ÍNDICE, QUANTO MENOR, MELHOR SERÁ
PAGE_COUNT = NUMERO DE PAGINAS UTILIZADAS
FILL_FACTOR = FILL_FACTOR
*/
IF OBJECT_ID ('TB_HISTORICO_FRAG') IS NOT NULL
DROP TABLE TB_HISTORICO_FRAG
SELECT DB_NAME(DB_ID()) AS BANCO, OBJECT_NAME(B.OBJECT_ID) AS TABELA, B.NAME, AVG_FRAGMENTATION_IN_PERCENT,PAGE_COUNT,FILL_FACTOR
INTO TB_HISTORICO_FRAG
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID
WHERE DB_NAME(DATABASE_ID) = 'DB_TESTE'
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC, OBJECT_NAME(B.OBJECT_ID), B.INDEX_ID
SELECT * FROM TB_HISTORICO_FRAG
http://guiadba.com.br/wp-content/uploads/2016/04/tb_historico_frag.pngNa imagem acima podemos observar o percentual de fragmentação dos índices nas 5 tabelas já criadas, algumas com altos percentuais e outras com menos, como informei eu mesmo já forcei uma fragmentação interna dos índices.
Melhorando ainda mas a nossa analise agora vamos ver como fica o resultado do DBCC SHOWCONTIG, logo teremos duas fontes de analise mesmo sabendo que a tabela de historico e o resultado do DBCC SHOWCONTIG não podem divergir, caso contrario o SQL Server jamais poderia solicitar a mudança do DBCC SHOWCONTIG para a SYS.DM_DB_INDEX_PHYSICAL_STATS.
/*
CUIDADO AO USAR, SERÁ RETIRADO EM NOVAS VERSÕES
DBCC SHOWCONFIG É UTILIZADO PARA RETORNAR A FRAGMENTAÇÃO DE TABELAS OU INDICES
LOGICALFRAGMENTATION = PORCENTAGEM DE PÁGINAS FRAGMENTADAS NO NÍVEL FOLHA DE UM ÍNDICE
*/
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
http://guiadba.com.br/wp-content/uploads/2016/04/dbcc-showconfig.pngBom agora temos duas fontes de analise de cenário, e nos print's vou seguir a regra de marcação da fragmentação para que facilite.
Neste momento vou mudar o cenário e vou executar um SHRINKDATABASE já com os índices bem fragmentados e vamos ver se muda alguma coisa em um cenário que já se encontra ruim.
DBCC SHRINKDATABASE (N'DB_TESTE')
http://guiadba.com.br/wp-content/uploads/2016/04/shrink.png
Logo após a execução vou mostrar todos os exemplos citados acima detalhando algumas diferenças do antes e depois.
SP_SPACEUSED
http://guiadba.com.br/wp-content/uploads/2016/04/sp_spaceused_antes_depois.pngLogo no nosso primeiro exemplo podemos observar que ganhamos muito espaço em disco, ou seja, o SHRINK realizou seu papel de diminuir os arquivos de data e log e enxugou nosso espaço não alocado, maravilha, vamos agora a algumas conseqüências, não detalhado mas a primeira delas é o auto crescimento do banco para algumas rotinas internas, o SQL Server tem a necessidade deste espaço e agora vamos as fragmentações.
SELECT DB_NAME(DB_ID()) AS BANCO, OBJECT_NAME(B.OBJECT_ID) AS TABELA, B.NAME,  AVG_FRAGMENTATION_IN_PERCENT,PAGE_COUNT,FILL_FACTOR
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID
WHERE DB_NAME(DATABASE_ID) = 'DB_TESTE'
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC, OBJECT_NAME(B.OBJECT_ID), B.INDEX_ID
GO
SELECT * FROM TB_HISTORICO_FRAG
http://guiadba.com.br/wp-content/uploads/2016/04/tb_historico_frag_antes_depois.pngSe notarmos a imagem anterior podemos observar que o SHRINK afetou os índices que tinham um menor percentual de fragmentação aumentando sua fragmentação (IDX_TEMP_ENQ_MALADIRETA26882) em pelo menos 80%.
Logo você pode se perguntar, mas para isso existe o rebuild e reorganize, não é? Então vamos a um cenário lindo onde podemos realizar um rebuild ou reorganize a qualquer momento em todos os nossos índices.
/* REALIZANDO UM REBIULD NOS ÍNDICES COM MAS DE 30% DE FRAGMENTAÇÃO
E REORGANIZE NOS ÍNDICES COM MENOS DE 30%
TENTANDO DIMINUIR A FRAGMENTAÇÃO DOS ÍNDICES
*/
ALTER INDEX IDX_TEMP_CRITERIOSELECAO80406 ON TEMP_CRITERIOSELECAO80406 REBUILD WITH (ONLINE = ON)
ALTER INDEX IDX_TEMP_REL_VALORES19286 ON TEMP_REL_VALORES19286 REBUILD WITH (ONLINE = ON)
ALTER INDEX IDX_TEMP_PROC_MALADIRETA26882_TITULOS ON TEMP_PROC_MALADIRETA26882_TITULOS REBUILD WITH (ONLINE = ON)
ALTER INDEX IDX_TEMP_REL_TEMP_REL_VALORES35254 ON TEMP_REL_TEMP_REL_VALORES35254 REBUILD WITH (ONLINE = ON)
ALTER INDEX IDX_TEMP_ENQ_MALADIRETA26882 ON TEMP_ENQ_MALADIRETA26882 REORGANIZE
http://guiadba.com.br/wp-content/uploads/2016/04/rebuild_reorganize.png
Como visto acima foi realizado o rebuild e reorganize levando em conta o percentual de fragmentação. Para aqueles índices com mais de 30% realizei um rebuild e os com o percentual menor que 30% um reorganize, agora vamos ver como ficaram os índices e se realmente estamos com tudo bonitinho.
Interessante... Nosso índices diminuíram e muito o percentual de fragmentação, lembrando que tínhamos um alto percentual de fragmentação interna e externa.
Vamos ver agora mas um detalhe interessante, vamos ver o mesmo espaço em disco, já que não realizamos nenhuma inserção na tabela tem que se manter o mesmo tamanho, vamos ver se isso é verdade?
SP_SPACEUSED
http://guiadba.com.br/wp-content/uploads/2016/04/sp_spaceused_rebuild-e-reorganize.png
Hum.. Aparentemente tem algo errado, não realizamos nenhuma inserção e nosso banco está com o tamanho muito maior do que antes do primeiro SHRINK, o que pode ter ocorrido?
Resposta: O rebuild e reorganize utilizaram espaço em seu banco de dados para realizar as operações necessárias.
Voltamos a estaca zero, ou pior agora temos menos espaço em disco ainda... Então vamos para um novo SHRINK para diminuir o tamanho dos arquivos.
DBCC SHRINKDATABASE (N'DB_TESTE')
Vamos agora analisar os índices, não é possível que tenha fragmentado novamente acabando com todo nosso trabalho.
SELECT DB_NAME(DB_ID()) AS BANCO, OBJECT_NAME(B.OBJECT_ID) AS TABELA, B.NAME,AVG_FRAGMENTATION_IN_PERCENT,PAGE_COUNT,FILL_FACTOR
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID
WHERE DB_NAME(DATABASE_ID) = 'DB_TESTE'
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC, OBJECT_NAME(B.OBJECT_ID), B.INDEX_ID
http://guiadba.com.br/wp-content/uploads/2016/04/shrink_apos-rebuild_reorganize.png
Está ai o motivo do SHRINK ser o nosso vilão e muitos terem raiva dele, todo nosso trabalho foi jogado por água abaixo e é por esse motivo que os DBA'S odeiam o SHRINK, espero que com esse artigo você tenha sanado toda sua duvida sobre o SHRINK e porque não é bom utilizá-lo. Vou ficar esperando seu comentário aqui no artigo para saber se você gostou e use este link como referencia caso precise.