Page Splits/sec: Two Kinds of "Split"
dbo.Users — clustered index on Id (identity)
UPDATE
dbo.Users
SET
AboutMe = '…much longer…'
WHERE
Id = 12345;
INSERT INTO
dbo.Users (DisplayName, …)
VALUES
('brento', …);
-- gets the next identity value: Id 9,000,001
Perfmon · SQLServer:Access Methods
Page Splits/sec
0
1
2
+1
+1
Intermediate
Intermediate page 1:200
Id 1 → 1:300
Id 8,001 → 1:301
Id 12,345 → 1:845
Id 16,001 → 1:302
⋯
Id 8,999,001 → 1:305
Id 9,000,001 → 1:846
Leaf · data pages
Page 1:300
Id 1
Id 1,450
Id 2,900
Id 4,300
Id 5,750
Id 7,200
91% full
Page 1:845 · new
≈48% full · 3 rows moved in
Page 1:301
Id 8,001
Id 9,500
Id 11,000
Id 12,345
Id 13,800
Id 15,200
96% full
≈52% full
⋯
Page 1:846 · new
1 row · nothing moved in
Page 1:305 · last page
Id 8,999,001
Id 8,999,200
Id 8,999,400
Id 8,999,600
Id 8,999,800
Id 9,000,000
98% full
98% full · nothing moved
Id 9,000,001 · new row
Mid-page split: 3 rows moved to 1:845, both pages ≈ 50% full, every moved row written to the log
1:845 isn't next to 1:301 on disk — that's where fragmentation comes from
End-of-page split: nothing moved, 1:305 still 98% full, 1:846 holds only the new row
The index simply grew — yet Page Splits/sec counted it exactly the same
Page Splits/sec = bad splits + normal growth · isolate the bad ones: transaction_log XE, LOP_DELETE_SPLIT