Shrink – Veja como acabar com seu banco de dados

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:

[Language code = "sql"]
/*
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
[/código]

Na 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.

[Language code = "sql"]
/*
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
GOTA TB_HISTORICO_FRAG TABLE

DB_NAME SELECIONAR (DB_ID ()) como BANCO, OBJECT_NAME (B.OBJECT_ID) AS TABELA, B.NAME, & amp; nbsp; Avg_fragmentation_in_percent, PAGE_COUNT, FILL_FACTOR
INTO TB_HISTORICO_FRAG
DE 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 E 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
[/código]

Na 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.

[Language code = "sql"]
/*
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 COM TABLERESULTS, ALL_INDEXES
[/código]

Bom 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.

[Language code = "sql"]
DBCC SHRINKDATABASE (N'DB_TESTE')
[/código]

Logo após a execução vou mostrar todos os exemplos citados acima detalhando algumas diferenças do antes e depois.

[Language code = "sql"]
SP_SPACEUSED
[/código]

Logo 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.

[Language code = "sql"]
DB_NAME SELECIONAR (DB_ID ()) como BANCO, OBJECT_NAME (B.OBJECT_ID) AS TABELA, B.NAME, & amp; nbsp; Avg_fragmentation_in_percent, PAGE_COUNT, FILL_FACTOR
- TB_HISTORICO_FRAG INTO
DE 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 E 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
IR

* SELECT FROM TB_HISTORICO_FRAG
[/código]

Se 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.

[Language code = "sql"]
/* 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 RECONSTRUÇÃO COM (ONLINE = ON)

ALTER INDEX IDX_TEMP_REL_VALORES19286 ON TEMP_REL_VALORES19286 RECONSTRUÇÃO COM (ONLINE = ON)

ALTER INDEX IDX_TEMP_PROC_MALADIRETA26882_TITULOS ON TEMP_PROC_MALADIRETA26882_TITULOS RECONSTRUÇÃO COM (ONLINE = ON)

ALTER INDEX IDX_TEMP_REL_TEMP_REL_VALORES35254 ON TEMP_REL_TEMP_REL_VALORES35254 RECONSTRUÇÃO COM (ONLINE = ON)

ALTER INDEX IDX_TEMP_ENQ_MALADIRETA26882 ON TEMP_ENQ_MALADIRETA26882 REORGANIZE
[/código]

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?

[Language code = "sql"]
SP_SPACEUSED
[/código]

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.

[Language code = "sql"]
DBCC SHRINKDATABASE (N'DB_TESTE')
[/código]

Vamos agora analisar os índices, não é possível que tenha fragmentado novamente acabando com todo nosso trabalho.

[Language code = "sql"]
DB_NAME SELECIONAR (DB_ID ()) como BANCO, OBJECT_NAME (B.OBJECT_ID) AS TABELA, B.NAME, avg_fragmentation_in_percent, PAGE_COUNT, FILL_FACTOR
- TB_HISTORICO_FRAG INTO
DE 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 E 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
[/código]

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.