February 23, 2009 at 11:10 am
We have a table heavily fragmented which has only non clustered Indexes...
I m trying to remove the fragmentation using the following command :
ALTER INDEX ALL ON tablename
REBUILD WITH(ONLINE = ON)
But the fragmentation never changes...it still remains the same....How can we remove the fragmentation?? Please advice me....Thank you
February 23, 2009 at 11:24 am
As far as I understand it from answers I got to a similar post I made myself the other day, you can't defragment unless you have a clustered index on the table
Give me a minute and I'll find the relevant thread
February 23, 2009 at 11:26 am
Thanks for the quick reply....So, can't we defragment the non clustered indexes??
February 23, 2009 at 11:32 am
http://www.sqlservercentral.com/Forums/Topic652865-146-1.aspx#bm653613
See thread above. you;d have to create a clustered index, then do the rebuild/reorganise, and then drop the clustered index
It might be worth considering adding a clustered index to the table, expecially if you foresee it happening often.
February 23, 2009 at 11:36 am
Here's another post that might be of interest
http://www.sqlservercentral.com/Forums/Topic660743-361-1.aspx
February 23, 2009 at 11:43 am
mazzz (2/23/2009)
As far as I understand it from answers I got to a similar post I made myself the other day, you can't defragment unless you have a clustered index on the table
Nope. You can't defrag the table is there's no clustered index, because without a cluster the table is a heap and has no defined order. You certainly can defragment nonclustered indexed that are defined on a heap.
ssismaddy: How big's the index? Generally anything under 1000 pages you don't have to worry about fragmentation. Small indexes do tend to remain fragmented even after a rebuild. It has to do with how pages are allocated.
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
February 23, 2009 at 11:48 am
GilaMonster (2/23/2009)
mazzz (2/23/2009)
As far as I understand it from answers I got to a similar post I made myself the other day, you can't defragment unless you have a clustered index on the tableNope. You can't defrag the table is there's no clustered index, because without a cluster the table is a heap and has no defined order. You certainly can defragment nonclustered indexed that are defined on a heap.
Thanks for the clarification.
I remember now, my situation was that rebuilding the indexes didn't allow me to shrink the database at all (because the tables were fragmented). Hence the confusion. Apologies to the OP.
February 23, 2009 at 1:21 pm
Thanks for all the inputs....
@Gila : The index pages are around 70MB in size. So, Please let me know if I can Reduce the defragmentation in the indexes!!!
February 23, 2009 at 1:29 pm
ssismaddy (2/23/2009)
@Gila : The index pages are around 70MB in size.
How many pages does the index you're trying to rebuild have?
Query sys.dm_db_index_physical_stats for the specific index that you're rebuilding.
What's the fragmentation before and after?
Are you shrinking this database at all (manually or autoshrink)?
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
February 23, 2009 at 2:44 pm
The record_count is around 3 million. The fragmentation is 87% before the rebuild. even after the rebuild it shows same 85%. Now, I dropped the index and recreated it. now it is 0%. so rebuild is not same as dropping and recreating the index?? Thanks for the reply...
February 23, 2009 at 2:45 pm
and we never shrink the database in auto....we always do it manually...
February 23, 2009 at 3:01 pm
Rebuild is essentially the same as drop and recreate. There may not have been enough space for the rebuild to create the new index without fragmentation.
You still haven't told me how many pages the index has. (not the table, the nonclustered index)
You shouldn't be shrinking your database at all, manually or automatically. Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
February 23, 2009 at 3:29 pm
Thanks for your valuable suggestion. This sound really good.
the page_count is 37,358.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply