Bit a Bit com Shrink no SQL Server

Shrink ou como dizem “Shrinkar” um banco de dados para liberar espaços sempre foi um assunto na roda de bate papo entre DBAs e Desenvolvedores. Na visão DBA, Shrink deveria ser excluído do SQL Server, já na visão desenvolvedor ou até mesmo de analista de Infraestrutura esse comando é magico.

 Shrink no bit

Shrink é um recurso do SQL Server que nos permite diminuir arquivos do Banco de Dados, seja ele um Primary Database Data File (MDF), Secundary Database Data File (NDF) ou ainda o Log Data File (LDF) mais conhecido como Transaction Log. A execução do Shrink move as páginas sujas (com dados) do final de cada arquivo para o início, assim realocando as paginas limpas para o fim do arquivo e no final da operação ocorre o corte, liberando esses espaços em disco de volta para o Sistema Operacional.

Essa movimentação de páginas não leva em consideração a ordem lógica dos dados, o que gera a fragmentação de índices. Em muitos casos o Shrink é executado com a ideia de diminuir o tamanho do banco de dados para melhorar a performance, pelo contrário, o Shrink piora ainda mais a performance, pois ao movimentar as páginas para liberar a maior quantidade de espaço possível para o Sistema Operacional as paginas ficam totalmente fora da ordem logica, consequentemente fragmentando drasticamente os índices, principalmente os clustered.

Além de gerar essa fragmentação o que trará problemas de performance, pois o plano de execução vai para espaço junto as estatísticas de I/O, essa rotina também gera uma grande sobrecarga de CPU e processamento de I/O elevando ainda mais o gargalo em seu ambiente.

Vamos simular essa fragmentação, a ideia é cria um banco de dados com duas tabelas, executar uma carga de dados em ambas, realizar o TRUNCATE em uma delas e depois executar o Shrink, tudo isso para simular um expurgo de dados onde comumente o pessoal executa o Shrink após esse tipo de manutenção no banco de dados para liberar os espaço em disco dos dados que foram deletados.

SET NOCOUNT ON

GO

USE master

GO

CREATE DATABASE DBShrink

GO

USE DBShrink

GO

— tabela de expurgo

CREATE TABLE DadosExpurgo

(

    ID       INT IDENTITY,

    Texto    CHAR(255)

)

GO

— Gerando Massa de Dados

INSERT INTO DadosExpurgo (Texto)

VALUES (‘Shrink – DBA Pira’)

GO 5000

— Tabela de Dados

CREATE TABLE Dados

(

    ID       INT IDENTITY,

    Texto    CHAR(200)

)

GO

— Criando indice Clustered

CREATE CLUSTERED INDEX idx_Dados ON Dados (ID);

GO

— Gerando Massa de Dados

INSERT INTO Dados (Texto)

VALUES (‘Shrink – DBA Pira’)

GO 5430

Até este ponto, criamos duas tabelas e realizamos uma carga. Vamos analisar a fragmentação até o momento:

— Retorna Fragmentação

SELECT

    [avg_fragmentation_in_percent]

FROM sys.dm_db_index_physical_stats (DB_ID (N’DBShrink’), OBJECT_ID (N’Dados’), 1, NULL, ‘LIMITED’)

GO

Aproximadamente 3% de fragmentação devido a operação de INSERT.

Abaixo, vamos simular um expurgo de dados, limpando uma das tabelas.
Mas antes, por curiosidade vamos avaliar o tamanho do banco de dados atual:

SP_HELPDB DBShrink

Agora sim, o TRUNCATE para limpar a tabela:

— Realizando o Expurgo

TRUNCATE TABLE DadosExpurgo

GO

Vamos analisar o tamanho do banco após o TRUNCATE:

Opa, mesmo realizando o TRUNCATE o tamanho do banco continua o mesmo? Não era para ter diminuindo já que executamos um TRUNCATE? A resposta é NÂO. O SQL Server já alocou esse espaço em disco pois teve a necessidade em determinado momento e não irá desfazer até que seja executado explicitamente essa liberação de disco. Essa execução de liberação é o Shrink, pronto, está aí um dos motivos onde muitos executam essa rotina com o objetivo de diminuir o tamanho do banco de dados, mas na realidade os impactos negativos são muito mais evidentes e tendenciosos.

Ok, agora vamos executar o Shrink para devolver esse espaço para o Sistema Operacional:

— Shrink

DBCC SHRINKDATABASE (DBShrink)

GO

Analisando o tamanho do banco novamente:

SP_HELPDB DBShrink

Observando o nível de fragmentação, podemos verificar que passou de 3% para 75%.

— Retorna Fragmentação após o Shrink

SELECT

    [avg_fragmentation_in_percent]

FROM sys.dm_db_index_physical_stats (

    DB_ID (N’DBShrink’), OBJECT_ID (N’Dados’), 1, NULL, ‘LIMITED’);

GO

A fragmentação subiu exponencialmente, se refletirmos sobre isso vamos entender o problema e impactados negativos que o Shrink pode gerar se executarmos em Produção. Essa simulação foi em ambiente controlado com duas tabelas, mas imagine esses números em grande escala com bancos de dados de 600, 800GB com milhares de tabelas e índices, o problema que podemos ter se executarmos esse comando sem consciência. O mito Paul Randal escreveu diversos artigos sobre essa rotina e ele comenta algo que acho muito interessante, o Shrink existe e para determinados momentos e ambientes ele é muito útil e pode nos salvas de alguns problemas, porem, devemos saber quando, onde e como executa-lo e sempre de forma conscientes dos impactos que podemos gerar.

Imagine um banco de dados em produção com 600GB de dados (MDF), 150GB de Log (LDF) e para uma determinada atividade foi solicitado uma copia deste banco para o ambiente de desenvolvimento. Normalmente é recomendado e uma boa pratica colocar todos os bancos de desenvolvimento em Recovery Model SIMPLE, neste tipo de situação não vejo como uma má pratica executar o Shrink para diminuir o log, pois estamos em ambiente de desenvolvimento e na grande maioria das empresas temos hardware limitado para servidores dessa função.

Levando em consideração os aspectos observados,  podemos concluir que o Shrink é uma feature interessante que tem suas particularidades, cabe a nós DBAs e/ou responsáveis pelo ambiente analisar a real necessidade levando em considerações os ricos envolvidos e sempre avaliando os impactos gerados nessa operação.

Bons estudos.

Comente sobre isso: