Evitando fragmentação através de (re)modelagem


 


Depois de algum tempo longe do meu blog, estou voltando a ativa. Como diariamente passo por algumas situações interessantes nos clientes, passei a anotar aquelas que dariam bons artigos. Tomara que eu acerte nas escolhas!


 


Chega de conversa e vamos para o que importa... SQL SERVER!


 


Muitos DBAs possuem um conjunto de boas práticas que costumam aplicar nos servidores que gerenciam, e uma delas é a reindexação semanal ou mensal das tabelas nos bancos de dados mais críticos. Quando o caso de uma tabela específica é muito crítico, surge a necessidade de se fazer uma reindexação diária ou até mesmo diversas vezes ao dia.


E se a tabela é muito grande e sua janela de manutenção não comporta a reindexação? Ou então sua aplicação deve funcionar 24x7 e a reindexação não pode ser feita? (deixamos de lado a possibilidade de reindexação online do SQL Server 2005).


** Se isso acontecer, você sai do furacão diário, senta em um canto calmo, reflete um pouco e arruma uma maneira de evitar a fragmentação! **


 


O seguinte cenário é a simplificação de um problema real.


Imagine um banco (banco de verdade, pense em Banco do Brasil, Bradesco, Itaú, etc.) que armazena em uma tabela o valor atual dos saldos de todos os seus clientes. Juntamente com o saldo está a data na qual aquele valor era válido, então existirá mais de uma entrada por cliente na tabela.


O script abaixo cria a tabela e insere o saldo atual de 10.000 clientes.


 


/*


                1) Cria a tabela SaldoConta com chave primária e um índice não clusterizado


*/


if exists(select [name] from sysobjects where xtype = 'U' and [name] = 'SaldoConta')


                DROP TABLE SaldoConta


 


CREATE TABLE SaldoConta (


CodigoCliente INT NOT NULL,


DataSaldo DATETIME NOT NULL,


SaldoAtual MONEY NOT NULL,


NomeBanco CHAR(100) NOT NULL,


OutraInformacao CHAR(100) NULL


)


go


 


ALTER TABLE SaldoConta


ADD CONSTRAINT PK_SaldoConta


PRIMARY KEY (CodigoCliente, DataSaldo)


go


 


CREATE NONCLUSTERED INDEX idx_DataSaldo


ON SaldoConta (DataSaldo)


go


 


 


/*


                2) Insere 10.000 clientes com diferentes saldos diferentes.


*/


DECLARE @Cont INT


SET @Cont = 1


 


WHILE @Cont < 10000


BEGIN


 


                INSERT INTO SaldoConta VALUES (@Cont, '20070131', ((RAND() * 1000) * DATEPART(ss, GETDATE())), 'Qualquer um', 'SQL Server 2005')


                SET @Cont = @Cont + 1


END


go


 


Se verificarmos a fragmentação da tabela...


 


/*


                3) Verifica a fragmentação


*/


DBCC SHOWCONTIG(SaldoConta, PK_SaldoConta)


go


/*


                Ótimo resultado


 


DBCC SHOWCONTIG scanning 'SaldoConta' table...


Table: 'SaldoConta' (1977058079); index ID: 1, database ID: 9


TABLE level scan performed.


- Pages Scanned................................: 286


- Extents Scanned..............................: 37


- Extent Switches..............................: 36


- Avg. Pages per Extent........................: 7.7


- Scan Density [Best Count:Actual Count].......: 97.30% [36:37]


- Logical Scan Fragmentation ..................: 0.00%


- Extent Scan Fragmentation ...................: 2.70%


- Avg. Bytes Free per Page.....................: 89.8


- Avg. Page Density (full).....................: 98.89%


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


*/


 


Até aqui tudo certinho, porém diariamente todo mundo movimenta dinheiro e quando a consolidação é feita, novos registros são inseridos, cada um contendo o saldo inicial do próximo dia. Vale notar que a tabela não sofre alterações durante o horário comercial e tudo é processado durante a noite.


Script para simular o processamento noturno e resultado de fragmentação:


 


/*


                4) Processamento diário e fragmentação


*/


DECLARE @SaldoAtual MONEY


DECLARE @Cont INT


SET @Cont = 1


 


WHILE @Cont < 10000


BEGIN


 


                SELECT @SaldoAtual = SaldoAtual FROM SaldoConta WHERE CodigoCliente = @Cont


                -- Esse meu banco é massa, o dinheiro somente aumenta nas contas...


                INSERT INTO SaldoConta VALUES (@Cont, '20070201', @SaldoAtual + ((RAND() * 10) * DATEPART(ss, GETDATE())), 'BANCO DO LUTI - Só o BB mesmo para fazer isso...', 'Keep walking')


                SET @Cont = @Cont + 1


END


go


 


DBCC SHOWCONTIG(SaldoConta, PK_SaldoConta)


go


/*


DBCC SHOWCONTIG scanning 'SaldoConta' table...


Table: 'SaldoConta' (1977058079); index ID: 1, database ID: 9


TABLE level scan performed.


- Pages Scanned................................: 857


- Extents Scanned..............................: 109


- Extent Switches..............................: 596


- Avg. Pages per Extent........................: 7.9


- Scan Density [Best Count:Actual Count].......: 18.09% [108:597]


- Logical Scan Fragmentation ..................: 33.37%


- Extent Scan Fragmentation ...................: 2.75%


- Avg. Bytes Free per Page.....................: 2752.3


- Avg. Page Density (full).....................: 66.00%


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


*/


 


Aqui a tabela está fragmentada e o cliente, após o processamento, executava um DBCC REINDEX para diminuir a fragmentação.


 


/*


                5) Para corrigir o problema de fragmentaçao


*/


DBCC DBREINDEX('Inside.dbo.SaldoConta', 'PK_SaldoConta')


go


 


DBCC SHOWCONTIG(SaldoConta, PK_SaldoConta)


go


/*


DBCC SHOWCONTIG scanning 'SaldoConta' table...


Table: 'SaldoConta' (1977058079); index ID: 1, database ID: 9


TABLE level scan performed.


- Pages Scanned................................: 572


- Extents Scanned..............................: 72


- Extent Switches..............................: 71


- Avg. Pages per Extent........................: 7.9


- Scan Density [Best Count:Actual Count].......: 100.00% [72:72]


- Logical Scan Fragmentation ..................: 0.17%


- Extent Scan Fragmentation ...................: 1.39%


- Avg. Bytes Free per Page.....................: 89.8


- Avg. Page Density (full).....................: 98.89%


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


*/


 


E se ao invés de corrigirmos a fragmentação através do DBCC REINDEX fosse possível evitá-la? Neste caso ganharíamos parte do tempo gasto para reorganizar os dados nas páginas.


Uma maneira de atingirmos nosso objetivo nesse cenário é:


 


1)      Recrie o índice cluster trocando a ordem das colunas (DataSaldo e CodigoCliente)


2)      Crie o índice não cluster no campo CodigoCliente, para continuar suportando consultas baseadas no código do cliente.


 


Dessa forma matamos o problema que estava causando a fragmentação (inserção de registros no meio da tabela devido ao código do cliente) e garantimos que tudo será inserido na devida ordem e no fim da tabela, ordenado pela data e código do cliente (crescente). Nesse caso o procedimento real de consolidação deveria processar os clientes de forma ordenada, baseado no seu código.


Vale lembrar que esse comportamento acontece devido à característica do índice cluster em manter os dados ordenados no seu nível folha de acordo com as colunas que compõe sua chave.


 


Agora re-execute os passos anteriores com uma diferente organização dos índices.


 


/*


                6) Evitando fragmentação ao invés de remediá-la


*/


if exists(select [name] from sysobjects where xtype = 'U' and [name] = 'SaldoConta')


                DROP TABLE SaldoConta


 


CREATE TABLE SaldoConta (


CodigoCliente INT NOT NULL,


DataSaldo DATETIME NOT NULL,


SaldoAtual MONEY NOT NULL,


NomeBanco CHAR(100) NOT NULL,


OutraInformacao CHAR(100) NULL


)


go


 


/*


                7) Organizando os índices e inserindo os dados


*/


ALTER TABLE SaldoConta


ADD CONSTRAINT PK_SaldoConta


PRIMARY KEY (DataSaldo, CodigoCliente)


go


 


-- Crie o índice não clusterizado na segunda coluna do índice cluster, para continuar suportando pesquisas no campo CodigoCliente


CREATE NONCLUSTERED INDEX idx_DataSaldo


ON SaldoConta (DataSaldo)


go


 


DECLARE @Cont INT


SET @Cont = 1


 


WHILE @Cont < 10000


BEGIN


 


                INSERT INTO SaldoConta VALUES (@Cont, '20070131', ((RAND() * 1000) * DATEPART(ss, GETDATE())), 'Qualquer um', 'SQL Server 2005')


                SET @Cont = @Cont + 1


END


go


 


/*


                8) Neste momento a fragmentação é igual a obtida com a outra estrutura de índices


*/


DBCC SHOWCONTIG(SaldoConta, PK_SaldoConta)


go


/*


DBCC SHOWCONTIG scanning 'SaldoConta' table...


Table: 'SaldoConta' (2057058364); index ID: 1, database ID: 9


TABLE level scan performed.


- Pages Scanned................................: 286


- Extents Scanned..............................: 38


- Extent Switches..............................: 37


- Avg. Pages per Extent........................: 7.5


- Scan Density [Best Count:Actual Count].......: 94.74% [36:38]


- Logical Scan Fragmentation ..................: 0.35%


- Extent Scan Fragmentation ...................: 10.53%


- Avg. Bytes Free per Page.....................: 89.8


- Avg. Page Density (full).....................: 98.89%


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


*/


 


/*


                9) Executa o processamento diário


*/


DECLARE @SaldoAtual MONEY


DECLARE @Cont INT


SET @Cont = 1


 


WHILE @Cont < 10000


BEGIN


 


                SELECT @SaldoAtual = SaldoAtual FROM SaldoConta WHERE CodigoCliente = @Cont


                -- Esse meu banco é massa, o dinheiro somente aumenta nas contas...


                INSERT INTO SaldoConta VALUES (@Cont, '20070201', @SaldoAtual + ((RAND() * 10) * DATEPART(ss, GETDATE())), 'BANCO DO LUTI - Só o BB mesmo para fazer isso...', 'Keep walking')


                SET @Cont = @Cont + 1


END


go


 


Após o processamento feito com a nova organização dos índices, conseguimos manter a tabela bem organizada, veja abaixo:


 


DBCC SHOWCONTIG(SaldoConta, PK_SaldoConta)


go


/*


DBCC SHOWCONTIG scanning 'SaldoConta' table...


Table: 'SaldoConta' (2057058364); index ID: 1, database ID: 9


TABLE level scan performed.


- Pages Scanned................................: 572


- Extents Scanned..............................: 74


- Extent Switches..............................: 73


- Avg. Pages per Extent........................: 7.7


- Scan Density [Best Count:Actual Count].......: 97.30% [72:74]


- Logical Scan Fragmentation ..................: 0.17%


- Extent Scan Fragmentation ...................: 8.11%


- Avg. Bytes Free per Page.....................: 89.8


- Avg. Page Density (full).....................: 98.89%


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


*/


 


A meu ver, a alteração trás os seguintes benefícios:


 


1 – Pesquisas por intervalos de datas, exemplos: total de dinheiro acumulado em todas as contas correntes do banco para um determinado dia. Ou então, variação do total acumulado entre dois dias específicos.


2 – As consultas por código do cliente continuam rápidas, pois o índice não-cluster acha diretamente o cliente em questão. Não imagino muitos cenários onde o código do cliente possa ser útil em range scans.


3 – Não é necessário mais executar o DBCC REINDEX ao fim do processamento e, talvez (não cheguei a verificar) o tempo para o processamento diminua porque não serão necessários page splits para inserir os dados.


 


Quando estamos falando de 10.000 registros, o custo de reindexação não é nada, mas vamos imaginar um banco com 2.000.000 clientes:


- A cada mês a tabela cresceria 60.000.000 de registros (30 dias).


- Suponhamos que um registro ocupe em média 100 bytes.


- Por página teremos 80 registros.


- Tamanho da tabela em um mês: 750.000 páginas x 8.192 Bytes = 6.144.000.000 Bytes = +/- 6 GB.


 


Para finalizar, faça o teste e reindexe 6 GB. J


 


Espero que o artigo tenha sido interessante, ele demonstra como podemos resolver problemas do cotidiano usando um pouco de criatividade.


Em anexo está um script com todos os passos descritos pelo artigo.


 


[]s


Luti


Luciano.moreira@microsoft.com

AvoidingFragmentation_01.sql

Comments (3)

  1. Olá Luciano,

    Show de bola o artigo !!

    Apenas uma obs. no ítem  7 "Organizando os índices e inserindo os dados" vc fala para criar o índice não cluster na coluna CodigoCliente mas o exemplo faz diferente..

    — Crie o índice não clusterizado na segunda coluna do índice cluster,

    para continuar suportando pesquisas no campo CodigoCliente

    CREATE NONCLUSTERED INDEX idx_DataSaldo

    ON SaldoConta (DataSaldo)

    go

    um  abraço e parabéns pelo blog…adicionarei ele em meu site 🙂

    Nilton Pinheiro

    SQL Server MVP

    http://www.mcdbabrasil.com.br

  2. MSDN Archive says:

    Oi Nilton, eu realmente errei no script (CTRL+C, CTRL+V :-)). O certo seria:

    CREATE NONCLUSTERED INDEX idx_CodigoCliente

    ON SaldoConta (CodigoCliente)

    go

    Muito brigado.

    Abraços!

  3. laertejunior says:

    Luti, partindo da sua explicação, seria correto dizer que se a modelagem for feita utlizando surrogate keys no indce cluster, unique indexes nos campos chaves e nonclustered index nos campos de consulta a necessidade de reindex também seria minima ou quase nula ?.Pergunto isso, pois vc trocou a ordem das colunas no indice cluster, fazendo as linhas sempre serem inseridas no final então se for trabalhado com campos indentitys no indice cluster nas tabelas seria a saida ?..

    Obrigado !!!!

Skip to main content