Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
How many rows do you expect will be in the table over time? For a really small table, fragmentation is irrelevant. –  Jon Seigel Apr 15 at 13:41
    
Nothing major - probably never more than 5,000. I guess under a certain amount sql will just not use the index anyway. Still I am kind of puzzled by the fragmentation I am getting. Shouldn't it be closer to 0 after a rebuild? –  jfrobishow Apr 15 at 13:53

1 Answer 1

up vote 4 down vote accepted

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

enter image description here

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.

share|improve this answer
    
Couldn't have asked for a clearer explanation. –  jfrobishow Apr 15 at 16:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.