Overview
A nightly batch that once finished in a short window now runs into the working day, and the fix proposed so far is a larger server. This scenario describes how AceMQ's SQL Server consulting works backwards from the engine's own wait statistics to the statements responsible.
Challenge
Nobody could say what had changed. The instance had been upgraded to a newer version and the compatibility level raised in the same year, statistics maintenance ran on a fixed calendar, and the team had no baseline to compare against. A larger server would also have raised the core-based licence cost.
Environment
A SQL Server instance on Windows Server in the customer's data centre, one large application database, a nightly batch plus a daytime reporting load, and a DBA team of two who own the change process.
Approach
AceMQ agrees the question, then collects read-only diagnostics through the customer's DBA or over screen-share: wait statistics, Query Store data, configuration, backup history and blocking. Findings are ranked by production risk and walked through in a working session. Each change is tested on a copy, applied one at a time and measured against the baseline.
Solution
- Capture a baseline of wait statistics and Query Store runtime data before changing anything
- Rank queries by duration, CPU and reads and compare plans before and after the compatibility level change
- Identify plan regressions and force the last good plan while the underlying statements are fixed
- Review blocking, tempdb contention, memory grants and statistics maintenance against the evidence
- Deliver a ranked change list with rollback steps and a way to prove each change worked
Outcome
The engagement delivers a ranked, evidence-backed change list with a recorded baseline, a tested rollback for each change, and a working session in which the customer's DBAs take ownership. Any hardware decision is made after the waits are known, not before.
Kafka
Kubernetes
PostgreSQL
Docker
dbt
