Link to home
Start Free TrialLog in
Avatar of Albert Widjaja
Albert WidjajaFlag for Australia

asked on

How to automate & schedule this Index optimization Script ?

Hi All SQL Experts,

How can I schedule this SQL script below to be executed once in a week for all my SQL Servers Database ?

-- 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(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 

Open in new window


If you have any other better script to rebuild index, please feel free to edit or suggest me instead.

Thanks,
SOLUTION
Avatar of JesNoFear
JesNoFear
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Albert Widjaja

ASKER

I'm using SQL Server 2012 Standard Edition.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ITSystemEngineer, do you still need help with this question?
Yes, so after executing the MaintenanceSolution.sql in SSMS, how to schedule it to run automatically ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Any updates with this?
ITSystemEngineer, you're one of our Experts so please give a good example by closing your questions.
Or at least return and give us some feedback.
Cheers.
Thanks people !