SQL Server Performance Forum – Threads Archive

Rebuild Index not defragmenting all index

Hi all, Why when I Rebuild all fragmented indexes (ALTER INDEX on > 30%) from a table, many indexes remain fragmented and cannot be defragmented ? Regards

Are the index ids >1 or are they indexes on system tables…would be intresting to know. If the index id >1 then you defragmentation would not have any effect since they are non clustered indexes. Cheers
Sat

http://sqlserver-qa.net/blogs/tools/archive/2007/04/05/sql-server-2005-index-optimization-best-practices.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I rebuild all Index with index_id >= 1 on a user table but they are still fragmented after (> or = 1) Regards

Are you rebuilding the indexes or defragging them? What command/script are you using?
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Rebuilding, DECLARE @DBname VARCHAR (64) DECLARE @MaxFrag DECIMAL DECLARE @Fragmented VARCHAR (64) SET @DBname = DB_NAME() SET @MaxFrag = 30 DECLARE @TableName VARCHAR (64) DECLARE @IndexName VARCHAR (64) DECLARE my_cursor CURSOR FOR select so.name as ‘Table Name’, si.name as ‘Index Name’, convert(int,ips.avg_fragmentation_in_percent) as ‘Fragmentation' from sys.dm_db_index_physical_stats (DB_ID(@DBname), NULL , NULL, NULL ,NULL)ips join sysobjects so on ips.object_id = so.id join sysindexes si on ips.object_id = si.id and ips.index_id = si.indid where ips.avg_fragmentation_in_percent > @MaxFrag and Index_id > 0 order by so.name,si.name OPEN my_cursor FETCH NEXT FROM my_cursor INTO @TableName, @IndexName, @Fragmented WHILE @@FETCH_STATUS = 0 BEGIN print ‘Fragm.:’ + @Fragmented + ‘ Index:’ + @IndexName + ‘(‘ + @TableName +’)' EXECUTE(‘ALTER INDEX ‘ + @IndexName + ‘ ON ‘ + @TableName + ‘ Rebuild’) FETCH NEXT FROM my_cursor INTO @TableName, @IndexName, @Fragmented END CLOSE my_cursor DEALLOCATE my_cursor The Output is always : Fragm.:50 Index:index_2103678542(Act_InternalActivity) Fragm.:75 Index<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />K_Act_InternalActivity(Act_InternalActivity) Fragm.:43 Index:index_1275151588(Act_Readings) Fragm.:80 Index:index_1787153412(AdvancedListTemplates) Fragm.:63 Index:index_2137058649(Contacts) Fragm.:50 Index<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />K_Lists(Lists) Fragm.:50 Index:IX_MenuItems(MenuItems) Fragm.:33 Index:nc2MSmerge_contents(MSmerge_contents) Fragm.:30 Index:nc3MSmerge_contents(MSmerge_contents) Fragm.:46 Index:nc4MSmerge_contents(MSmerge_contents) Fragm.:40 Index:cMSmerge_current_partition_mappings(MSmerge_current_partition_mappings) Fragm.:53 Index:ncMSmerge_current_partition_mappings(MSmerge_current_partition_mappings) Fragm.:50 Index:c1MSmerge_genhistory(MSmerge_genhistory) Fragm.:66 Index:IX_Params(Params) Fragm.:36 Index:index_245575913(Planning) Fragm.:31 Index<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />K_Planning(Planning) Fragm.:75 Index:index_437576597(Queries) Fragm.:31 Index:index_613577224(Shops_Categories) Fragm.:53 Index:IX_Shops_Categories(Shops_Categories) Fragm.:54 Index:IX_Shops_Contacts(Shops_Contacts) Fragm.:43 Index:MSmerge_index_1963947610(Shops_Contacts) Fragm.:50 Index<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />K_StandardControls(StandardControls) Fragm.:75 Index:index_661577395(StandardInfos) Fragm.:50 Index:uc2sysmergesubsetfilters(sysmergesubsetfilters) Fragm.:50 Index:index_677577452(Tasks) Fragm.:50 Index:IX_Tasks(Tasks) Fragm.:80 Index<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />K_Tasks(Tasks) Fragm.:75 Index:index_693577509(TechnicalExceptions) Best Regards

You not using REBUILD WITH option,by default it specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. As BOL specifies: Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I Try this : ALTER INDEX ALL ON MSmerge_contents
REBUILD WITH (SORT_IN_TEMPDB = ON); SELECT INDEX_ID, index_type_desc, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats(db_id(),Object_ID(N’MSmerge_contents’),NULL, NULL, ‘DETAILED’) The output is always : 1CLUSTERED INDEX0
1CLUSTERED INDEX0
2NONCLUSTERED INDEX33,3333333333333
2NONCLUSTERED INDEX0
3NONCLUSTERED INDEX46,6666666666667
3NONCLUSTERED INDEX0
4NONCLUSTERED INDEX30,4347826086957
4NONCLUSTERED INDEX0
6NONCLUSTERED INDEX0
6NONCLUSTERED INDEX0 Regards

THat is a system related table for replication, I don’t think you need to reindex that table. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Ok but on an user Table, it produce the same behaviour : ALTER INDEX ALL ON Tasks
REBUILD WITH (SORT_IN_TEMPDB = ON); SELECT INDEX_ID, index_type_desc, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats(db_id(),Object_ID(N’Tasks’),NULL, NULL, ‘DETAILED’) Reports always : 1CLUSTERED INDEX80
1CLUSTERED INDEX0
2NONCLUSTERED INDEX50
2NONCLUSTERED INDEX0
8NONCLUSTERED INDEX50
8NONCLUSTERED INDEX0
Regards

Have you referred my blog entry above as it explains using SORT IN TEMPDB option. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>