select allocUnitName,COUNT(*) from ::fn_dblog(null, null) where Operation = 'LOP_DELETE_SPLIT' group by allocUnitName order by COUNT(*) desc 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) ) 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' 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 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