July 13, 2015 at 10:24 pm
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
July 14, 2015 at 2:47 am
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
July 14, 2015 at 6:12 am
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