Not Able to reduce % of Index Fragmentation Please suggest....Urgent..

• anshu84onnet

  SSC Eights!

  Points: 836

  More actions

  August 15, 2011 at 11:33 pm

  #246436

  HI All

  I am using the microsoft recommended script for decreasing the fragmentation level.script ran successfully but not able to decrease fragmentation for all index on that db still i can see 100% fragmentation of clustered index some table are small in size can that be the reason.please suggest thanks in advance..

  -- Ensure a USE <databasename> statement has been executed first.

  SET NOCOUNT ON;

  DECLARE @objectid int;

  DECLARE @indexid int;

  DECLARE @partitioncount bigint;

  DECLARE @schemaname nvarchar(130);

  DECLARE @objectname nvarchar(130);

  DECLARE @indexname nvarchar(130);

  DECLARE @partitionnum bigint;

  DECLARE @partitions bigint;

  DECLARE @frag float;

  DECLARE @command nvarchar(4000);

  -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

  -- and convert object and index IDs to names.

  SELECT

  object_id AS objectid,

  index_id AS indexid,

  partition_number AS partitionnum,

  avg_fragmentation_in_percent AS frag

  INTO #work_to_do

  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

  WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

  -- Declare the cursor for the list of partitions to be processed.

  DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

  -- Open the cursor.

  OPEN partitions;

  -- Loop through the partitions.

  WHILE (1=1)

  BEGIN;

  FETCH NEXT

  FROM partitions

  INTO @objectid, @indexid, @partitionnum, @frag;

  IF @@FETCH_STATUS < 0 BREAK;

  SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

  FROM sys.objects AS o

  JOIN sys.schemas as s ON s.schema_id = o.schema_id

  WHERE o.object_id = @objectid;

  SELECT @indexname = QUOTENAME(name)

  FROM sys.indexes

  WHERE object_id = @objectid AND index_id = @indexid;

  SELECT @partitioncount = count (*)

  FROM sys.partitions

  WHERE object_id = @objectid AND index_id = @indexid;

  -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

  IF @frag < 30.0

  SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

  IF @frag >= 30.0

  SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

  IF @partitioncount > 1

  SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

  EXEC (@command);

  PRINT N'Executed: ' + @command;

  END;

  -- Close and deallocate the cursor.

  CLOSE partitions;

  DEALLOCATE partitions;

  -- Drop the temporary table.

  DROP TABLE #work_to_do;

  GO

• Jeff Moden

  SSC Guru

  Points: 1004436

  More actions

  August 16, 2011 at 12:39 am

  #1368941

  I'm sure that Grant Fritchey or Gail Shaw will have the precise numbers in their head but, yes... small tables (especially those less than 1 extent) won't be defragged because they may live in "mixed extents" due to their small size.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• muthukkumaran Kaliyamoorthy

  SSCoach

  Points: 19026

  More actions

  August 16, 2011 at 12:59 am

  #1368946

  SELECT

  object_id AS objectid,

  index_id AS indexid,

  partition_number AS partitionnum,

  avg_fragmentation_in_percent AS frag

  INTO #work_to_do

  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

  WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

  According to Jeff, if the table reside in mixed extent it may has less pages it's not owning the extent.

  we suggest you to add the condition in your select statement page_count >1000 (MS's rule of thumb the pages >1000 has to be defragment it)

  Muthukkumaran Kaliyamoorthy
  https://www.sqlserverblogforum.com/