Clustered Index Fragmentation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Clustered Index Fragmentation

Clustered Index Fragmentation

Rate Topic

Display Mode

Topic Options

Author

Message

InvoluntaryDBA

Posted Wednesday, February 24, 2010 10:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 30, 2012 7:40 AM
Points: 2, Visits: 16

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.

Post #872069

Jeffrey Williams 3188

Posted Wednesday, February 24, 2010 11:08 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:21 AM
Points: 4,317, Visits: 9,216

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 opportunites brilliantly disguised as insurmountable obstacles.

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

Post #872088

Tim Weintz

Posted Wednesday, February 24, 2010 2:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 02, 2012 3:25 PM
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.

Post #872320

InvoluntaryDBA

Posted Wednesday, February 24, 2010 4:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 30, 2012 7:40 AM
Points: 2, Visits: 16

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?

Post #872367

Jeffrey Williams 3188

Posted Wednesday, February 24, 2010 4:24 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:21 AM
Points: 4,317, Visits: 9,216

InvoluntaryDBA (2/24/2010)

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 opportunites brilliantly disguised as insurmountable obstacles.

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

Post #872375

Henrico Bekker

Posted Wednesday, February 24, 2010 10:27 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 1,871, Visits: 2,692

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.

Post #872477

« Prev Topic | Next Topic »

Permissions