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: http://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


luticm79@hotmail.com


 


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


*/

Fragmentation – extent switches.sql

Comments (0)

Skip to main content