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
Comments
Anonymous
February 08, 2007
The comment has been removedAnonymous
February 11, 2007
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!Anonymous
March 03, 2007
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 !!!!