I run following script:-
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'SFNData');
SET @object_id = OBJECT_ID(N'FDIP_B');
IF @object_id IS NULL BEGIN PRINT N'Invalid object'; END ELSE BEGIN
SELECT IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count, IPS.ghost_record_count, IPS.fragment_count, IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS WHERE IPS.avg_fragmentation_in_percent>0;
To find Fragmentation level on table named "ABC" and found avg frag. in percentage as per attached image.
Then i applied rebuild / Reorganize indexes on this table through right click on indexes in S.S.M.S. After rebuilding / Reorganizing i run same script to check frag. level then result showing 0 for Nonclustered Indexed but heap index frag. percentage not updating.
And other problem is when i run this script on same table after 24 hours it showing same avg_frag. percentage for all indexes as it was showing before Rebuilding / Reorganizing indexes.
Please tell me where i am doing wrong. And help me to reduce fragmentation percentage of indexes for fast result from queries.
Thanx in advance