Introduction

The sql maintenance plan uses Ola Hallengren's optimization plan. If you already have it installed, you must uninstall it and then re-install it, if you want the latest version. We can not guarantee that all jobs will run successfully with an "old" implementation of the MaintenanceSolution.sql file that is installed on the server. If you do not have any MaintenanceSolution.sql installed, you can skip the uninstalling section.

Uninstalling existing MaintenanceSolution.sql

You can find the uninstall script on the FAQ on Ola Hallengren's website here. The direct link of the download is: https://ola.hallengren.com/scripts/misc/Uninstall.sql

Run the uninstall script so all old jobs are deleted and are created with the latest implementation. If you already have the latest version, this is not needed.

Screenshot from the FAQ page on Ola Hallengren's website.

Image Added

Installing MaintenanceSolution.sql

1. Download MaintenanceSolution.sql installation

Navigate to Ola Hallengren's website and download the MaintenanceSolution.sql package. The direct link of the download is: https://ola.hallengren.com/scripts/MaintenanceSolution.sql

Screenshot from the front page on Ola Hallengren's website.

Image Added

2. Open MaintenanceSolution.sql file

Open the MaintenanceSolution.sql on Sql Server Management Studio (double-click), and connect to the SQL Server you want to install the maintenance plan. The script is "using Master" in the beginning, so no need to change the database the script is targeting.

3. (Optional) Change script parameters

Change the configurable parameters on the script. You can change parameters like where the backup directory is, or the cleanup time (delete backup after X hours) i.e. retention time. Below, you can see the changes we made on interworks beta sql server.

Screenshot from interworks beta sql server configuration.

Image Added

4. Run the installation script

Run the script and wait for the objects to be installed. This script should not take more than 10 seconds. This script creates stored procedures on your master database that contain the implementation for the MaintenanceSolution.

Screenshot from stored procedures created by the MaintenancePlan.sql

Image Added

It is recommended that you do not schedule the Stored Procedure themselves on the Sql Server Agent. Create a maintenance plan instead that references these stored procedures. If you create scheduled jobs on the stored procedures themselves, when they are uninstalled, the scheduling will be lost. On the other hand, if you create a maintenance plan, you can uninstall the MaintenancePlan.sql and the scheduling will remain scheduled. That way, you can delete/uninstall the MaintenancePlan.sql and install a new version of it (see previous steps), and you can keep all the scheduling and parameter configuration on your server.

Image Added

5. Create a maintenance plan

Navigate to SqlServer → Management → Maintenance Plan → Right Click → New maintenance plan → Name the new maintenance plan → OK

Image Added

The new maintenance plan opens up. Double click on the subplan_1 template that is created automatically. It will open up the properties.

Image Added

In this example, we will schedule the Index Optimization Plan. Change the name/description and set a scheduling according to your needs. In this tutorial, we set the index optimization to run at 01.00 AM every day.

Image Added

Click OK on the new schedule, and save the sub-plan. Now we need to give the Maintenance Plan a task to run. From the Toolbox on the management studio (this should open when you open a maintenance plan), select Execute T-SQL Statement Task. Double click the new task on the maintenance plan, and set the T-SQL statement to the following:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'N'

This index optimization statement will run every day at 1 AM.

Image Added

6. A complete maintenance plan example

Apart from index optimization, a comprehensive optimization plan with backups, cleanup etc, might look like this:

Image Added

and have a schedule like this:

Image Added

The screenshots above, show what interworks is using for our BETA infrastructure.

Image Added

Disclaimer: The screenshot and schedule might not be up to date with the current maintenance implementation on our BETA infrastructure.