Why should you care?

Random inserts into the middle of an index (GUID keys) cause splits and fragmentation — fill factor can reduce how often that hurts.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders
REBUILD WITH (FILLFACTOR = 90);
SELECT i.name, i.fill_factor
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID(N'dbo.Orders') AND i.name = N'IX_Orders_CustomerId';

What happened?

• REBUILD applies fill factor 90 to that index.
• sys.indexes confirms the setting.
• Sequential IDENTITY keys often keep fill factor near 100.

Practice next

1. Rebuild one NC index with FILLFACTOR = 90.
2. Confirm fill_factor in sys.indexes.
3. Note: fill factor applies on rebuild, not magically to new pages forever without maintenance.
4. Rebuild with FILLFACTOR = 100 for a sequential key index.
5. Compare fragmentation before/after heavy inserts (lab only).

Remember

Fill factor reserves page free space. Apply on CREATE/REBUILD. Tune for insert patterns, not superstition.

GUID-heavy index maintenance

A legacy DataVerse table clustered on NEWID().

Outcome: Ops uses lower fill factor + scheduled rebuilds until they migrate keys.