11

I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. Is there a way to prevent this by tweaking the settings?

CREATE TABLE HighScore(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user] [int] NULL,
    [player] [int] NULL,
    [round] [tinyint] NULL,
    [group] [int] NULL,
    [rank] [int] NULL,
    [delta] [int] NULL,
    [roundpoints] [int] NULL,
    [totalpoints] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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]


CREATE NONCLUSTERED INDEX [HighScore_RoundGroup_Nidx] ON .[HighScore] 
(
    [round] ASC,
    [group] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
  • 1
    Silly question, but to cover all basis - are you rebuilding/reorganizing every day? – JHFB Jun 14 '12 at 15:22
  • without TABLE DDL anyone posting will guess. Are you using GUID as primary key? – SQL Learner Jun 14 '12 at 15:29
  • I am rebuilding every day right now but I wonder if I can prevent this from having to happen every day since I can anticipate pretty well on how the data evolves. – olle Jun 14 '12 at 15:30
  • 1
    How are you determining fragmentation level? I wouldn't expect much logical fragmentation at all for the clustered index. Some internal fragmentation though, you don't need FILLFACTOR = 80 there. It will just waste space. All the columns are fixed length so a row can't expand on update and inserts can't happen in the middle of the table. 99% seems unexpectedly high for the other index too. How many pages are there in each index? – Martin Smith Jun 14 '12 at 15:39
  • 99% after rebuilding each day would really be going some, can you show your sys.dm_db_index_physical_stats output? – Martin Smith Jun 15 '12 at 7:35
3

I think you should try higher FILLFACTOR settings on HighScore_RoundGroup_Nidx (e.g. 50 or 40). You can set FILLFACTOR to 0 or 100 for the PRIMARY KEY because it shouldn't fragment If it still does, FILLFACTOR does not help because the reason is that newly allocated pages interleave with other newly allocated pages. This is a well-known SQL Server issue. You could move this index into its own filegroup which would stop this problem.

2

Maybe you think you are rebuilding but the index doesn't get rebuilt because the index isn't large enough.

Have a look at this question Why index REBUILD does not reduce index fragmentatation?

Did you check the fragmentation after rebuild? Is it actually defragmented.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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