Microsoft – SQLServer – Page Splits

Background

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:

  1. Microsoft SQL Profiler
  2. Microsoft Query Plan

The following tools also fall short, for one or more reasons:

  1. 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

Code

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:

  1. 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
  2. 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
    )
    

References:

  1. Using ::fn_dblog() to identify which indexes are experiencing page splits (Greg Linwood)
    http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/11/27/37.aspx
  2. Subtle change to ::fn_dblog in SQL 2008 (SQL Fascination \ WordPress)
    http://sqlfascination.com/2010/07/15/subtle-change-to-fn_dblog-in-sql-2008/
  3. Index Fill Factor & Performance Considerations (Mike Hodgson)
    http://sqlnerd.blogspot.com/2005/06/index-fill-factor-performance.html

2 thoughts on “Microsoft – SQLServer – Page Splits

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s