Description

This article provides a comprehensive guide for clients using Sitecore Managed Cloud on how to effectively request and utilize the "Set up SQL Maintenance Plan" service. The SQL Maintenance Plan is essential for maintaining the health and performance of your Azure SQL databases. This guide covers the importance of regular database maintenance, how to initiate the request, and best practices for ensuring your SQL databases remain optimized. Whether you're new to Managed Cloud or looking to fine-tune your database management, this article will help you navigate the process and achieve optimal results.

Regular Azure SQL database health routine is one of the Managed Cloud Standard best practice suggestions. Failure to follow proper maintenance steps can result in degraded SQL performance over time. The Sitecore Managed Cloud support 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 encompasses several essential actions to ensure optimal performance, such as index and statistics rebuilding and removal of old logs. It is run for every database in a customer Sitecore Managed Cloud implementation. Customer can specify a schedule for the maintenance work to run at a recurring time. It's recommended to schedule the work to repeat each week outside business hours for an environment. However, alternative schedules can be supported as well, such as monthly for lightly loaded websites or every other day for heavily loaded websites.

As part of Azure SQL Maintenance Plan set up, the Sitecore Managed Cloud support creates an Azure Automating Account or utilizes the existing one if already 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 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:

Before and after the maintenance plan is executed

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

Normally, these maintenance plans run in 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.

What the SQL Maintenance Plan Does

• Index Fragmentation Check: A query is run to identify indexes with fragmentation levels above 30%. This generates a list of indexes that require defragmentation.
• Defragmentation Loop: The system enters another loop, specifically for defragmentation.
  • Index Defragmentation: Each identified index undergoes defragmentation. This is a resource-intensive process that heavily loads the server's CPU.
• Next Index: After defragmenting one index, the process moves to the next index in the list.
• Close Defragmentation Loop: Once all indexes have been defragmented, the loop is closed.
• Metadata Update: For each index and table, metadata is updated, specifically the statistics. Statistics are internal data used by the query planner to optimize query execution and updating them is also a resource-heavy task.
• Next Database: After completing all tasks for the current database, the process moves on to the next one in the list.

Best practice

Increase DTUs During SQL Maintenance Plan Execution

To ensure optimal performance of your SQL Maintenance Plan, it's recommended to temporarily increase the Database Transaction Units (DTUs) during the plan's execution. This is particularly important during the initial run, as it allows the plan to process tasks like index defragmentation and database optimization more efficiently. By increasing DTUs, you provide the necessary resources to handle the maintenance workload, reducing the time it takes to complete the process. After the maintenance is complete, you can scale the DTUs back down to your regular settings.

Use a Hybrid Agent for Large Databases

If your SQL Maintenance Plan takes more than 180 minutes to complete due to large databases, Microsoft recommends using a Hybrid Agent. This solution is designed to handle more extensive maintenance tasks by offloading some of the workloads. However, it's important to note that configuring a Hybrid Agent falls outside the scope of standard Managed Cloud support. While this can be an effective approach for large-scale databases, you may need to engage external resources or consult with Microsoft directly for assistance in setting up the Hybrid Agent.

Scope of Support

The Managed Cloud support will set up the initial SQL Maintenance Plan for your environment. However, ongoing maintenance, monitoring, and adjustments to the plan are the customer's responsibility. Additionally, Sitecore support does not provide debugging services for database issues that might arise during or after the execution of the SQL Maintenance Plan.

	Sitecore	Customer
Creating a request in the portal	C, I	R, A
Installation and initial setup of the maintenance plan	R, A	C, I
Changing the maintenance plan logic or parameters (e.g. fragmentation percentage, script execution mode, logs retention period)	C, I	R, A
Analysis of problems and errors in work	C, I	R, A

Legend:

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