Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to automate & schedule this Index optimization Script ?

Posted on 2016-07-18
10
Medium Priority
?
114 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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
LVL 52

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 52

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 52

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

604 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