Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clustered Index Fragmentation Expand / Collapse
Author
Message
Posted Wednesday, February 24, 2010 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 24, 2010 11:08 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:48 PM
Points: 4,388, Visits: 9,506
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
Posted Wednesday, February 24, 2010 2:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 2, 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
Posted Wednesday, February 24, 2010 4:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 24, 2010 4:24 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:48 PM
Points: 4,388, Visits: 9,506
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #872375
Posted Wednesday, February 24, 2010 10:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 1,904, Visits: 2,825
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 »

Add to briefcase

Permissions Expand / Collapse