Page Splits per-Second Over TimePage splits occur when data is expanded to a size which will no longer fit into the 8KB page. Page splits are a normal part of SQL Server's operation, but page splits are I/O and lock intensive - so too many page splits can be indicitave of a design flaw or misconfiguration. For this reason, taking a baseline of your SQL Server's page splits is important - especially before making any changes which could increase or
decrease this measure.
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'
Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.