Solved

How to automate & schedule this Index optimization Script ?

Posted on 2016-07-18
10
83 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
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 7

Author Comment

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

Accepted Solution

by:
Zberteoc earned 250 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 47

Expert Comment

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

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 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41743721
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
ID: 41744589
These are the jobs:
ola_hallegren_jobs.PNG
1
 
LVL 26

Expert Comment

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

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 7

Author Closing Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 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