Solved

How to automate & schedule this Index optimization Script ?

Posted on 2016-07-18
10
64 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
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 2

Assisted Solution

by:JesNoFear
JesNoFear earned 125 total points
Comment Utility
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 7

Author Comment

by:Senior IT System Engineer
Comment Utility
I'm using SQL Server 2012 Standard Edition.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
ITSystemEngineer, do you still need help with this question?
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
Comment Utility
Yes, so after executing the MaintenanceSolution.sql in SSMS, how to schedule it to run automatically ?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
Comment Utility
Just go to SQL Agent jobs and schedule each of the jobs created by the script.
1
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
Comment Utility
These are the jobs:
ola_hallegren_jobs.PNG
1
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Any updates with this?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 7

Author Closing Comment

by:Senior IT System Engineer
Comment Utility
Thanks people !
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
A procedure for exporting installed hotfix details of remote computers using powershell
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now