Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered Index Fragmentation


Clustered Index Fragmentation

Author
Message
InvoluntaryDBA
InvoluntaryDBA
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
We have a Clustered Index on a auto incrementing key. Yet the index gets fragmented heavily. Can somebody explain how a clustered index on a auto incrementing key can get fragmented at all.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4461 Visits: 9829
I find that this is one of the most mis-understood concepts in SQL Server. The belief is that because you have an auto-incrementing key, you will never have fragmentation in the clustered index. Of course, as you have found out - that is not true.

Why? Very simple if you take the time to understand what fragmentation is in the first place. Fragmentation is simply out of order pages in the b-tree which are caused by page splits. When you try to add data to a page that does not have enough room, SQL Server performs a page split creating a new page. That new page is now out of order and you have fragmentation.

Data will get added to a page through an insert or update operation. If you modify a VARCHAR column and add data, this will increase the amount of data on that page. If there is not enough room on the page to add that data, SQL Server will create a new page and move the data.

So, in short - the answer to your question is that your updates to the data are changing the size of that data causing page splits and fragmenting the index. If that fragmentation is happening too often, you need to change the fillfactor to a lower value to account for the update activity.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Tim Weintz
Tim Weintz
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 147
Yes, changing the fillfactor will help. But I would make an argument that a clustered index probably shouldn't be used on an identity field at all. There are few exceptions, of course, but in general I think they cause more harm than good.
InvoluntaryDBA
InvoluntaryDBA
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
I guess I missed some detail in my post. This table is a history table we created to track changes to data. So data never gets updated, only inserted into this table. Also it is an incrementing column not auto incrementing as I intially wrote. The column is a crud datetime column which tracks the date and time the change was made.

Given these parameters would fragmentation still occur?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4461 Visits: 9829
InvoluntaryDBA (2/24/2010)
I guess I missed some detail in my post. This table is a history table we created to track changes to data. So data never gets updated, only inserted into this table. Also it is an incrementing column not auto incrementing as I intially wrote. The column is a crud datetime column which tracks the date and time the change was made.

Given these parameters would fragmentation still occur?



Yes, it could still occur - all it takes is multiple processes trying to insert into the same page. This could occur in many different scenarios. One is having multiple processes inserting data - if process A inserts a row with a specific datetime value, process B comes through and inserts a rows with a datetime that is prior to the one inserted by process A - something has to be moved.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Henrico Bekker
Henrico Bekker
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 3208
Read up on Gail Shaw's blog, lots of info and help on all types of Indexes.
www.sqlinthewild.co.za

She's a ninja when it comes to Indexes.

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search