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.