research Question

rebuild index for selected table using existing index rebuild script

Avatar of marrowyung
marrowyung asked on
Microsoft SQL Server
25 Comments3 Solutions50 ViewsLast Modified:


We are using SQL server 2016 STANARD and we have index always defrag a lot daily, like 88% everyday, and we are using index rebuild script like:

-- Ensure a USE  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(, @schemaname = QUOTENAME(          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 < 20.0              SET @command = N'SET QUOTED_IDENTIFIER ON; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';          IF @frag >= 20.0              SET @command = N'SET QUOTED_IDENTIFIER ON; 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 

how to make this script check the table name and if it sees that tables name , build the index of that table completely?

Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 25 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 25 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros