Fragmentação (cont...) - Extent switches
Continuando a análise do DBCC SHOWCONTIG, pretendo mostrar como a organização das páginas fisicamente pode gerar uma baixa densidade no item scan density. Para uma tabela pequena em que todas as páginas estão no cache de dados, isso não é problema, mas quando a questão escala para proporções maiores, o impacto pode ser ruim para as consultas.
O script utiliza o comando DBCC PAGE que deve ser habilitado através da instrução DBCC TRACEON(3604) e nos permite ver a estrutura e os dados de cada página do banco. Para mais informações sobre o DBCC Page, visite o site: https://support.microsoft.com/kb/83065/en-us
Está em anexo o script com os testes que eu fiz.
Para mais informações, leiam o Inside SQL Server 2000 ;-) ou me mandem um e-mail.
[]s
Luti
=============== SCRIPT ===================================
/******************************************************************************
Autor: Luciano Caixeta Moreira
Data: 11/02/2006
Descrição: Continua a análise de fragmentação, colocando um cenário onde
devem existir muitos extent switches.
Histórico:
-
******************************************************************************/
USE MASTER
GO
IF EXISTS (SELECT * FROM SYSDATABASES WHERE [Name] = 'Inside')
BEGIN
DROP DATABASE Inside
END
GO
CREATE DATABASE Inside
ON
(
Name = 'Inside_data',
FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_data.mdf',
Size = 10MB,
FileGrowth = 5MB,
MaxSize = UNLIMITED
)
LOG ON
(
Name = 'Inside_log',
FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_log.mdf',
Size = 5MB,
FileGrowth = 3MB,
MaxSize = UNLIMITED
)
GO
USE Inside
GO
/*
Cria a nossa tabela de trabalho
*/
IF EXISTS(SELECT * FROM sysObjects WHERE [Name] = 'Fragmentation' AND XType = 'U')
BEGIN
DROP TABLE Fragmentation
END
CREATE TABLE Fragmentation
(
Codigo INT NOT NULL CONSTRAINT PK_Codigo PRIMARY KEY,
Name VARCHAR(8000)
)
GO
/*
Insere os valores ímpares
*/
Declare @Contador INT
SET @Contador = 1
WHILE @Contador < 1000
BEGIN
INSERT INTO Fragmentation VALUES (@Contador, Replicate('L', 8000))
SET @Contador = @Contador + 2
END
GO
/*
Insere os valores pares
*/
Declare @Contador INT
SET @Contador = 2
WHILE @Contador < 1000
BEGIN
INSERT INTO Fragmentation VALUES (@Contador, Replicate('L', 8000))
SET @Contador = @Contador + 2
END
GO
/*
Como o regsitro ocupa mais de 8000 bytes para armazenar a informação, somente cabe
uma linha por página, portanto nesse momento, a estrutura esperada das páginas é
(aproximadamente) a seguinte:
Extent 1
Pag 1 - Codigo 1
Pag 2 - Codigo 3
Pag 3 - Codigo 5
Pag 4 - Codigo 7
Pag 5 - Codigo 9
Pag 6 - Codigo 11
Pag 7 - Codigo 13
Pag 8 - Codigo 15
.....
Extent 63
Pag 1 - Codigo 2
Pag 2 - Codigo 4
Pag 3 - Codigo 6
Pag 4 - Codigo 8
Pag 5 - Codigo 10
Pag 6 - Codigo 12
Pag 7 - Codigo 14
Pag 8 - Codigo 16
Vamos verificar isso logo abaixo...
*/
select * from master..sysdatabases where [name] = 'Inside'
select * from sysindexes where [id] = Object_id('Fragmentation')
GO
/*
Resultado...
first: 0x1C0000000100
root: 0x0F0000000100
firstIAM: 0x190000000100
*/
DBCC TRACEON(3604)
GO
/*
No meu caso, o database id é 7. Consulte a tabela sysdatabases para verificar qual
o dbid do banco Inside no SQL Server que você está trabalhando.
*/
DBCC PAGE(7, 1, 15, 3) -- Root do índice 0x0F
DBCC PAGE(7, 1, 710, 3) -- Nível não folha do índice
DBCC PAGE(7, 1, 711, 3) -- Nível não folha do índice
GO
/*
Analisando a saída do DBCC PAGE(7, 1, 710, 3) podemos ver claramente como estão as páginas
no disco. As ímpares são as primeira páginas (a partir da 28) enquanto as pares estão após
as ímpares, a partir da 589.
FileId PageId Row Level ChildFileId ChildPageId Codigo
------ ----------- ------ ------ ----------- ----------- -----------
1 710 0 0 1 28 NULL
1 710 1 0 1 589 2
1 710 2 0 1 29 3
1 710 3 0 1 590 4
1 710 4 0 1 30 5
1 710 5 0 1 591 6
1 710 6 0 1 31 7
1 710 7 0 1 592 8
1 710 8 0 1 33 9
*/
DBCC PAGE (7, 1, 28, 3)
GO
/*
Analisando a página com o registro de código 1, podemos verificar no
seu cabeçalho a seguinte informação:
Page @0x45562000
----------------
m_pageId = (1:28) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1977058079 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (1:589) pminlen = 8 m_slotCnt = 1
m_freeCnt = 79 m_freeData = 8111 m_reservedCnt = 0
m_lsn = (8:1413:5) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 2
A próxima página física em disco é a página 29, que contém o registro 3.
Enquanto a próxima página lógica é a 589 (item m_nextPage) que contém o
código 2, garantindo a ordenação do índice clusterizado.
*/
DBCC PAGE (7, 1, 589, 3)
GO
/*
Analisando a página com o registro de código 2, podemos verificar no
seu cabeçalho a seguinte informação:
Page @0x4516E000
----------------
m_pageId = (1:589) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 1977058079 m_indexId = 0 m_prevPage = (1:28)
m_nextPage = (1:29) pminlen = 8 m_slotCnt = 1
m_freeCnt = 79 m_freeData = 8111 m_reservedCnt = 0
m_lsn = (8:1413:10) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 1
Notem que a próxima página lógica é a página 29, que contém o registro
de código 3, enquanto a anterior é a página 28 (m_prevPage) que contém o
registro de código 2.
-> O nível folha dos índices clusterizados são encadeados através
dos registros m_nextPage e m_prevPage encontrados no cabeçalho de cada página.
*/
DBCC SHOWCONTIG ('Fragmentation', 1)
GO
/*
Resultado:
===============================================================================
DBCC SHOWCONTIG scanning 'Fragmentation' table...
Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 999
- Extents Scanned..............................: 127
- Extent Switches..............................: 998
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 12.51% [125:999]
- Logical Scan Fragmentation ..................: 49.95%
- Extent Scan Fragmentation ...................: 0.79%
- Avg. Bytes Free per Page.....................: 79.0
- Avg. Page Density (full).....................: 99.02%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
O output do DBCC SHOWCONTIG mostra claramente a quantidade de extent switches que
são feitos. Enquanto o ideal seria somente mudar 125 vezes (999 / 8), foram feitas 999
mudanças (igual ao número de páginas :-p).
*/
DBCC INDEXDEFRAG (7, 'Fragmentation', 1)
GO
/*
O INDEXDEFRAG somente manteve no mesmo local algumas páginas, fazendo a troca
de local entre 990 páginas.
Pages scanned: 992
Pages moved: 990
Pages deleted: 0
*/
DBCC SHOWCONTIG ('Fragmentation', 1)
GO
/*
Resultado:
===============================================================================
DBCC SHOWCONTIG scanning 'Fragmentation' table...
Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 999
- Extents Scanned..............................: 127
- Extent Switches..............................: 137
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 90.58% [125:138]
- Logical Scan Fragmentation ..................: 0.60%
- Extent Scan Fragmentation ...................: 0.79%
- Avg. Bytes Free per Page.....................: 79.0
- Avg. Page Density (full).....................: 99.02%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
O INDEXDEFRAG fez um ótimo trabalho de organização das páginas, mas ainda poderia ter sido
melhor, chegando mais perto do Scan Density de 99%.
*/
/*
Refazer todo o processo e ao invés de utilizar o INDEXDEFRAG, utilizar o DBREINDEX.
*/
DBCC DBREINDEX('Fragmentation', 'PK_Codigo', 0)
GO
DBCC SHOWCONTIG ('Fragmentation', 1)
GO
/*
Resultado:
===============================================================================
DBCC SHOWCONTIG scanning 'Fragmentation' table...
Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 999
- Extents Scanned..............................: 126
- Extent Switches..............................: 125
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.21% [125:126]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 79.0
- Avg. Page Density (full).....................: 99.02%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/