Contents
The script was reviewed, approved and run inside the maintenance window. Ten minutes later the application stopped responding. There was no error in the script; the work it did was correct. The problem was how long it locked the table while doing it. This article is about the question most approval processes never ask: the script does the right thing, but does it leave production standing while doing it?
A Known Number Nobody Uses
One finding has been repeated in IT management for a long time: more than half of unplanned outages affecting mission critical services are caused directly by change, configuration or release management failures. Figures published by organisations running very large systems go higher still; roughly seventy per cent of outages in live systems have been reported as caused by a change.
What is interesting about that number is that everyone knows it and almost nobody measures it inside their own organisation. Outage counts are measured, resolution times are measured, service levels are measured. Whether a change caused the outage usually stays as free text somewhere in the incident record and never gets aggregated.
Because it is never aggregated, nobody knows how many of last year's outages came from a database change. As long as that answer is unknown, whether the approval process actually protects anything is also unknown.
Approved and Safe Are Not the Same Word
Most change approval focuses on correctness: does it touch the right table, does it use the right condition, does it match the business rule. Those are important questions and they are usually asked well.
The question not asked is about behaviour: how long will this statement run, how much will it lock, how much log space will it generate, what happens to normal application traffic while it runs. A script can be entirely correct and still dangerous enough to stop production, and there is no contradiction between the two.
The blind spot in review
If a script ran in seconds in the test environment, the reviewer usually does not treat duration as a risk. But the table that holds ten thousand rows in test holds thirty million in production. Duration does not simply scale with row count; locking behaviour changes completely past certain thresholds, and that threshold is never crossed in test.
Six Patterns That Stop Production
Every pattern below is correctly written, was approved, and has caused an outage in production.
| Pattern | Why it causes an outage |
|---|---|
| Adding a not null column with a default to a large table | Depending on version and type it can require every row to be rewritten. The table cannot be modified for the whole duration and reads may block too. |
| Building or rebuilding an index without the online option | Writes on the table are blocked for the whole operation. The same statement with the online option causes no outage; the difference is a single keyword. |
| Updating or deleting millions of rows in one transaction | Row locks escalate to a table lock past a certain count. The transaction log grows, and when it fills, the whole database stops accepting writes. |
| Adding a foreign key or a constraint | It requires every existing row to be validated, and validation can lock both tables while it runs. |
| Altering a heavily used procedure or view | Changing the definition takes a schema lock and waits for every running query using that object; meanwhile new requests queue behind it. |
| Dropping an index or statistics, or changing a table substantially | The outage does not arrive at execution time but afterwards. Query plans change and a query that took seconds now takes minutes. The change looked successful, so the causal link is never made. |
The last row matters most, because it is where organisations are most often wrong. The execution succeeded and returned no error, so the change is recorded as clean. The outage arrives the next morning at peak hour and the incident review looks everywhere else.
Three Questions the Assessment Is Missing
A risk assessment usually answers how important the change is. Preventing an outage needs three more questions, and all three can be read from the content of the script.
1. What does this statement lock and how widely? Lock type and scope can be derived from the shape of the statement. A statement that will take a table level lock on an object from the critical list belongs in the highest band even when its content is correct.
2. How many rows will be affected? The affected row count is the deciding threshold for lock escalation and log growth. If that number can be estimated in advance, doing the same work in batches becomes an option.
3. Can this be rolled back and is the rollback script ready? That is the first question asked during an outage, and a script written at that moment is always a script written in a hurry. The rollback should be produced and reviewed before the change is approved.
SQL Change Guard derives the first and third from the parsed script: the objects touched, the statement type, matches against the critical object list and findings such as an unqualified update set the risk band, while the rollback script is prepared from the current definition on the server. For the second question, the affected row count, the record is kept after execution; as those counts accumulate it becomes visible which kinds of work are actually large. Because the assessment rests on written rules rather than human judgement, the same script always gets the same result.
The Worst Case: A Half Finished Execution
There is only one thing worse than an outage: an execution left half finished during one. If the script has ten statements and the sixth failed, the database is now neither in its old state nor in its new one.
This is hardest with data definition statements, because in some database systems they commit implicitly and cannot be wrapped in a transaction and rolled back together. An all or nothing guarantee is therefore not always available.
A design that accepts this reality does three things. It records how far the script got, statement by statement, so the state is known. It produces the rollback in advance and writes it to be idempotent, so it can run safely against a partially applied state. And it defines per server, ahead of time, what happens on failure: roll back automatically, or stop and wait for a human decision.
Without those three, a half finished execution is what actually extends the outage. The team first tries to work out what happened, then argues about what to do; the service is down throughout.
Two Indicators Worth Measuring
Change caused outage rate. How many outages in a period had a change as their root cause? Producing that number needs a field linking to the change record rather than free text in the incident. The moment it is measured, the discussion stops being anecdotal.
Change failure rate. How many approved and executed changes did not achieve their intended result or had to be rolled back? The spread between organisations on this indicator is very wide; the most mature teams stay at a few per cent while teams with immature processes run at several times that. What creates the gap is not how careful the team is, but whether the assessment looks at content.
Read together, the two indicators show whether the approval process actually protects anything. If the approval count is high but both rates are also high, the process is producing records rather than protection.
Frequently Asked Questions
We run inside a maintenance window. Is that not enough?
A maintenance window limits duration, it does not change behaviour. It falls short in two cases. First, if the runtime is not known in advance the window can overrun and the work is left half done, which is worse than not starting. Second, some effects never appear inside the window: a slowdown caused by a changed query plan shows up at peak hour the next day. Windows work for changes whose duration can be estimated and which can be rolled back.
It ran cleanly in test. Why would it behave differently in production?
For three reasons. Data volume differs and locking behaviour changes completely past certain thresholds that test never crosses. Concurrency differs; nobody else writes to that table in test, while hundreds of sessions do in production, and lock contention only exists there. Finally the statistics, and therefore the query plans, differ. A test environment validates correctness, not behaviour.
How can we estimate lock duration by looking at the script?
Exact duration cannot be predicted, but the risk class can be derived from the shape of the statement, and that is what the decision needs. Which statements take table level locks, which can force a rewrite of every row, which must validate existing rows: that is a known set. Turned into a rule set, it can raise a warning that a statement may lock production before the script ever runs. What the decision needs is not the duration but the presence of the risk.
Would examining every change this closely not slow us down?
Having a person do the review slows you down; having a rule do it does not. The patterns that carry locking risk are a limited set and can be detected automatically. The large majority of scripts contain none of them and pass straight through. The only thing that slows down is the genuinely risky minority, which is exactly what should slow down.
After an outage, how do we find which change caused it?
Only one thing is needed: a record that, given a time range, shows what changed in the database during it. Without it the team aligns timestamps across several systems by hand, which takes hours while the service stays down. With history kept per object the question is answered in seconds: did this table's definition change in the last twenty four hours, who changed it and under which request.
Let us try it on your own scripts
We will show live how the risk band is derived from the content of the script, which findings raise it, and how the rollback script is prepared.
Book a Demo →