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 123»»»

Rebuild Index Expand / Collapse
Author
Message
Posted Tuesday, December 1, 2009 8:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, 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
Post #827173
Posted Wednesday, December 2, 2009 12:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
How big's the index? How many pages?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #827223
Posted Wednesday, December 2, 2009 2:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:15 AM
Points: 1,159, Visits: 4,659
Hi,
Read & use the index de-fragmentation script added in my signature.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #827256
Posted Wednesday, December 2, 2009 2:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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.
Post #827263
Posted Wednesday, December 2, 2009 7:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, 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


Thanks
Ali
Post #827431
Posted Wednesday, December 2, 2009 7:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #827438
Posted Wednesday, December 2, 2009 7:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #827439
Posted Wednesday, December 2, 2009 7:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:15 AM
Points: 1,159, Visits: 4,659


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
Post #827442
Posted Wednesday, December 2, 2009 7:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:15 AM
Points: 1,159, Visits: 4,659
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
Post #827446
Posted Wednesday, December 2, 2009 7:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, 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
Post #827467
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse