Arjun SreeVastsva (7/13/2015)
Hey Guys,i have table in database having the 60 % fragmentation when i am checking the fragmentation .i have rebuild the indexes on that table.after that i have checked the index fragmentation of the table still it is showing 60 % only.
i have checked the fill factor and all.every thing looks good.
can one help me what is reason behind this
Thanks,
Arjun
Please post results of the following query, substitute the name of the table into the variable first
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @table SYSNAME
SET @table = 'your tablename here'
SELECTDB_NAME()AS DBName
, s.nameAS SchemaName
, OBJECT_NAME(o.OBJECT_ID)AS TableName
, ISNULL(i.name, 'HEAP')AS IndexName
, CASE i.[type]
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'NonClustered'
WHEN 3 THEN 'XML'
WHEN 4 THEN 'Spatial'
ENDAS IndexType
, i.is_disabledAS IsDisabled
, CASE
WHEN i.data_space_id > 65600 THEN ps.name
ELSE f.name
ENDAS FG_or_PartitionName
, p.partition_numberAS PartitionNo
, p.[rows]AS [RowCnt]
, p.data_compression_descAS CompressionType
, au.type_descAS AllocType
, au.total_pages
, au.total_pages / 128AS TotalMBs
, au.used_pages/ 128AS UsedMBs
, au.data_pages/ 128AS DataMBs
FROM sys.indexes i
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] IN (1,3) THEN p.hobt_id
WHEN au.[type] = 2 THEN p.partition_id
END = au.container_id
WHERE o.is_ms_shipped <> 1
AND OBJECT_NAME(o.OBJECT_ID) = @table
ORDER BY OBJECT_NAME(o.OBJECT_ID)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉