?
Solved

How to automate & schedule this Index optimization Script ?

Posted on 2016-07-18
10
Medium Priority
?
108 Views
Last Modified: 2016-09-05
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
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 2

Assisted Solution

by:JesNoFear
JesNoFear earned 500 total points
ID: 41718176
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
 
LVL 8

Author Comment

by:Senior IT System Engineer
ID: 41718220
I'm using SQL Server 2012 Standard Edition.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 41719381
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41741922
ITSystemEngineer, do you still need help with this question?
0
 
LVL 8

Author Comment

by:Senior IT System Engineer
ID: 41743472
Yes, so after executing the MaintenanceSolution.sql in SSMS, how to schedule it to run automatically ?
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41743721
Just go to SQL Agent jobs and schedule each of the jobs created by the script.
1
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1000 total points
ID: 41744589
These are the jobs:
ola_hallegren_jobs.PNG
1
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41762878
Any updates with this?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41784312
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
 
LVL 8

Author Closing Comment

by:Senior IT System Engineer
ID: 41784323
Thanks people !
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question