Banco de Dados

Ξ 6 comentários

Shrink – Veja como acabar com seu banco de dados

publicado por Thiago Cruz

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]

SP_SPACEUSEDNa 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]

TB_HISTORICO_FRAGNa 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]

DBCC SHOWCONFIGBom 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]

ENCOLHER

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]

SP_SPACEUSED_ANTES_DEPOISLogo 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]

TB_HISTORICO_FRAG_ANTES_DEPOISSe 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]

REBUILD_REORGANIZE

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]

SP_SPACEUSED_REBUILD E REORGANIZE

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]

SHRINK_APOS REBUILD_REORGANIZE

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.

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Compare preços de Uber, 99 e Taxi

Minimum Way

Autor

Formado em Administração de empresas, trabalho no ramo da TI a cerca de 11 anos, sendo 10 anos como DBA, atualmente trabalhando com DBA e prestando serviço externo como Consultor SQL Server 2008, 2008 R2 e 2014, autor de artigos para Devmedia e Ti Especialistas. Profissional certificado MCP na plataforma de Banco de Dados e co-fundador do site Guia DBA. Site: www.guiadba.com.br

Thiago Cruz

Comentários

6 Comments

  • Excelente artigo, Thiago! Muito obrigado pelas valiosas informações sobre o SHRINK. Valeu, um abraço!

    • Rafael, obrigado pelo comentário espero ajuda-los ainda mas.

  • Depende da visão sobre o uso do shrink.
    Em um ambiente, por exemplo, com falta de espaço em disco/storage (o que não é difícil encontrarmos nos ambientes de T.I. atualmente) o uso do shrink é uma forma de se obter espaços em disco.
    Agora, colocar o shirink nas rotinas administrativas diarias, dai sim, concordo que não faz sentido o uso.

    Como eu não vivo na T.I. em um mundo ideal e acho que 99% dos profissionais também não, não vejo problemas em utilizá-lo com uma certa dose de conhecimento.

  • Esclarecido porque eu não tinha resultado com essa porra.

  • Uma pena que as imagens não estão abrindo mais….

  • Qual o melhor plano de manutenção pra esse caso?

    Pensei em algo como:
    1 – Remoção dos índices
    2 – Shrink
    3 – Recriação dos índices

    Acha que o efeito seria positivo?

You must be logged in to post a comment.

Busca

Patrocínio

Publicidade




Siga-nos!

Newsletter: Inscreva-se

Para se inscrever em nossa newsletter preencha o formulário.