Hi,

As we all know index maintenance is important especially on large Dynamics Ax databases, but often I see installations where there are little or no maintenance plans or all kinds of exotic scripts. Therefor I want to show you guys the SQL Server Maintenance Solution by Ola Hallengren, this does not only contain stored procedures for index maintenance but also for database backup and integrity.

Installing it is easy, grab a copy of the installation script and run it. But I would suggest you install it on a new maintenance database and change the following parameters of the install script.

USE [master] -- Specify the database in which the objects will be created.

SET @CreateJobs          = 'Y'          -- Specify whether jobs should be created.
SET @BackupDirectory     = N'C:\Backup' -- Specify the backup root directory.
SET @CleanupTime         = NULL         -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
SET @OutputFileDirectory = NULL         -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
SET @LogToTable          = 'Y'          -- Log commands to a table.
Code language: SQL (Structured Query Language) (sql)

• USE [master]: Installing it on a separate database maintenance instead of the master makes it easier to uninstall or update.
• @CreateJobs: I like to set this option to ‘N’ because I don’t want to call the stored procedures directly from the agent but from a T-SQL block inside of a maintenance plan. This looks more consistent so that it doesn’t look like a lack of maintenance plans.
• The rest of the configuration is quite self-explanatory and personal 😉

At the moment I only use it for index and statistics maintenance so here’s an example on how I like to run it on Dynamics Ax databases.

USE [Maintenance]

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',
@SortInTempdb = 'Y',
@PageCountLevel = 1000,
@FillFactor = 80,
@LogToTable = 'N',
@MaxDOP = 0
Code language: SQL (Structured Query Language) (sql)

More information on the parameters of this stored procedure.