Page Splits Per Second

Page splits are a sign of index fragmentation and insert pressure. This script reads the Page Splits/sec performance counter, waits ten minutes (adjust the WAITFOR to taste), reads it again, and prints the total split count and the average per second over the window - a simple baseline for before/after comparisons.

DECLARE @BeforeCount Decimal(16, 2)
DECLARE @AfterCount Decimal(16, 2)
DECLARE @BeforeTime DateTime
DECLARE @AfterTime DateTime

SELECT @BeforeCount = cntr_value, @BeforeTime = GetDate()
FROM master.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access Methods%'

--Change this to as many hours:minutes:seconds you want to average out.
WAITFOR DELAY '00:10:00'

SELECT @AfterCount = cntr_value, @AfterTime = GetDate()
FROM master.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access Methods%'

PRINT 'Total splits: ' + Convert(VarChar(50), Convert(Decimal(18, 0), (@AfterCount - @BeforeCount)))
	+ ' over: ' + Convert(VarChar(50), datediff(s, @BeforeTime, @AfterTime)) + ' seconds = '
	+ Convert(VarChar(50), Convert(Decimal(16, 2),
	((@AfterCount - @BeforeCount) / datediff(s, @BeforeTime, @AfterTime)))) + '/s'