December 1, 2009 at 8:46 pm
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
December 2, 2009 at 12:13 am
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
December 2, 2009 at 2:03 am
Hi,
Read & use the index de-fragmentation script added in my signature.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 2:18 am
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.
December 2, 2009 at 7:01 am
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
December 2, 2009 at 7:07 am
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
December 2, 2009 at 7:09 am
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
December 2, 2009 at 7:12 am
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
https://www.sqlserverblogforum.com/
December 2, 2009 at 7:18 am
Hi,
Gail already point out pages are small
If the table is "Heap" fragmentation ll not remove
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 7:40 am
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
December 2, 2009 at 7:57 am
Ali_SQLDBA (12/2/2009)
Most of my tables are either clusterd or non clustered.
Um, the distinction is clustered or heap. A nonclustered index is a separate structure and can sit on either tables with a clustered index or heaps
so as per your expertise... this is not a big deal for me to worry. Right?
Not for the small tables. For the large ones, it's still something that you need to do.
Also, do you recommend me t run rebuild offline scheduling an outage of the database every week.??
Offline, no. If your system is 24/7, then rebuild the indexes that you can online, if they need rebuilding, and only the ones that have to be rebuilt offline (because of LOB columns) get an offline rebuild.
Use a custom script rather than just a maintenance plan. I'm fond of this one - http://sqlfool.com/2009/06/index-defrag-script-v30/, lots of options to customise how much you rebuild, where the threshold are and other details.
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
December 2, 2009 at 7:59 am
muthukkumaran (12/2/2009)
Hi,Read & use the index de-fragmentation script added in my signature.
Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?
Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error details
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
December 2, 2009 at 8:06 am
GilaMonster (12/2/2009)
muthukkumaran (12/2/2009)
Hi,Read & use the index de-fragmentation script added in my signature.
Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?
Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error details
Gail,Thanks for the update & Suggestion.I ll edit and post Script ASAP.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 10:24 pm
Gila - "External fragmentation usually refers to file-level fragmentation that's fixed with a disk defragmentation program."
According to my understanding the external fragmentation is nothing to do with disk fragmentation program. This is simply hip hop between extents because of page split. What's ur PoV (point of view)?
December 3, 2009 at 12:04 am
/* Offline, no. If your system is 24/7, then rebuild the indexes that you can online, if they need rebuilding, and only the ones that have to be rebuilt offline (because of LOB columns) get an offline rebuild.
*/
Ali,
If you have a downtime/offpeak please rebuild during ur downtime as doing it online is always more expensive than a offline rebuild.
If you dont have any downtime online rebuild is the only option.
Regards,
Raj
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply