SQL Server index fragmentation is high after rebuilt

Question

SQL Server index fragmentation is high after rebuilt

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Luk (12/11/2012)
Yes, that's correct, but the index itselft is composed only of that bigint.

No it's not. If you were talking about a nonclustered index, that would be the case, but the clustered index contains every single column in the table within it, that's the definition of a clustered index. The key just consists the bigint (LOBs can't be index key columns).

http://www.sqlservercentral.com/articles/Indexing/68563/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ramasankar Molleti Ten Centuries Points: 1044 More actions · Answer 2

Give me more information about what is the table structure and clustered index key and non clustered index built on it. If the clustered index key long and it is not in increasing order you will find more bookmark lookups which leads to more fragmentation. I don't think changing the fillfactor will solve the issue.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sankar276 (12/11/2012)
If the clustered index key long and it is not in increasing order you will find more bookmark lookups which leads to more fragmentation.

Bookmark lookups don't lead to fragmentation. Page splits are what cause logical fragmentation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

sankar276 (12/11/2012)
If the clustered index key long and it is not in increasing order you will find more bookmark lookups

it is not the case, bookmark lookup happens when non clustrered index doesnt able to retrieve all called columns (in query) , then takes help of clustered key or RID.this can be avoided with the help of covering indexes.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Luk, you have been going at this problem for over 10 days now. Give me or another good professional 15 minutes on your system and you will have a cause and a fix.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service