February 24, 2010 at 10:47 am
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.
February 24, 2010 at 11:08 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2010 at 2:58 pm
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.
February 24, 2010 at 4:07 pm
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?
February 24, 2010 at 4:24 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2010 at 10:27 pm
Read up on Gail Shaw's blog, lots of info and help on all types of Indexes.
She's a ninja when it comes to Indexes.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply