Índices clusterizados, page splits e mais... (parte I)
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