When will our SQL Server run reliably again?

“Does our team lack the expertise?” Sometimes, yes. More often, the environment has changed: the data has grown, processes are used differently, and new reports and interfaces compete with day-to-day business. The two can be told apart, and both can be addressed. That has been our work at PSG since 1998: find the cause, change what works, and check that it holds.

Tuning and managing are two different jobs

SQL Server performance can be tuned. To keep it stable, it has to be managed continuously. Three disciplines are involved:

• Performance tuning fixes a specific problem or speeds up a process.
• Performance engineering designs the application and its workload so they hold up under the expected conditions.
• Performance risk management detects future degradation early, rates it by its business impact and checks whether measures work.

Good tuning can remove a problem for good and create substantial headroom. But it does not freeze the environment. Data volumes, usage, background jobs, releases, statistics and indexes keep changing. SQL Server chooses its execution strategies based on estimates and reuses them. A strategy that was right yesterday is therefore not necessarily right for tomorrow’s data.

For management, this means a recurring cycle: identify critical processes, assess risks, agree on measures, monitor changes and verify the effect.

An example from our practice

A central list view in a business-critical application responded sluggishly. A first, technically obvious fix made no difference to the measurements. The finding: in SQL Server’s execution history, a specific plan had been forced for the key query a long time ago. Nobody knew when or why. That forcing prevented the improvement from taking effect.

After the forcing was removed, the workload observed and a new plan forced by agreement, the mean runtime of the function fell by 98.6%, compared over millions of executions. Not a single line of application code was changed. Our page on performance tuning describes the full case.

The lesson for management: even an earlier fix can become a risk if nobody documents it and reviews it regularly. Not every case ends this clearly. But the path is always the same: an evidence-based finding, an agreed change, a measured effect.

A fixable fault or a structural limit?

Often there is a specific, fixable cause: an unfavorable execution plan, a missing index, maintenance at the wrong time, a blocking transaction. Then it is a fault that can be repaired, and we say so.

Sometimes an application reaches a structural limit: data access does not scale with the data volume, transactions hold shared data for too long, or the load has outgrown the current design. Then one more individual fix only helps for a while.

We show with evidence which of the two you are dealing with. We keep short-term stabilization and structural change apart, and for each option we state the effort, the effect and the remaining risk. That way you decide on a clear basis rather than after the next slowdown.

What “stable” means

Three expectations are often mixed up:

• Availability: Is the system reachable, and does recovery after a failure work?
• Performance stability: Do important business processes stay within their response-time targets under the expected conditions?
• Capacity: How much concurrent work can the system handle while meeting those targets?

A server can be up and still be too slow for the month-end close. That is why the assessment starts with the business process: what has to be how fast, in which time window and under which load?

Why monitoring can be green while the application is slow

A healthy VM, normal average CPU and successful jobs are valuable signals. They do not tell you whether an important business process meets its response time. Existing tools often do a lot here, provided monitoring, historical data and root-cause analysis fit together. IT leadership should regularly see:

• Response time of the most important processes, including the slow outliers
• Share within target: how many calls stay within the agreed target
• Changes in individual queries and their execution plans
• Releases and maintenance with a before-and-after comparison
• Time to finding: how long it takes until a cause is backed by evidence
• Effect: which measures were actually re-measured

What is measured, and which data is missing, belongs in the same report.

Who can influence what

A DBA lays essential foundations: configuration, maintenance, diagnostics and reliable operations. Development teams or software vendors shape data access and transactions. Infrastructure and cloud platforms set further conditions. So that responsibility does not get stuck between “database”, “application” and “vendor”, we separate three areas in our work:

• What we handle ourselves: analysis, evidence-based findings, approved changes to the database and configuration, each with a rollback path and a check of the effect.
• Where we work with the vendor: if the application code belongs to a software vendor, we provide a traceable finding with conditions, impact and a proposed change. After a vendor change, we check whether the effect materializes. The vendor’s schedules and approvals are the vendor’s.
• What you decide: priority, budget, accepted limitations and the remaining risk. We lay out the options with their effort and consequences.

Even without access to the application code, a lot can be achieved this way. The example above is one such case.

Seven questions for your team

Use the questions as a basis for discussion. Every answer should be explainable with a concrete example or piece of evidence.

1. Which three business processes must not slow down unnoticed? Name the process, the response-time target or time window, and the business impact.
2. Do we know how they normally behave at peak times? Put load, data volume and comparison period into context.
3. Do we have historical query and plan data if a problem is only reported tomorrow? Check the retention period, the coverage and known gaps.
4. Can we reliably compare changes after a release? Define the baseline and the test criterion before the change.
5. Who talks to the software vendor when the cause lies in the code? Name the contact person and a finding both sides can follow.
6. Who decides on short-term measures and on rolling them back? Clarify authority to intervene, approval, rollback path and effect check.
7. Do we know which recurring problems cause the most business damage? Prioritize by affected processes and impact, not just by technical counters.

An unclear answer points to a gap in information or responsibility first, not to personal failure. Pick the gap that matters most to the business and close it first. A joint assessment of this kind is the performance part of our health check.

Which next step fits?

A first engagement with us has a clear question, an agreed scope and a decision point. It ends with a usable result: a cause backed by evidence, an effective measure, a concrete action item for the vendor or a clear statement of which data is still missing. Then you decide how to proceed.

• A reliable starting point: a SQL Server health check examines an agreed question, states evidence and limits and puts the next measures in order, even if your own team implements them.
• A specific case: we narrow down with you what has changed and agree on analysis and tuning with a check of the effect, for individual requests also as a consulting hours package.
• Ongoing care: with PSG MX, we monitor, analyze and improve the agreed application and verify the effect, month by month. Only here do our tools run continuously, and they are built for exactly this kind of ongoing management: MX AIOps continuously collects wait times, resources, jobs and events on site, and PSG QX analyzes the execution history in Query Store. That way changes stand out before users report them, and the effect of every measure is backed by evidence. Both run in your environment and do not sync any data to a public cloud. Goals, responsibilities and scope are agreed in advance.

For routine operations, our SQL Server DBA services are the right place to start; for recovery and architecture, see infrastructure and high availability. If your system is down right now, get urgent help.

Common objections

“Our web servers run stable, too.”

The uncomfortable truth first: SQL is not always executed the same way. On a web or application server, the program code determines which path is taken; the same call takes the same path. SQL, by contrast, only describes what result is needed. How it is obtained is decided by SQL Server at run time, based on statistics, parameter values and data distribution. The same query can therefore get a different execution plan tomorrow and cause many times the work, without anyone changing a line of code.

On top of that, many application tiers spread requests across several interchangeable instances. A database, by contrast, usually holds the shared data, coordinates competing changes and chooses its execution strategies based on data volume and distribution. More backend instances can accept more orders, for example. But if the related data access locks the same rows for a long time, the queue grows anyway. These are typical architectural traits, not fixed rules: backends can become bottlenecks too, and databases can scale.

“It has been slower since the maintenance.”

An important clue, but not yet proof of the cause. Statistics updates, index changes and upgrades can affect how queries are executed; maintenance and backups consume resources. Necessary maintenance should therefore not be dropped. Its benefit and side effects should be checked with a before-and-after comparison.

“More CPU or the cloud will settle it.”

Additional resources can help. They can also raise running costs without removing the actual cause. In the cloud, the provider takes over parts of platform operations, depending on the service; data access, transactions and response-time targets remain your responsibility. An availability SLA is not a promise that every business process is fast enough.

“But the health check was good.”

Then it is worth asking honestly what it actually examined. Were configuration, maintenance and best practices rated on a given date? Or also whether the most important business processes meet their response times, under peak load and after the last release? The former is an important foundation, but it does not answer the question of performance stability. And even a good result only holds for the period it covers.

“The same software runs fast for other customers.”

A useful comparison, once the conditions are known: data volume and distribution, version, extensions, concurrent usage, resources and configuration. It proves neither a mistake by your operations team nor that the software holds up under all conditions.

“Would a different database engine solve it?”

For a particular task, another engine may be a better fit. A comparison of your business processes under representative conditions will show that, including migration, operations and total cost. Problematic access patterns and long transactions usually move along with a switch.

Sources

• Microsoft: Query processing architecture guide
• Microsoft: Design to scale out