Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

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,
0
Senior IT System Engineer
Asked:
Senior IT System Engineer
  • 3
  • 3
  • 3
  • +1
4 Solutions
 
JesNoFearEnterprise Systems Administration Team MemberCommented:
are you using real SQL servers, or SQL Express?

If you are using the full versions, then you can schedule tasks with the SQL Agent.

check out https://support.rackspace.com/how-to/create-a-maintenance-backup-plan-in-sql-server-2008-r2-using-the-wizard/ they have a good how-to document.

Microsoft's How to: Create Multiserver Maintenance Plans
https://technet.microsoft.com/en-us/library/bb153887(v=sql.105).aspx

Modifying Schedules
https://technet.microsoft.com/en-us/library/bb326729(v=sql.105).aspx
1
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
I'm using SQL Server 2012 Standard Edition.
0
 
ZberteocCommented:
Don't reinvent the wheel, use Ola Hallegren's Maintenance solution and I recommend you to run the master script. It will create all the object in either master database or a user database of your choice together with the jobs that are needed.

Particularly the Index Optimization procedure does exactly what you are after to, checking the fragmentation and does the corresponding operation accordingly. On top of that it also updates the statistics for you, which helps for overall better performance. You will have to install it on all your servers and schedule the jobs as you need. The jobs are created without schedule. Here is the solution:

https://ola.hallengren.com/

In order to install it you only need to execute the "master" script called:  MaintenanceSolution.sql, the link do download is at the top.

Beside the index optimization procedure it will also install the backup and integrity check procedures and jobs, both very useful for general maintenance.

The solution is rock solid, mature and free! You can't ask for better than that.
1
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
ITSystemEngineer, do you still need help with this question?
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Yes, so after executing the MaintenanceSolution.sql in SSMS, how to schedule it to run automatically ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just go to SQL Agent jobs and schedule each of the jobs created by the script.
1
 
ZberteocCommented:
These are the jobs:
ola_hallegren_jobs.PNG
1
 
ZberteocCommented:
Any updates with this?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks people !
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now