/******************************************************************************

	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.

*/