brentozar.com
Database Animations: Stop Using Page Splits to Justify Lowering Fill Factor.
You're looking at page split numbers in a monitoring tool or Perfmon, and you've heard that page splits are bad, so you're lowering fill factor, expecting your page splits to go down. You're monitoring the wrong number. To prove it, let's check the page splits counter, add 10,000 pages to a table, and then check it again: DECLARE @StartingPageSplits BIGINT = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Splits/sec%'); DROP TABLE IF EXISTS dbo.PageSplitTest; CREATE TABLE dbo.PageSplitTest (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, BigData CHAR(8000)); INSERT INTO dbo.PageSplitTest(BigData) SELECT 'BigData' FROM generate_series(1, 10000); SELECT cntr_value - @StartingPageSplits AS PageSplits FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page Splits/sec%';
