Home - www.devmedia.com.br

Alavancando Performance - Desfragmentando

Fragmentar é o mesmo que quebrar em partes ou despedaçar. Dizemos que um índice está fragmentado quando suas páginas não estão contíguas OU apresentam-se pouco utilizadas. Exemplos:

(1) Índice com fragmentação Externa <=> as páginas estão desordenadas (1-2-2-5-4...)

                1 aabb .. .d.
2m5kkk
dkk mdm
d,,ddfdf
                  
3

mmh uyu
yt76nbi
jufmdfdf
mr9fdfdf

2

mmh uyu
yt76nbi dd
jufm df yrt
mr9fdf rr


5
aabb .. .d.
2m5kkk re
dkk mdm t
d,,d u juu
4

mmh uyu
yt76nbi df
jufm tu uua
mr9f yy red

(2) Índice com fragmentação Interna <=> páginas sub-utilizadas, com muito espaço disponível

                1
aabb .. .d.
                  
2

m

3


yuu

4
aabb .. .d.

2m5kkk re

5

mmh

Tanto a fragmentação externa quanto interna irão requerer um custo maior de I/O para operações de leitura. Após um processo de desfragmentação desse indice, reverteríamos esse quadro para:

(1.1) Indice após desfragmentação externa

                1
aabb .. .d.
2m5kkk
dkk mdm
d,,ddfdf
                  
2

mmh uyu
yt76nbi dd
jufm df yrt
mr9fdf rr

3

mmh uyu
yt76nbi
jufmdfdf
mr9fdfdf



4
mmh uyu
yt76nbi df
jufm tu uua
mr9f yy red
5

aabb .. .d.
2m5kkk re
dkk mdm t
d,,d u juu

(2.1) Indice após desfragmentação interna

                1
aabb .. .d.
m
yuu
aabb .. .d.

                  
2

2m5kkk re
mmh

Repare que um processo de leitura sequencial nos dois modêlos é bem mais eficiente após desfragmentação: em (1.1) as páginas estão dispostas sequencialmente, evitando-se o vai-e-vem dos cabeçotes de leitura que ocorriam em (1); em (2.1) o número de páginas do índice caiu para duas páginas, o que reduz consideravelmente o custo de I/O envolvido numa operação de leitura.

Agora como saber se um indice está fragmentado ? A resposta está no comando DBCC ShowContig. Esse comando deve ser executado sobre uma tabela, especificando-se o índice que se deseja analisar.

Primeiramente temos que levantar os indices da tabela que desejamos analisar. Para verificar os índices existentes na tabela Orders no database NorthWind com o comando sp_HelpIndex:

use NorthWind
go
exec sp_HelpIndex Orders
index_name
index_description index_keys
CustomerID nonclustered located on PRIMARY CustomerID
CustomersOrders nonclustered located on PRIMARY CustomerID
EmployeeID nonclustered located on PRIMARY EmployeeID
EmployeesOrders nonclustered located on PRIMARY EmployeeID
OrderDate nonclustered located on PRIMARY OrderDate
PK_Orders clustered, unique, primary key located on PRIMARY OrderID
ShippedDate nonclustered located on PRIMARY     ShippedDate
ShippersOrders    nonclustered located on PRIMARY     ShipVia
ShipPostalCode    nonclustered located on PRIMARY     ShipPostalCode

Agora que sabemos o nome dos indices podemos verificar a fragmentação do indice CustomerId.

dbcc showcontig('orders','CustomerID')
DBCC SHOWCONTIG scanning 'Orders' table...
Table: 'Orders' (2116202589); index ID: 2, database ID: 6
LEAF level scan performed.

- Pages Scanned.............................…..: 4
- Extents Scanned..........................…...: 2
- Extent Switches.........................….….: 2
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 25.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page...............…....: 3946.0
- Avg. Page Density (full)................……...: 51.25%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Vamos agora traduzir o que o showcontig nos revelou:

  • Apesar existirem apenas 4 páginas na tabela (=Pages Scanned), a tabela utiliza 2 extents (=Extents Scanned). Isso é ruim, e é um indicador de fragmentação. Sabemos que uma extent acomoda 8 páginas, então porque 2 extents são utilizadas para armazenar somente 4 páginas ???
  • Para scanear toda a tabela foram realizadas 2 extents switches, o que significa que a leitura começa na extent-1, vai para a extent-2 e depois retorna para a extent-1 para finalizar o serviço. Esse vai-e-volta é um indicador de fragmentação externa.
  • O número médio de páginas por extent (=Avg.Pages Per Extent) é 2. Esse número é tanto melhor quanto mais próximo. Outro indicador de fragmentação.
  • Scan Density é o percentual de fragmentação externa. Num mundo perfeito, esse indicador deve estar próximo de 100%.
  • As páginas desse indice estão sub-utilizadas, é o que diz o indicador Avg.Page Density = 51.25%. Num mundo perfeito, quanto mais próximo de 100%, melhor.

Bom até agora constatamos um problema, e agora? A solução está em reestruturar o índice com o comando DBCC dbReindex. A reindexação corrige problemas de fragmentação externa E interna como veremos a seguir.

dbcc dbreindex('orders','CustomerId')
dbcc showcontig('orders','CustomerID')
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'Orders' table...
Table: 'Orders' (2116202589); index ID: 2, database ID: 6
LEAF level scan performed.
- Pages Scanned.............................…...: 3
- Extents Scanned...........................…...: 1
- Extent Switches.........................……...: 0
- Avg. Pages per Extent.........................: 3.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 2562.7
- Avg. Page Density (full)................….....: 88.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Compare os resultados dos comandos showcontig antes e depois da desfragmentação e sinta a diferença.

Por hoje é só.
Um grande abraço a todos e até a próxima !

Paulo Ribeiro

 

Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine. Atua como DBA sênior em SQL Server na Livraria e Papelaria Saraiva S/A.

  Monitorando deadlocks com Trace Flags    
  Monitorando deadlocks com o Profiler    
  Variáveis tipo TABLE    
  Tabelas Temporárias    
  Subqueries Parte II: Queries correlatas    
  Subqueries – Parte I    
  Gerenciando Bloqueios–Parte II    
  Gerenciando Bloqueios–Parte I    
  Porque qualificar o owner na chamada de stored-procedures    
  Explorando os Tipos de Join – Parte II    
  Explorando os Tipos de Join – Parte I    
  Versões existentes do SQL Server 2000    
  Tuning - Plano de Execução no SQL Server - Parte 4    
  Tuning - Plano de Execução no SQL Server - Parte 3    
  Tuning - Plano de Execução no SQL Server - Parte 2    
  Tuning - Plano de Execução no SQL Server - Parte 1    
  Tuning - Estatísticas de I/O    
  Desfragmentando Índices no SQL Server    
  Procedures Não Documentadas
no SQL Server 2000 Parte 2
   
  Procedures Não Documentadas
no SQL Server 2000.
   
  SQL Server 2005 - YUKON    
  SQL Server 2000: o Contra-Ataque.    
  Boas-Vindas    
     

 

Todos os direitos reservados: DevMedia Group
SQL Magazine - 2004