Spending quite a bit of time monitoring for Performance lately. Reviewed a few things and liked the way they turned out.
But, because of some lagging Stored Procedure\SQL that re-calibrates the current Sequence Number which is used in an Indexed View; as indexed view, do not do max(sequenceNbr), I started to wonder how much Page Splits was actually occurring as I re-sequenced this big time-sensitive table.
Unfortunately, the following facilities will not allow me to track page splits:
- Microsoft SQL Profiler
- Microsoft Query Plan
The following tools also fall short, for one or more reasons:
- Windows Performance Monitor – “SQL Server:Access Methods\Page Splits/sec” – as it aggregates the entire page\split and does not provide granular data as to which objects\indexes are most affected
Googling to find a good path landed me where I needed to be:
Basically, the query will query the current log and look for where Operation matches LOP_DELETE_SPLIT and aggregates the records for each index:
MS SQL Server v2005+
select tblDBLog.Operation , tblDBLog.AllocUnitName , [typeLiteral] = max(tblSI.type_desc) , fill_factor = max(tblSI.fill_factor) , NumberofIncidents = COUNT(*) from ::fn_dblog(null, null) tblDBLog left outer join sys.indexes tblSI on tblDBLog.AllocUnitName = + object_schema_name(tblSI.object_id) + '.' + object_name(tblSI.object_id) + '.' + tblSI.name where tblDBLog.Operation = N'LOP_DELETE_SPLIT' group by tblDBLog.Operation , tblDBLog.AllocUnitName order by COUNT(*) desc
MS SQL Server v2000
select [Object Name], [Index Name], count([Current LSN]) from ::fn_dblog(null, null) where Operation = N'LOP_DELETE_SPLIT' group by [Object Name], [Index Name]
If you find that you are experiencing high page\splits then you may want to consider the following tracks:
- Review your Clustered Indexes and ensure that the columns referenced are pretty static. If the Clustered Index Columns are changing, older index pages are broken in half and new pages are touched to make room for the re-stamped values
- Change the Index Fill Factors — Reduce the index fill factor —If your SQL Instance edition is Enterprise or greater this can be an online Operation.
alter index idx_ranking on dbo.employee rebuild with ( fillfactor = 20 , sort_in_tempdb = on , statistics_norecompute=off , online=on )
- Using ::fn_dblog() to identify which indexes are experiencing page splits (Greg Linwood)
- Subtle change to ::fn_dblog in SQL 2008 (SQL Fascination \ WordPress)
- Index Fill Factor & Performance Considerations (Mike Hodgson)