Description

This article provides a detailed overview of the steps involved in the "Set up SQL Maintenance Plan" request, and the expected outcomes. To access the request, click here.

Action	Sitecore	Customer
Create a request in the portal	C, I	R, A
Install and set up the maintenance plan	R, A	C, I
Set the start time and schedule	R, A	C, I
Change the maintenance plan logic or parameters (For example, fragmentation percentage, script execution mode, logs retention period)	C, I	R, A
Analyze problems and errors in work	C, I	R, A

Legend:

R – responsible
A – accountable
C – consultant
I - Informed

Regular Azure SQL database health routine is one of the Managed Cloud Standard best practice suggestions. Failure to follow proper maintenance steps can degrade SQL performance over time. The Sitecore Managed Cloud support team has a standard solution for maintenance steps that is built on Azure Automation, known as the "Set up SQL Maintenance Plan" service in the Service Catalog of ServiceNow.

The Azure SQL Maintenance Plan includes several essential actions to ensure optimal performance, such as rebuilding index and statistics, and removal of old logs. It is run for every database in a customer Sitecore Managed Cloud implementation. A customer can specify a recurring schedule for the maintenance work. It is recommended to schedule the work to recur weekly outside business hours for an environment. However, alternative schedules are also supported, such as monthly for lightly loaded websites or every other day for heavily loaded websites.

As part of the Azure SQL Maintenance Plan setup, the Sitecore Managed Cloud support team creates an Azure Automating Account—or uses an existing one if available—and a Runbook that performs the maintenance work using various SQL queries.

To demonstrate the effectiveness of this maintenance approach, the Sitecore Managed Cloud support team can execute a utility SQL query before and after running the Runbook. This allows for a comparison of the results in the customer's environment.

The following example demonstrates the difference in average fragmentation before and after the maintenance plan was executed:

The execution of a SQL Maintenance Plan does not cause any downtime. However, a temporary spike in consumed DTUs (Database Transaction Units) may be observed during the plan's execution. A "master" database, which is generally the most complicated Sitecore database in terms of this maintenance logic, will experience the most load. Therefore, it is recommended to initiate the first run during non-working hours.

Normally, these maintenance plans run for less than an hour for all the databases in a standard Sitecore Managed Cloud deployment. Azure Automation provides a standard allocation of minutes for execution, with the exact amount varying depending on the region. As of Spring 2023, it was 500 minutes for most regions. If the processing time for this maintenance work exceeds the allocated defaults, additional fees may apply.