As a SQL Server DBA or developer, it is important to monitor and optimize the performance of your database. One key aspect to consider is the occurrence of page splits, which can impact query performance and increase IO operations.

Traditionally, the most popular method to measure page splits is by using Performance Monitor (PerfMon), a Windows tool. However, some users find it inconvenient to open a separate tool to monitor these values. In this blog post, we will explore an alternative method to measure page splits using T-SQL scripts.

A Quick Note on Page Splits

Page splits occur when a data page becomes full and a new page is needed to accommodate additional data. This is a common operation in SQL Server. However, during insert or update operations, if rows are inserted or updated in the middle of a page, it can lead to page splits, which can negatively impact performance.

Page splits can slow down queries and increase the amount of IO operations required to complete data selection queries. It is crucial to monitor this metric over time and make appropriate adjustments to your SQL Server configurations. Additionally, you can make necessary adjustments at the table level to reduce the number of page splits.

Methods to Access the Page Splits Counter

There are two methods to access the page splits counter using T-SQL:

Method 1: sys.sysperfinfo

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

This method retrieves the counter value from the sys.sysperfinfo system view. It filters the results based on the counter name and object name to specifically target the page splits counter.

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%'

This method utilizes the sys.dm_os_performance_counters dynamic management view, which is recommended for accessing performance counters in SQL Server. It provides more detailed information about the counter, including the object name, counter name, instance name, counter value, and counter type.

Both Method 1 and Method 2 will yield the same results. You can modify the WHERE clause of these queries to retrieve various other counters as well.

Note: If you are familiar with Extended Events, it is recommended to measure page splits using extended events. Extended events provide additional details that can enhance the understanding and analysis of the page splits counter.

By utilizing these T-SQL scripts, you can easily measure the page splits counter without the need for external tools like PerfMon. This allows for a more streamlined monitoring process and enables you to quickly identify and address any performance issues related to page splits in your SQL Server environment.

Remember, optimizing your database’s performance is an ongoing process, and monitoring key metrics like page splits is essential for maintaining a high-performing SQL Server environment.