Í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

PageSplits.sql

Comments (0)

Skip to main content