SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Index


Rebuild Index

Author
Message
ALI SQLDBA
ALI SQLDBA
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 335
Hello SQL Gurus

We have rebuilding index issue in our SQL environment. our jobs were failing since the server has been in production (since a week). I have learnt that the issue is because of the rebuild index LOB datatypes. According to MSDN, while rebuilding index, if a clustered index has any lob data type included in it or if a non clustered index has any lob data type coloumns included in it then the Rebuild will not suceed with rebuild = ON option.

I tried to rebuild the database when it is offline. defragmentaion is not reducing. I have collected the tables with rows >= 4000 and checked the corresponding coloumns included in it and also checked the respective data types. surprisingly, the data types on the coloumns are INTs and my rebuild offline does not defrag those coloumns. Can anyone help me out in this regard.

Thanks
Ali
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118307 Visits: 45534
How big's the index? How many pages?

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


muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2645 Visits: 4953
Hi,
Read & use the index de-fragmentation script added in my signature.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

luckysql.kinda
luckysql.kinda
SSC Eights!
SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)

Group: General Forum Members
Points: 898 Visits: 659
Run following query and let us know the result.
select * from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

Find 'avg_fragmentation_in_percent' - External Fragmentation
and 'avg_page_space_used_in_percent' - Internal

I want you to go ahead and remove the indexes which are referring LOB (text,ntext,image) and run rebuild again.
ALI SQLDBA
ALI SQLDBA
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 335
I have ran the scripts and executed the store proc. I see that only update statistics is applied on my database. the tables in the database has varied page count. I think I have very few pages >=10 - <=25 and their frag ranges from 87.5 - 33.33.

I cannot remove the indexes which include LOBs in it. Our database is updated quaterly by our third party vendor on a quaterly basis. Even if i drop those indexes now, It's a temporary solution for my problem.

But I am not able to understand why the rebuild index task is not able to defrag the columns with int data type. Is it something set wrong at the database level settings..? I am confused.. Help me out plzzzz Crying


Thanks
Ali
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118307 Visits: 45534
sqldba.jagan (12/2/2009)
The tables in the database has varied page count. I think I have very few pages >=10 - <=25 and their frag ranges from 87.5 - 33.33.


Don't even bother rebuilding indexes so small. Firstly, under 24 pages the index won't show much, if any change at all when rebuilt. This is due to the way SQL allocates pages in smaller indexes. The other point is that fragmentation is only a performance issue when doing large scans of an index. Emphasis large. The rough rule of thumb is that you should start worrying about fragmentation when an index reaches around 1000 pages as, at that point, the performance impact of the fragmentation may become noticeable.

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118307 Visits: 45534
luckysql.kinda (12/2/2009)
I want you to go ahead and remove the indexes which are referring LOB (text,ntext,image) and run rebuild again.


Why?

External fragmentation usually refers to file-level fragmentation that's fixed with a disk defragmentation program.
Internal fragmentation usually refers to fragmentation of the index pages that's fixed with ALTER INDEX .. REBUILD or ALTER INDEX ... REORGANIZE

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


muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2645 Visits: 4953


But I am not able to understand why the rebuild index task is not able to defrag the columns with int data type. Is it something set wrong at the database level settings..? I am confused.. Help me out plzzzz [Crying]


Hi,
Can u send me the Object structure.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2645 Visits: 4953
Hi,
Gail already point out pages are small

If the table is "Heap" fragmentation ll not remove

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

ALI SQLDBA
ALI SQLDBA
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 335
Most of my tables are either clusterd or non clustered.

so as per your expertise... this is not a big deal for me to worry. Right?

Also, do you recommend me t run rebuild offline scheduling an outage of the database every week.??

Thanks
Ali
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