Introduction
This article provides a comprehensive overview of database maintenance and its critical role in optimizing system performance. Readers will learn why regular maintenance is essential, how it enhances efficiency, and what to expect during the process. Two key aspects of maintenance are index defragmentation and statistics updates.
Why do I need database maintenance?
Database maintenance is essential for ensuring performance, reliability, and data integrity. Over time, databases accumulate inefficiencies that can slow down queries, increase storage costs, and lead to potential failures. Regular maintenance helps prevent these issues.
As part of our maintenance processes, we will perform regular database maintenance. This is an official performance recommendation from Microsoft.
Would this still be required if we upgraded to a newer version?
Yes, index fragmentation is inherent to database use. Maintenance will be performed regularly regardless of software version.
Do you have other customers that have needed this?
Yes, other customers have needed and benefited from database index defragmentation. All customers will be moving into regular maintenance schedules where this is performed.
Do you have an estimate for how long the maintenance will take?
Unfortunately, no. The time required is based on the amount of data and degree of fragmentation, is variable across customers and environments. Once the indexes have been defragmented in the initial pass(es), regular subsequent runs will be shorter as the data fragmentation will be lesser.
Will there be downtime required? How long if so? Will downtime affect application and the API?
We will schedule 2x sub-maintenance windows when the database servers are rebooted. These reboots take less than 15 minutes on average.
Yes, API integrations and application usage should be paused during these windows.
How is Fabric measuring to ensure the changes have a positive impact on performance?
We continuously monitor databases across a number of performance metrics, including CPU, IOPs, and Database Transaction Unit (DTU) usage
In addition to database metrics, we are actively improving our query performance and database transaction monitoring