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:
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. |
|
|
|
|