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]
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]
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]
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]
[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]
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.
Leave a Comment