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.
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?