Index Rebuild

  • 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

  • How big is the table? How many pages?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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" 😉

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply