Custom Metric - Page Splits

darichar

Description

When new data is being added to a page, and that page lacks the space to store it, SQL Server will create two new pages from the one page. If that happens too often performance can suffer. This custom metric can be used in Ignite to collect high level page splits data.

Metric Definition

To create the custom metric, click on Options > Custom Resource Metrics and configure the metric similar to this:

Database Versions: 9.0 and above

Display Name: Page Splits

Description: <add your own description>

Category: <add to whatevfer category you like or create a new one>

Units: page splits

Metric Type: Delta

Frequency: 60

Timeout: 20

SQL Statement:

select cntr_value

from sys.dm_os_performance_counters

where counter_name = 'page splits/sec'

rick.sellers

I created my own Custom Metric on this before I found this one.  The difference is that I used a Metric Type of "Rate", which gives me the Page Splits/sec on the SQL Server instance and I run it on a 60-second interval.  The SQL I used is shown below:

SELECT cntr_value

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Access Methods' AND counter_name = 'Page Splits/Sec'