Estou fazendo um pequeno teste para verificar alguns detalhes do page split, e resolvi colocar o script aqui. Ele mostra algumas informações de alocação de páginas, page split, lista encadeada do índice clusterizado e informações de fragmentação.

Detalhe: o que eu pretendo verificar vai ficar para a segunda parte deste script, pois como estava ficando muito grande eu resolvi dividir o trem todo.

 

***************************************

 

/*

          0) Cria o banco de dados Inside

*/

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

 

/*

          1 - PRIMEIRA ETAPA

          =============================================

 

          Demonstrar que o índice clusterizado mantém os registros ordenados de acordo com a sua chave e que dependendo da forma que os registros são inseridos, isto pode causar um alto número de page splits, gerando uma fragmentação desnecessária na tabela.

 

*/

 

/*

          1.1) Cria nova tabela para verificar page splits

*/

 

USE Inside

go

 

IF EXISTS (SELECT [Id] FROM SysObjects WHERE Name = 'PageSplit' and Xtype = 'U')

BEGIN

          DROP TABLE PageSplit

END

 

CREATE TABLE PageSplit (

          Code int NOT NULL,

          Column2 char(3950) NOT NULL)

 

CREATE UNIQUE CLUSTERED INDEX PageSplit_ind on PageSplit(Code)

GO

 

/*

          1.2) Os próximos passos vão mostrar a inserção de 4 registros, onde veremos um page split e analisaremos o padrão de alocação do SQL Server.

*/

 

INSERT INTO PageSplit VALUES(1, 'SQL Server')

GO

 

--       2 páginas, uma é a raiz do índice clusterizado e a outra é a primeira página do nível folha.

DBCC ExtentInfo (Inside, 'PageSplit', 1)

GO

 

--       Inside database id = 7

SELECT * FROM Master..sysdatabases

GO

 

DBCC TRACEON (3604)

 

--       Index page (m_type = 2)

DBCC PAGE (7, 1, 15, 2)

--       Data page (m_type = 1)

DBCC PAGE (7, 1, 28, 3)

 

INSERT INTO PageSplit VALUES(99, 'SQL Server')

go

 

-- 2 páginas, uma é a raiz do índice clusterizado e a outra é a primeira página do nível folha.

DBCC ExtentInfo (Inside, 'PageSplit', 1)

-- 2 registros (1 e 99)

DBCC PAGE (7, 1, 28, 3)

GO

 

-- Até o momento, nada de fragmentação lógica

DBCC SHOWCONTIG ('PageSplit', 1)

GO

 

/*

          Esta inserção causa um page split, pois a página 28 não possui espaço para conter os 3 registros.

          Como o índice clusterizado deve manter os registros no nível folha ordenados de acordo com suas chaves (no nosso caso somente o campo Code), o registro 99 será colocado em uma nova página (29) enquanto os registros 1 e 2 ficarão na página original (28).

*/

INSERT INTO PageSplit VALUES(2, 'SQL Server')

GO

 

/*

          3 páginas, uma é a raiz do índice clusterizado. As outras duas pertencem ao nível folha.

          1 página com registros 1 e 2

          1 página com registro 99

*/

DBCC ExtentInfo (Inside, 'PageSplit', 1)

GO

 

-- Registros 1 e 2

DBCC PAGE (7, 1, 28, 3)

-- Registro 99

DBCC PAGE (7, 1, 29, 3)

GO

 

-- Até o momento, nada de fragmentação lógica.

DBCC SHOWCONTIG ('PageSplit', 1)

GO

 

/*

          Ao invés de colocar o registro na página 29 (que continha espaço para suportar uma inserção), o SQL Server preferiu colocar o registro em uma nova página.

*/

INSERT INTO PageSplit VALUES(98, 'SQL Server')

GO

 

/*

          3 páginas, uma é a raiz do índice clusterizado. As outras duas pertencem ao nível folha.

          1 página com registros 1 e 2

          1 página com registro 99

          1 página com registro 98

*/

DBCC ExtentInfo (Inside, 'PageSplit', 1)

GO

 

/*

          Registros 1 e 2

          m_prevPage = (0:0) (Está é a primeira página do índice clusterizado, então não existe prevPage)

          m_nextPage = (1:30)  (refere-se ao registro 98, que está em uma página fisicamente posterior que

                   a página do registro 99)

*/

DBCC PAGE (7, 1, 28, 3)

GO

 

/*

          Registro 98

 

          m_prevPage = (1:28) (Refere-se a página com os registros 1 e 2)

          m_nextPage = (1:29) (refere-se ao registro 99, que fisicamente está em uma página anterior)

*/

DBCC PAGE (7, 1, 30, 3)

GO

 

/*

          Registro 99

 

          m_prevPage = (1:30) (Refere-se a página com o registro 98)

          m_nextPage = (0:0) (Esta é a última página do índice clusterizado, pois não existe nextPage)

*/

DBCC PAGE (7, 1, 29, 3)

GO

 

/*

          Como a ordenação lógica das páginas está diferente da ordenação física, temos uma fragmentação lógica.

*/

DBCC SHOWCONTIG ('PageSplit', 1)

GO

 

TRUNCATE TABLE PageSplit

GO

 

/*

          1.3) Coloque no performance monitor o contador Page Splits/sec e execute o script abaixo.

          Como são feitas inserções no início e no fim da tabela, vários page splits são necessários para manter o índice clusterizado ordenado.

*/

 

SET NOCOUNT ON

 

DECLARE @contador INT

SET @contador = 1

WHILE (@contador < 50)

BEGIN

          INSERT INTO PageSplit VALUES(@contador, 'SQL Server')

          INSERT INTO PageSplit VALUES(100 - @contador, 'SQL Server')

          SET @contador = @contador + 1

END

 

SET NOCOUNT OFF

GO

 

/*

          Muitos page splits por segundo podem degradar a performance das operações de inserção e atualização dos registros, portanto deve-se prestar atenção em dois pontos:

 

          - Fillfactor do seus índices.

          - Ordenação do índice clusterizado.

 

          Infelizmente não existe uma receita de bolo de como configurar suas tabelas, mas nada como conhecer a forma como o SQL Server trabalha para conseguir um melhor desempenho.

*/

 

***************************************

 

[]s

LUTI