Page Splits Part 2: Identifying Page splits

Even though we know theoretically how page split occurs when the database system is on live identifying page splits is a different story altogether. We have little control over the data inserted or updated.  (There are mechanisms to control the clustered key inserted, but it is difficult to control the update)

There is no event in profiler to identify the page split.

Page splits could be identified through Performance Monitor (It is being called with different names:  Performance monitor, System Monitor and Performance – All refers to the same tool named PerfMon.exe ) It is part of control panel and placed under administrative tools.

PerfMon is a great tool to identify system usage such as CPU usage, Memory usage and Disk Queue.  It also used to read SQL Server Counters.  In that section there is an object named SQL Server: Access Methods the counter named “page splits/sec” resides there. It will show the page splits occurred.  When the figure increases from zero, we can conclude that page splits have occurred.

The same perf Mon numbers could be retrieved from a dynamic management view:

Select * from sys.dm_os_performance_counters

where object_name like 'SQLServer:Access Methods%'  and

      counter_name like 'page splits/sec%'

Please note that like operator is used because the object_name and counter_name are padded with extra space.  

The query returns four columns.  Out of which object_name and Counter_Name are self-explainers. Crtl_value refers to the number of splits occurred.  However, there is a major issue with this number:  The figure is cumulative of all databases and objects in that server.  It does not have any breakdown.  From this counter, there is no way of identifying the database, table or index where the page split has occurred.

However, by analysing the events took place (what tables got inserted) and by checking fragmentation, we may be able to make an intelligent guess on where page splits have occurred.

The undocumented system function fn_dblog() provides some valuable information on page splits.  Before proceeding a few words about this function. Even though this function is undocumented now, during the early days of SQL Server (up to SQL Server 6.5), DBCC Log was used to offer similar functionality; it was a documented command.  It became an undocumented command and discontinued from SQL server 2005.  Instead SQL Server 2005 implemented fn_dblog(). With the release of SQL Server 2008, there were few minor changes in the function. So I assume Microsoft will continue to use this for its internal use, and we need to check the future of the functionality periodically. One of the major reason behind having this continuously as undocumented is it gives the freedom to Microsoft to implement new log format if needed during any release without worrying about backward compatibility.  This points shows the risk in using this function, especially on built in code.

Enough preaching.  Let’s comeback to code.    J

Select COUNT(1) AS NumberOfSplits, AllocUnitName , Context

From fn_dblog(NULL,NULL)

Where operation = 'LOP_DELETE_SPLIT'

Group By AllocUnitName, Context

Order by NumberOfSplits desc

Fn_dblog() function accepts two parameters: start of the LSN number and end of the LSN number. The functions searches the transaction log for certain actions within the range specified and returns the information.  

As you understand, scanning the entire transaction log of every database is not only tedious but also consumes additional overhead to the transaction log.  It slows down the DML operations too.  Thus it is not recommended to use in production environment.  Even if we need to, we need use the start and end of LSN numbers so that we can limit the amount of transaction log to be scanned.  

Unfortunately again, this function could be used within a database context.  Thus this cannot be executed against any database, but could be used only against the current database.  I tried the function with “dbname.sys.” prefix, but it didn’t help. SQL Server did not throw any errors, but the prefix was simply ignored.   

With the inclusion of extended events, we have slightly better control over page splits. 

References: & Further Reading:
http://technet.microsoft.com/en-us/library/cc917672.aspx
This is a white paper published by Microsoft on best practices, a portion talks about page splits and performance.
http://danieladeniji.wordpress.com/2011/01/29/microsoft-sqlserver-page-splits/
Another excellent article from Brad McGehee on performance counters.

http://strictlysql.blogspot.com/2009/10/identifying-page-splits.html

A good article if you need to learn about page splits.

-- to be continued --