Optimizing a production database with no downtime risk

The production SQL Server database serves the ERP system and related applications. The environment tolerates no downtime and no uncontrolled experiments. Every performance change (from an index through statistics to instance configuration) goes through an established process: measuring the current state, preparing proof of impact, formal approval and a controlled deployment with the ability to roll back.

• 01The production database runs continuously and serves critical business processes.
• 02Every change must be reversible and must not degrade existing behavior.
• 03Standard diagnostic tools require interpretation in the context of the specific workload and schema.
• 04Optimization is not a one-off project: it is a cyclic process adapted to a changing workload.

An established, repeatable optimization process based on six steps.

• 01Measurement: collecting a baseline: query execution times, waits, I/O statistics, execution plans and instance load.
• 02Proof: preparing an analysis that identifies the specific problem, its cause and the predicted effect of the change.
• 03Change candidate: preparing a precise change: an index, statistics, configuration or a query refactor.
• 04Approval: a deployment decision based on the proof, taking the risk and the maintenance window into account.
• 05Deployment: a controlled application of the change with a state copy, a transaction and a rollback plan.
• 06Re-measurement: comparing the results with the baseline and confirming the effect, or deciding to roll back.

• 01No change without measurement. Intuition and experience point the direction, but the decision is based on data.
• 02Minimal intervention. One change at a time, with a clear scope and the ability to roll back in isolation.
• 03The production environment is not a laboratory. Performance tests can point the direction, but the final validation happens under real load.
• 04Documentation of every change. The problem description, the proof, the change, the result and the decision stay in the knowledge repository.

• 01The process is applied cyclically and adapted to the changing database workload.
• 02Every change has documented proof of impact and the ability to roll back.
• 03Optimizations cover indexes, statistics, instance configuration and query refactoring.
• 04The production environment remains stable: no incidents caused by uncontrolled changes.