Clustered composite key high fragmentation and fill factor

Question

I've recently made a table to hold the language preferences of my users as follow:

CREATE TABLE [dbo].[systemUserLangPreference](
  [systemUserID] [int] NOT NULL,
  [langID] [int] NOT NULL,
  [preferredOrder] [int] NOT NULL,
  [createdBy] [int] NOT NULL,
  [createdOn] [datetime] NOT NULL,
  [lastActionBy] [int] NOT NULL,
  [lastActionOn] [datetime] NOT NULL,
CONSTRAINT [PK_systemUserLangPreference] PRIMARY KEY CLUSTERED 
([systemUserID] ASC, [langID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

There's less than 1,000 rows in there now so performance isn't much of an issue at this time however when I run an index fragmentation report this index and more of my compound keys always comes up as high fragmentation no matter what I do.

Using

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()

This one index will return 80% avg fragmentation even moment after I rebuild it. I've tried playing with the fill factor with the following results:

Fill 100%; Fragmentation 75%
Fill 80%; Fragmentation 80%
Fill 50%; Fragmentation 62.5%
Fill 10%; Fragmentation 19.44%
Fill 1%; Fragmentation 1.90%

So it seems the lower I go the better? I'm not sure I'm interpreting this right. Since the table is mostly reads (insert on new user and I guess when they learn a language) wouldn't a higher fill factor be better? Why does it lead to immediate fragmentation and is it a bad thing?

Answer 1

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control. The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential. Only after an index has more than eight pages will it be allocated additional pages from uniform extents.

At fewer than 1,000 rows, your clustered index would be well under 64K and entirely allocated out of mixed extents (unless you start forcing the issue by adjusting the fill factor, as you have seen).

You can get an idea of how this clustered index's pages are allocated by issuing this command:

DBCC EXTENTINFO( DB_NAME, systemUserLangPreference, PK_systemUserLangPreference)

Once the index is large enough that it is allocated more than eight pages, you can ensure that they are all allocated out of uniform extents by dropping the index and then creating it again. Again, though, for such a small table, fragmentation is not going to have a performance impact, and you probably shouldn't try to solve it by fiddling with fill factor.

Lowering the fill factor might actually have detrimental results, as fewer records fit on each page, so more pages needed to be loaded into memory. Here's more on why to be careful with the fill factor.

For more details on pages and extents, see this article.