SQL SERVER – How to Measure Page Splits Counter Value via T-SQL?

Question: How to Measure Page Splits Counter Value via T-SQL?

Answer: Recently I was asked this question while I was discussing the most important counters to keep an eye on during my SQL Server Performance Tuning Consultancy. Well, the most popular method is to use Performance Monitor (PerfMon) which is Windows Tool. However, many DBA and Developer find it a bit inconvenient as they have to open a separate tool to measure these values.

Honestly, I have seen quite a few DBAs who claim they have many years of experience of maintaining the database has no idea how to measure any of the counters related to SQL Server in PerfMon. Well, here is another alternative way which users who are not familiar with PerfMon can use it.

Here we will try to measure Page Splits counter via T-SQL scripts.

A Quick Note on Page Split

When the data page gets full with data and a new page is needed to accommodate more data, page split occurs and it is a very common operation. However, during Insert or Update there are cases when rows are updated/inserted in middle of the page and that creates the page split which can be potentially bad, leading to poor performance.

Page splits can slow down queries and increase the amount of IOs needed to complete our data selection queries. It is very important that you keep eyes on this setting over the time and make appropriate adjustments in your SQL Server Configurations. You can also make necessary adjustment at table level to reduce the number of the page splits.

This is a very big topic and it is out of the scope of this blog post. I usually discuss about this in details about during SQL Server Performance Tuning Practical Workshop (3-4 Hours) .

Methods to Access Counter

Method 1: sys.sysperfinfo

SELECT cntr_value
FROM sys.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'
GO

Method 2: sys.dm_os_performance_counters(Recommended)

SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'
GO

Method 1 and Method 2 both will show exactly the same results. You can change the WHERE clause of this query and see various different counters as well.

Note: If you are familiar with Extended Events, I strongly suggest that you measure Page Split with extended events as along with the count, they also provide quite a few interesting details which you can use it along with this counter make this counter more meaningful.

Reference: Pinal Dave (http://blog.SQLAuthority.com)