question

colibri avatar image
colibri asked

index rebuild/reorgnize does not update in report

When doing an index rebuild/reorgnize the task runs but does not update it's status in server report for 'index physical usage stats' Any ideas why this doesn't work? Any suggestions? Thanks.
indexesrebuildreorganize
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
It depends on the state and size of the index/statistics. Some wont defragment because the are too small/not fragmented enough. Can you provide any details on the object that you are referring to? [edit] as you havent come back and there are no votes on this question I thought I'd add a demo that you can try yourself to see how the frag/defrag process works. USE [adventureworks] GO IF OBJECT_ID('TestTable') > 0 DROP TABLE TestTable CREATE TABLE TestTable ( IntTwo INT , CharThree VARCHAR(7950) ) INSERT INTO [TestTable] ( [IntTwo] , [CharThree] ) SELECT TOP ( 1000 ) --<<< Change this number to suit IntTwo = ABS(CHECKSUM(NEWID())) % 1000 + 1 , CharThree = REPLICATE(CHAR(ABS(CHECKSUM(NEWID())) % 25 + 65), ABS(CHECKSUM(NEWID())) % 30 + 1) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 ; CREATE CLUSTERED INDEX CI_Char ON [TestTable] (inttwo) -- there will be very little fragmentation found this time SELECT OBJECT_NAME([ddips].[object_id]) AS [table_name] , [i].[name] , [ddips].[avg_fragmentation_in_percent] , [ddips].[fragment_count] , [ddips].[page_count] , 'before insert' FROM [sys].[dm_db_index_physical_stats](DB_ID(), OBJECT_ID('testtable'), NULL, NULL, NULL) AS ddips INNER JOIN [sys].[indexes] AS i ON [ddips].[index_id] = [i].[index_id] AND [ddips].[object_id] = [i].[object_id] INSERT INTO [dbo].[TestTable] ( [IntTwo] , [CharThree] ) VALUES ( 500 , -- IntTwo - int 'This is a test row to show fragmentation' -- CharThree - varchar(100) ) -- there will be some fragmentation found this time as the inserts will have been spread out in the table SELECT OBJECT_NAME([ddips].[object_id]) AS [table_name] , [i].[name] , [ddips].[avg_fragmentation_in_percent] , [ddips].[fragment_count] , [ddips].[page_count] , 'after insert, before defrag' FROM [sys].[dm_db_index_physical_stats](DB_ID(), OBJECT_ID('testtable'), NULL, NULL, NULL) AS ddips INNER JOIN [sys].[indexes] AS i ON [ddips].[index_id] = [i].[index_id] AND [ddips].[object_id] = [i].[object_id] -- some of the fragmentation will be fixed here but the index is very small DBCC INDEXDEFRAG ([adventureworks],[testtable],[CI_Char]) -- there is likely to still be some fragmentation found this time SELECT OBJECT_NAME([ddips].[object_id]) AS [table_name] , [i].[name] , [ddips].[avg_fragmentation_in_percent] , [ddips].[fragment_count] , [ddips].[page_count] , 'after defrag' FROM [sys].[dm_db_index_physical_stats](DB_ID(), OBJECT_ID('testtable'), NULL, NULL, NULL) AS ddips INNER JOIN [sys].[indexes] AS i ON [ddips].[index_id] = [i].[index_id] AND [ddips].[object_id] = [i].[object_id] -- What if we make a massive change to the data INSERT INTO [dbo].[TestTable] ( [IntTwo] , [CharThree] ) SELECT TOP 50000 ABS(CHECKSUM(NEWID())) % 5000 + 1 , -- IntTwo - int 'This is a test row to show fragmentation' -- CharThree - varchar(100) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 ; -- there will be large amounts of fragmentation found this time as the inserts will have been all over the range of values in the table SELECT OBJECT_NAME([ddips].[object_id]) AS [table_name] , [i].[name] , [ddips].[avg_fragmentation_in_percent] , [ddips].[fragment_count] , [ddips].[page_count] , 'after big insert, before defrag' FROM [sys].[dm_db_index_physical_stats](DB_ID(), OBJECT_ID('testtable'), NULL, NULL, NULL) AS ddips INNER JOIN [sys].[indexes] AS i ON [ddips].[index_id] = [i].[index_id] AND [ddips].[object_id] = [i].[object_id] -- the index is bigger, the fragmentation is more serious, defrag will do more work DBCC INDEXDEFRAG ([adventureworks],[testtable],[CI_Char]) -- we should see a big change in these figures as the defrag will have resolved a lot of issues SELECT OBJECT_NAME([ddips].[object_id]) AS [table_name] , [i].[name] , [ddips].[avg_fragmentation_in_percent] , [ddips].[fragment_count] , [ddips].[page_count] , 'after big defrag' FROM [sys].[dm_db_index_physical_stats](DB_ID(), OBJECT_ID('testtable'), NULL, NULL, NULL) AS ddips INNER JOIN [sys].[indexes] AS i ON [ddips].[index_id] = [i].[index_id] AND [ddips].[object_id] = [i].[object_id] hope this helps.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered
Some time few indexes are not defragment because table data size is very small and these also not effect the performance.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.