Fala Pessoal,

Há um tempo atrás, dando uma vasculhada na internet a procura de como monitorar Page Split no meu Banco de Dados(SQL SERVER 2005), encontrei uma query EXCEPCIONAL em vários artigos em inglês, mas também encontrei um artigo em português do Laerte Júnior(Twitter) que fala sobre o assunto: O que mais o transaction log pode fazer por mim.

Segue a query EXCEPCIONAL:

select   allocUnitName,COUNT(*)
from    ::fn_dblog(null, null)
where Operation = ‘LOP_DELETE_SPLIT’
group by allocUnitName
order by COUNT(*) desc

Resumidamente, Page Split é uma operação que acontece quando um registro é inserido ou alterado (expandindo seu tamanho) em uma página de dados e essa página não possui espaço livre suficiente para realizar essa operação. Assim, uma página é dividida em duas para comportar as novas informações, o que causa uma operação custosa de disco (nosso maior gargalo no banco de dados).

Para tentar diminuir o Page Split do meu ambiente, criei uma rotina(que eu rodo somente quando necessária) que guarda um histórico das operações de Page Splits realizadas nos meus índices.

Inicialmente criei duas tabelas em uma database que armazenará os históricos:
CREATE TABLE Max_LSN_Log(
Nm_Database VARCHAR(50),
Ultimo_LSN VARCHAR(46))

CREATE TABLE Historico_Page_Split(
Id_Historico_Page_Split INT IDENTITY(1,1),
Nm_database VARCHAR(40),
Nm_tabela VARCHAR(50),
Nm_Indice VARCHAR(50),
Dt_Referencia DATETIME DEFAULT(getdate()),
Nr_Page_Split INT,
CONSTRAINT PK_Historico_Page_Split PRIMARY KEY(Id_Historico_Page_Split)
)

Em seguida, você deve se conectar em cada database que você deseja realizar o monitoramento e executar os comandos abaixo:

Inserção do menor LSN existente na tabela de controle do LSN para que não seja inseridas informações duplicadas:

INSERT INTO Max_LSN_Log
select db_name(db_id()), min([Current LSN])
FROM ::fn_dblog(null, null)
WHERE Operation = N’LOP_DELETE_SPLIT’
AND parsename(AllocUnitName,3) <> ‘sys’

Criação da procedure que armazenará o histórico.
CREATE procedure stpCarga_Page_Split
AS
BEGIN
declare @Ultimo_LSN varchar(46)

— Seleciona o último LSN registrado no Log
select @Ultimo_LSN = Ultimo_LSN
from Max_LSN_Log
where Nm_database = db_name(db_id())

insert into Historico_Page_Split(Nm_database,Nm_tabela,Nm_Indice,Nr_Page_Split)
select db_name(db_id()),  substring(replace(AllocUnitName,’dbo.’,”),1, CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) -1), –tabela
substring(replace(AllocUnitName,’dbo.’,”), CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”))+1,
len( replace(AllocUnitName,’dbo.’,”) ) – CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) +1 ), — Indice,
count([AllocUnitName]) [Splits] from ::fn_dblog(null, null)
where Operation = N’LOP_DELETE_SPLIT’
and parsename(AllocUnitName,3) <> ‘sys’
and [Current LSN] > @Ultimo_LSN  — Para não contabilizar duas vezes
group by  substring(replace(AllocUnitName,’dbo.’,”),1, CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) -1), –tabela
substring(replace(AllocUnitName,’dbo.’,”), CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”))+1,
len( replace(AllocUnitName,’dbo.’,”) ) – CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) +1 ) — Indice

if @@rowcount > 0 –atualiza o LSN da database
begin
select @Ultimo_LSN = max([Current LSN])
from ::fn_dblog(null, null)
where Operation = N’LOP_DELETE_SPLIT’
and parsename(AllocUnitName,3) <> ‘sys’
and ([Current LSN] > @Ultimo_LSN or @Ultimo_LSN is null)

update Max_LSN_Log
set Ultimo_LSN = @Ultimo_LSN
where Nm_database = db_name(db_id())
end

END

Tenho certeza que você realiza um Backup do Log de suas databases OLTP(eu rodo de 7 em 7 minutos para algumas DB’s), então, agora é só incluir a chamada dessa SP no Job que realiza o backup do Log das suas databases que serão logadas.  Ela deve ser incluída em um step antes da realização do Backup.

Exec stpCarga_Page_Split

Com a query abaixo podemos acompanhar o Page_Split dos índices de seu ambiente em um período específico:

SELECT Nm_Database,Nm_Tabela,Nm_Indice, sum(Nr_Page_Split) Total_Page_Split
FROM Historico_Page_Split
WHERE Dt_Referencia >= ‘20110810’ and Dt_Referencia < ‘20110811’
GROUP BY Nm_Database,Nm_Tabela,Nm_Indice
ORDER BY Total_Page_Split DESC

Com mais essa informação em mãos, temos mais uma forma de analisar a famosa dúvida de qual é o FILLFACTOR ideal para um índice. Diminuindo o FILLFACTOR de um índice você também diminui o PAGE SPLIT desse índice, em contra partida, também aumenta o espaço em disco para armazenar o índice e consequentemente o espaço utilizado em memória, pois um número maior de páginas será carregada na memória.

Concordo que não é simples realizar essa análise, entretanto, caso não queira entrar nesse nível de detalhe do SGBD, nós DBA’s estamos a disposição para resolver esse e muitos outros tipos de problemas. =)

Como nem tudo são flores, realizar a leitura do arquivo de log é uma operação MUITO MUITO custosa. Então se seu ambiente já está com grandes problemas de performance, isso não é indicado. Faça um teste com uma database com poucas alterações e depois passe  para as outras databases caso seu ambiente não esteja sendo impactado. Isso fica por sua conta e risco.

No meu ambiente eu habilitei essa procedure durante menos de uma hora para cada database e identifiquei os índices que mais possuíam PAGE SPLIT durante esse período.

Espero que possa te ajudar de alguma forma.

Script deste post: Artigo Page Split

Gostou desse Post?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006