Fala Pessoal,<\/p>\n
H\u00e1 um tempo atr\u00e1s, 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\u00e1rios artigos em ingl\u00eas, mas tamb\u00e9m encontrei um artigo em portugu\u00eas do Laerte J\u00fanior(Twitter<\/a>) que fala sobre o assunto: O que mais o transaction log pode fazer por mim.<\/a><\/p>\n Segue a query EXCEPCIONAL:<\/p>\n select\u00a0\u00a0 allocUnitName,COUNT(*) Resumidamente, Page Split \u00e9 uma opera\u00e7\u00e3o que acontece quando um registro \u00e9 inserido ou alterado (expandindo seu tamanho) em uma p\u00e1gina de dados e essa p\u00e1gina n\u00e3o possui espa\u00e7o livre suficiente para realizar essa opera\u00e7\u00e3o. Assim, uma p\u00e1gina \u00e9 dividida em duas para comportar as novas informa\u00e7\u00f5es, o que causa uma opera\u00e7\u00e3o custosa de disco (nosso maior gargalo no banco de dados).<\/p>\n Para tentar diminuir o Page Split do meu ambiente, criei uma rotina(que eu rodo somente quando necess\u00e1ria) que guarda um hist\u00f3rico das opera\u00e7\u00f5es de Page Splits realizadas nos meus \u00edndices.<\/p>\n Inicialmente criei duas tabelas em uma database que armazenar\u00e1 os hist\u00f3ricos: CREATE TABLE Historico_Page_Split( Em seguida, voc\u00ea deve se conectar em cada database que voc\u00ea deseja realizar o monitoramento e executar os comandos abaixo:<\/p>\n Inser\u00e7\u00e3o do menor LSN existente na tabela de controle do LSN para que n\u00e3o seja inseridas informa\u00e7\u00f5es duplicadas:<\/p>\n INSERT INTO Max_LSN_Log Cria\u00e7\u00e3o da procedure que armazenar\u00e1 o hist\u00f3rico. — Seleciona o \u00faltimo LSN registrado no Log insert into Historico_Page_Split(Nm_database,Nm_tabela,Nm_Indice,Nr_Page_Split) if @@rowcount > 0 –atualiza o LSN da database update Max_LSN_Log END<\/p>\n Tenho certeza que voc\u00ea realiza um Backup do Log de suas databases OLTP(eu rodo de 7 em 7 minutos para algumas DB’s), ent\u00e3o, agora \u00e9 s\u00f3 incluir a chamada dessa SP no Job que realiza o backup do Log das suas databases que ser\u00e3o logadas.\u00a0 Ela deve ser inclu\u00edda em um step antes da realiza\u00e7\u00e3o do Backup.<\/p>\n Exec stpCarga_Page_Split<\/p>\n Com a query abaixo podemos acompanhar o Page_Split dos \u00edndices de seu ambiente em um per\u00edodo espec\u00edfico:<\/p>\n SELECT Nm_Database,Nm_Tabela,Nm_Indice, sum(Nr_Page_Split) Total_Page_Split Com mais essa informa\u00e7\u00e3o em m\u00e3os, temos mais uma forma de analisar a famosa d\u00favida de qual \u00e9 o FILLFACTOR ideal para um \u00edndice. Diminuindo o FILLFACTOR de um \u00edndice voc\u00ea tamb\u00e9m diminui o PAGE SPLIT desse \u00edndice, em contra partida, tamb\u00e9m aumenta o espa\u00e7o em disco para armazenar o \u00edndice e consequentemente o espa\u00e7o utilizado em mem\u00f3ria, pois um n\u00famero maior de p\u00e1ginas ser\u00e1 carregada na mem\u00f3ria.<\/p>\n Concordo que n\u00e3o \u00e9 simples realizar essa an\u00e1lise, entretanto, caso n\u00e3o queira entrar nesse n\u00edvel de detalhe do SGBD, n\u00f3s DBA’s estamos a disposi\u00e7\u00e3o para resolver esse e muitos outros tipos de problemas. =)<\/p>\n Como nem tudo s\u00e3o flores, realizar a leitura do arquivo de log \u00e9 uma opera\u00e7\u00e3o MUITO MUITO custosa. Ent\u00e3o se seu ambiente j\u00e1 est\u00e1 com grandes problemas de performance, isso n\u00e3o \u00e9 indicado. Fa\u00e7a um teste com uma database com poucas altera\u00e7\u00f5es e depois passe\u00a0 para as outras databases caso seu ambiente n\u00e3o esteja sendo impactado. Isso fica por sua conta e risco.<\/p>\n No meu ambiente eu habilitei essa procedure durante menos de uma hora para cada database e identifiquei os \u00edndices que mais possu\u00edam PAGE SPLIT durante esse per\u00edodo.<\/p>\n Espero que possa te ajudar de alguma forma.<\/p>\n Script deste post: Artigo Page Split<\/a><\/p>\n Gostou desse Post?<\/b><\/p>\n
\nfrom\u00a0\u00a0\u00a0 ::fn_dblog(null, null)
\nwhere Operation = ‘LOP_DELETE_SPLIT’
\ngroup by allocUnitName
\norder by COUNT(*) desc<\/p>\n
\nCREATE TABLE Max_LSN_Log(
\nNm_Database VARCHAR(50),
\nUltimo_LSN VARCHAR(46))<\/p>\n
\nId_Historico_Page_Split INT IDENTITY(1,1),
\nNm_database VARCHAR(40),
\nNm_tabela VARCHAR(50),
\nNm_Indice VARCHAR(50),
\nDt_Referencia DATETIME DEFAULT(getdate()),
\nNr_Page_Split INT,
\nCONSTRAINT PK_Historico_Page_Split PRIMARY KEY(Id_Historico_Page_Split)
\n)<\/p>\n
\nselect db_name(db_id()), min([Current LSN])
\nFROM ::fn_dblog(null, null)
\nWHERE Operation = N’LOP_DELETE_SPLIT’
\nAND parsename(AllocUnitName,3) <> ‘sys’<\/p>\n
\nCREATE procedure stpCarga_Page_Split
\nAS
\nBEGIN
\ndeclare @Ultimo_LSN varchar(46)<\/p>\n
\nselect @Ultimo_LSN = Ultimo_LSN
\nfrom Max_LSN_Log
\nwhere Nm_database = db_name(db_id())<\/p>\n
\nselect db_name(db_id()),\u00a0 substring(replace(AllocUnitName,’dbo.’,”),1, CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) -1), –tabela
\nsubstring(replace(AllocUnitName,’dbo.’,”), CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”))+1,
\nlen( replace(AllocUnitName,’dbo.’,”) ) – CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) +1 ), — Indice,
\ncount([AllocUnitName]) [Splits]\nfrom ::fn_dblog(null, null)
\nwhere Operation = N’LOP_DELETE_SPLIT’
\nand parsename(AllocUnitName,3) <> ‘sys’
\nand [Current LSN] > @Ultimo_LSN\u00a0 — Para n\u00e3o contabilizar duas vezes
\ngroup by\u00a0 substring(replace(AllocUnitName,’dbo.’,”),1, CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) -1), –tabela
\nsubstring(replace(AllocUnitName,’dbo.’,”), CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”))+1,
\nlen( replace(AllocUnitName,’dbo.’,”) ) – CHARINDEX(‘.’,replace(AllocUnitName,’dbo.’,”)) +1 ) — Indice<\/p>\n
\nbegin
\nselect @Ultimo_LSN = max([Current LSN])
\nfrom ::fn_dblog(null, null)
\nwhere Operation = N’LOP_DELETE_SPLIT’
\nand parsename(AllocUnitName,3) <> ‘sys’
\nand ([Current LSN] > @Ultimo_LSN or @Ultimo_LSN is null)<\/p>\n
\nset Ultimo_LSN = @Ultimo_LSN
\nwhere Nm_database = db_name(db_id())
\nend<\/p>\n
\nFROM Historico_Page_Split
\nWHERE Dt_Referencia >= ‘20110810’ and Dt_Referencia < ‘20110811’
\nGROUP BY Nm_Database,Nm_Tabela,Nm_Indice
\nORDER BY Total_Page_Split DESC<\/p>\n