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 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
share|improve this question
    
What is the table definition (including indexes)? –  Martin Smith Jun 14 '12 at 15:20
    
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
show 1 more comment

1 Answer 1

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.

share|improve this answer
add comment

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.