I finished a root cause analysis recently that I was quietly proud of. It had everything a respectable incident document is supposed to have: a timeline to the second, the plan regression that started it, the parameter behind the regression, and the deployment three weeks earlier that changed the parameter.

The document was accurate. That was the uncomfortable part. Not one important fact required hindsight. Every warning had existed before the outage. I had not solved a mystery. I had written an excellent account of an avoidable event.

So this post is the other document, the one that should exist before the incident. These are eight checks I now run on every engagement, why each one matters, and the T-SQL to see where you stand. The first pass takes about an hour. Testing and implementing any change still belongs in normal change control.

A quick note. Details are blended across engagements and changed so nothing identifies a client. Test everything below outside production first, as you would with anything you read on the internet.

There is an easy way to ruin this audit before it begins: assume every edition can do every trick. Record the exact version and edition first. Automatic tuning remains an Enterprise feature in boxed SQL Server. Developer edition through SQL Server 2022, and Enterprise Developer in SQL Server 2025, provide the Enterprise feature set for non-production development. Resource Governor is available in Enterprise and Developer through SQL Server 2022. SQL Server 2025 makes it available in Enterprise, Enterprise Developer, Standard, and Standard Developer. Azure SQL offerings have different support rules.

SELECT SERVERPROPERTY('ProductVersion')      AS product_version,
       SERVERPROPERTY('ProductMajorVersion') AS major_version,
       SERVERPROPERTY('Edition')             AS edition,
       SERVERPROPERTY('EngineEdition')       AS engine_edition;

The audit queries are read-only, but read-only does not mean permission-free. Requirements vary by view and SQL Server version. Common permissions include VIEW DATABASE STATE or VIEW DATABASE PERFORMANCE STATE for database-scoped information, and VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE for instance-scoped information. Changing a setting requires separate permission and change approval.

Checks 1 and 2 are database-scoped. The detailed statistics and table-footprint queries in checks 5 and 6 are also database-scoped, so run them in each writable user database you intend to audit. The remaining queries examine instance-wide configuration, tempdb, Resource Governor, or all online user databases.

The One-Hour Prevention Pass

Check 1: Automatic Plan Correction, With Receipts

This is the one that annoys me most on supported editions. The capability has existed since SQL Server 2017, yet I still meet systems where nobody has even checked its state. Enabling it is one statement. Deciding to enable it is still a change, and those are not the same thing.

When SQL Server identifies an eligible query plan choice regression, automatic plan correction can force the last known good plan, verify the result, and undo the force if performance does not improve. It does not catch every regression, but it can make a useful class of plan incidents self-correcting.

SELECT name,
       desired_state_desc,
       actual_state_desc,
       reason_desc
FROM sys.database_automatic_tuning_options;

If actual_state_desc is OFF, do not jump straight to the ALTER statement. Read reason_desc, confirm that the version and edition support the feature, and verify that Query Store is healthy. A single command can enable the feature, but it cannot create the history the feature needs.

ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Automatic plan correction requires Query Store to be read-write, which is check 2. After a week, ask the database for receipts instead of assuming that ON means useful:

SELECT type,
       reason,
       score,
       execute_action_initiated_by,
       execute_action_initiated_time,
       revert_action_initiated_by,
       revert_action_initiated_time,
       JSON_VALUE(state, '$.currentValue')                        AS current_state,
       JSON_VALUE(state, '$.reason')                              AS state_reason,
       JSON_VALUE(details, '$.planForceDetails.queryId')          AS query_id,
       JSON_VALUE(details, '$.planForceDetails.regressedPlanId')  AS regressed_plan,
       JSON_VALUE(details, '$.planForceDetails.recommendedPlanId') AS recommended_plan,
       JSON_VALUE(details, '$.implementationDetails.script')      AS script
FROM sys.dm_db_tuning_recommendations
ORDER BY score DESC;

The score is the estimated value or effect of the recommendation on a scale from 0 to 100, with larger values considered better. If you are not ready to enable automatic correction, leave it off and review this DMV for a fortnight. SQL Server can still identify potential regressions when the option is disabled. Treat each recommendation as a lead to investigate, not a guaranteed future outage. The DMV is not persisted, so a Database Engine restart clears its recommendations. If the history matters, collect it elsewhere.

Check 2: Query Store Is On, but Is It Alive?

SQL Server 2022 enables Query Store by default for newly created databases. Databases restored from earlier versions, and databases carried through an in-place upgrade, retain their previous setting. In every case, on is not the same as working.

The failure I find most often is a Query Store sitting in READ_ONLY because its storage filled months ago. It stopped collecting quietly, nobody was alerted, and the first person to notice is the person who desperately needs yesterday’s history. That is a terrible time to discover that the security camera has not been recording.

SELECT actual_state_desc,
       desired_state_desc,
       readonly_reason,
       current_storage_size_mb,
       max_storage_size_mb,
       query_capture_mode_desc,
       size_based_cleanup_mode_desc,
       stale_query_threshold_days
 FROM sys.database_query_store_options;

On a database with an active workload, also confirm that Query Store contains recent runtime data:

SELECT MAX(last_execution_time) AS latest_captured_execution
FROM sys.query_store_runtime_stats;

A null or unexpectedly old timestamp on a busy database is a reason to inspect the capture policy and Query Store health. Interpret it against the workload and collection interval, because an idle database should not produce fresh executions.

If actual_state_desc and desired_state_desc disagree, something forced it read-only and readonly_reason tells you what. It is a bitmask. 65536 means the storage size was exceeded, which is the common one.

Here is a sensible starting configuration, not a universal recipe. Adjust the capacity and retention to the workload, then monitor them:

ALTER DATABASE CURRENT SET QUERY_STORE (
    OPERATION_MODE            = READ_WRITE,
    MAX_STORAGE_SIZE_MB       = 2048,
    QUERY_CAPTURE_MODE        = AUTO,
    SIZE_BASED_CLEANUP_MODE   = AUTO,
    CLEANUP_POLICY            = (STALE_QUERY_THRESHOLD_DAYS = 60),
    MAX_PLANS_PER_QUERY       = 200,
    INTERVAL_LENGTH_MINUTES   = 60,
    DATA_FLUSH_INTERVAL_SECONDS = 900
);

Two notes matter. QUERY_CAPTURE_MODE = ALL can fill storage with single-execution ad hoc noise on some workloads. Also, put an alert on the state. A red row in an occasional audit is not an alerting strategy:

IF DATABASEPROPERTYEX(DB_NAME(), 'Updateability') = 'READ_WRITE'
   AND EXISTS (SELECT 1 FROM sys.database_query_store_options
               WHERE actual_state_desc <> 'READ_WRITE')
    RAISERROR('Query Store is not recording', 16, 1);

Run that check as a SQL Server Agent job step and configure the job to notify somebody when the step fails. RAISERROR by itself does not send an email.

While you are here, audit what is already being forced, including plans with recorded forcing failures:

SELECT p.query_id,
       p.plan_id,
       p.is_forced_plan,
       p.plan_forcing_type_desc,
       p.force_failure_count,
       p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
WHERE p.is_forced_plan = 1
   OR p.force_failure_count > 0;

A nonzero force_failure_count means plan forcing has failed at least once. The counter increments when forcing fails during recompilation, not on every execution. Inspect last_force_failure_reason_desc, the current plan, and recent compilations before assuming that the query is still protected.

Check 3: Parallelism Without a Worker Stampede

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('cost threshold for parallelism',
               'max degree of parallelism');

Cost threshold still defaults to 5. Five is a factory default, not a recommendation and certainly not a family tradition. SQL Server considers parallel alternatives when the best serial plan’s estimated cost exceeds the threshold. That cost is an optimizer estimate, not seconds. If the threshold is too low for an OLTP workload, too many modest queries can receive parallel plans and contribute to worker pressure. CXPACKET and CXCONSUMER waits are evidence to interpret, not a diagnosis by themselves.

DECLARE @target_cost_threshold int = 20; -- Example only. Choose from evidence.
DECLARE @apply_change bit = 0;           -- Change to 1 only after approval.

SELECT value_in_use AS current_value,
       @target_cost_threshold AS proposed_value,
       @apply_change AS apply_change
FROM sys.configurations
WHERE name = 'cost threshold for parallelism';

IF @apply_change = 0
    RETURN;

IF @target_cost_threshold NOT BETWEEN 0 AND 32767
    THROW 50000, 'Choose a cost threshold between 0 and 32767.', 1;

DECLARE @advanced_options_was_on bit =
(
    SELECT CONVERT(bit, value_in_use)
    FROM sys.configurations
    WHERE name = 'show advanced options'
);

IF @advanced_options_was_on = 0
BEGIN
    EXEC sys.sp_configure 'show advanced options', 1;
    RECONFIGURE;
END;

EXEC sys.sp_configure 'cost threshold for parallelism', @target_cost_threshold;
RECONFIGURE;

IF @advanced_options_was_on = 0
BEGIN
    EXEC sys.sp_configure 'show advanced options', 0;
    RECONFIGURE;
END;

The script is deliberately safe by default and uses 20 only as an example. Raise the threshold in small, reviewed increments, observe a complete business cycle, and compare Query Store evidence before and after. Set MAXDOP deliberately as well, based on SQL Server version, available logical processors, NUMA layout, and workload behavior instead of assuming that 0 is suitable.

Neither setting needs a restart, but both can change plan selection across the instance. Treat them as measured workload changes, not harmless checkboxes.

Check 4: tempdb, Measure the Latch Before Adding Files

SELECT file_id,
       name,
       type_desc,
       size / 128.0                 AS size_mb,
       CAST(CASE WHEN is_percent_growth = 1
                 THEN growth
                 ELSE growth * 8.0 / 1024
            END AS decimal(18,2))   AS growth_value,
       CASE WHEN is_percent_growth = 1
            THEN 'PERCENT' ELSE 'MB'
       END                          AS growth_unit
 FROM tempdb.sys.database_files;

tempdb has accumulated enough folklore to qualify for its own mythology. Start with evidence. Look for data files sized for the workload, equal sizes and growth increments across the data files, and fixed-megabyte growth rather than percentage growth. Multiple equally sized data files are a standard starting point for allocation contention, commonly one per logical processor up to eight, but do not multiply files when the waits do not support that diagnosis.

To confirm allocation contention instead of diagnosing by tradition, look at what is waiting right now:

SELECT session_id,
       wait_type,
       wait_duration_ms,
       blocking_session_id,
       resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH[_]%'
  AND resource_description LIKE '2:%';

2:1:1, 2:1:2, and 2:1:3 are familiar first-page examples for PFS, GAM, and SGAM in the first tempdb data file. Allocation pages repeat later in every file, so those three page numbers are examples, not a complete detector. This DMV is also a point-in-time view. Sample it repeatedly and look for sustained PAGELATCH waits on tempdb allocation pages before changing the file count. One screenshot is a clue. A repeated pattern is evidence.

Check 5: Statistics, Stale Is a Diagnosis

SELECT name,
       is_auto_create_stats_on,
       is_auto_update_stats_on,
       is_auto_update_stats_async_on
FROM sys.databases
WHERE database_id > 4;

Auto create and auto update should almost always be on. The interesting setting is is_auto_update_stats_async_on. With it off, a query that triggers an automatic statistics update waits for the update before compilation continues. Turning it on avoids that synchronous wait, but the triggering query compiles with the existing statistics while the refresh runs in the background. That is a tradeoff, not a universal recommendation. On SQL Server 2022 and later, also evaluate ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY to reduce lock contention from the background update.

The settings query is instance-wide. The next query is database-scoped, so run it inside each database that matters. It finds heavily modified statistics on large rowsets that deserve inspection:

SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
       OBJECT_NAME(s.object_id)        AS table_name,
       s.name                          AS stats_name,
       sp.last_updated,
       sp.rows,
       sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.rows > 1000000
  AND sp.modification_counter > sp.rows * 0.1
ORDER BY sp.modification_counter DESC;

The modification counter tracks changes to the leading statistics column. The 10 percent filter is a triage heuristic, not SQL Server’s internal automatic-update threshold. A large counter also does not prove that stale statistics caused the slow query. Compare rows_sampled with rows, then examine the affected query’s estimated and actual row counts. A targeted update with a higher sample rate can help when the statistics are genuinely responsible. Blanket full scans on every large table are how a maintenance job becomes the next incident report.

Check 6: Size Changes the Meaning of Safe

Most of the worst incidents I investigate trace back to a change that was technically correct and run at the wrong scale. The statement was valid. The table was enormous. SQL Server honored both facts.

You do not need a new platform to begin. You need a list, published before the change window, of the tables where nothing casual happens:

WITH table_footprint AS
(
    SELECT object_id,
           SUM(CASE WHEN index_id IN (0, 1)
                    THEN row_count ELSE 0 END) AS row_count,
           SUM(reserved_page_count) * 8 / 1024.0 AS reserved_mb
    FROM sys.dm_db_partition_stats
    GROUP BY object_id
)
SELECT s.name AS schema_name,
       t.name AS table_name,
       f.row_count,
       f.reserved_mb
FROM table_footprint AS f
JOIN sys.tables AS t
  ON t.object_id = f.object_id
JOIN sys.schemas AS s
  ON s.schema_id = t.schema_id
WHERE f.row_count > 50000000
ORDER BY f.row_count DESC;

The row count is approximate, and reserved_mb includes the table’s indexes. Replace 50 million with a threshold that reflects your environment. That is enough to create a deployment gate. The rule fits in one sentence: no potentially blocking or size-dependent DDL against anything on this list without a written execution plan, a tested rollback path, and estimates for duration, transaction-log growth, and lock impact. That one rule has prevented more outages for my clients than many far more impressive-looking projects.

Check 7: Resource Governor for the Workload That Thinks It Owns the Server

Every shop has one. The month-end reporting workload arrives, flattens the transactional workload, gets investigated, gets explained, and then returns next month like a meeting nobody was brave enough to decline.

Governing that workload is supported where the edition permits. The values below are placeholders for a tested policy, not recommended production values. MAX_CPU_PERCENT is an opportunistic maximum that is enforced when CPU is contested, while CAP_CPU_PERCENT is a hard CPU ceiling. For ordinary disk-based workloads, MAX_MEMORY_PERCENT governs query workspace memory for the pool, not SQL Server’s total memory or buffer pool. Memory-optimized tables have additional pool behavior that must be evaluated separately.

USE master;
GO

CREATE RESOURCE POOL ReportingPool
WITH (MAX_CPU_PERCENT = 25,
      CAP_CPU_PERCENT = 40,
      MAX_MEMORY_PERCENT = 25);

CREATE WORKLOAD GROUP ReportingGroup
USING ReportingPool;
GO

CREATE FUNCTION dbo.fn_ClassifyWorkload()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE
             WHEN SUSER_SNAME() = N'DOMAIN\ReportingService'
             THEN N'ReportingGroup'
             ELSE N'default'
           END;
END;
GO

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fn_ClassifyWorkload);
ALTER RESOURCE GOVERNOR RECONFIGURE;

This is an illustrative new configuration. If the server already has a classifier function, add the routing rule to that function instead of replacing it with this example. The classifier belongs in master and is evaluated for every new session, even when connection pooling is enabled. Reusing an existing pooled session does not create a new classification event, and existing sessions keep their current group. Keep the function simple, test with a genuinely new connection, verify the assigned workload group, and confirm dedicated administrator connection access before rollout. An overly restrictive pool can make a query run longer and hold locks longer, so validate the effect on the protected transactional workload as carefully as the effect on reporting.

Check 8: Alert on the Smoke, Not the Ashes

Nearly everybody alerts on the outage. That is useful, but late. Far fewer teams alert on the condition that has been building for several minutes while the database is still answering calls and pretending everything is fine.

A current blocked request that has waited more than thirty seconds is one of the highest-value signals I know. Thirty seconds is an example threshold, not a law. Tune it to the workload, poll the query from a job, retain the results, and account for maintenance that is expected to block:

SELECT r.session_id,
       r.blocking_session_id,
       r.wait_time / 1000 AS wait_seconds,
       r.wait_type,
       r.wait_resource,
       DB_NAME(r.database_id) AS database_name,
       t.text                 AS running_sql
 FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
  AND r.wait_time > 30000;

Positive blocking_session_id values identify another session. Negative values have special meanings, including orphaned distributed transactions and latch owners that SQL Server cannot identify. In particular, -5 by itself does not prove a performance problem. This query is an early-warning signal, not a complete blocking-chain analysis.

Add transaction-log utilization and the reason that log truncation is being held. This can expose pressure caused by an active transaction, missing log backups, replication, or an availability replica before the volume is full:

SELECT d.name AS database_name,
       ls.total_log_size_mb,
       ls.active_log_size_mb,
       CAST(100.0 * ls.active_log_size_mb /
            NULLIF(ls.total_log_size_mb, 0) AS decimal(6,2)) AS active_log_percent,
       ls.log_since_last_log_backup_mb,
       ls.log_truncation_holdup_reason
 FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_stats(d.database_id) AS ls
WHERE d.state_desc = 'ONLINE'
  AND d.database_id > 4
 ORDER BY active_log_percent DESC;

sys.dm_db_log_stats is available in SQL Server 2016 SP2 and later. On an availability-group secondary, the function returns only a subset of its normal columns, so missing size values should not be interpreted as zero pressure. The query shows current pressure and the truncation holdup, not the history of file-growth events. Capture autogrowth separately with Extended Events or your monitoring platform. The warning signal is often visible before the pager goes off, but only if you retain a baseline and alert on sustained abnormal values.

Why None of This Gets Done

The first audit takes about an hour. Much of the remediation is configuration, code, and operating discipline using capabilities the organization already owns. Edition-specific features still need to be checked before anybody promises a change. So why is so much of the practical prevention layer unused?

Because prevention is invisible, and invisible work has no advocate.

The person who spends a quiet Thursday evaluating automatic plan correction, fixing a proven tempdb problem, testing the parallelism configuration, and writing a blocking alert has produced, from the outside, nothing at all. No incident report. No bridge call where they were heroic. Nothing dramatic to put in a review.

The person who fixes a catastrophic outage at four in the morning gets thanked in a company-wide email. The person whose preparation prevented the outage gets a quiet night and no email. I know which reward I prefer, but I also know which one organizations tend to notice.

Nobody here is behaving irrationally. The incentives point at the fire rather than at the wiring, and they have for a long time.

My practical advice is to describe prevention in the language of the incident it removes. Not “I enabled automatic tuning.” Instead, “eligible plan regressions can now be corrected automatically and verified after the force.” Not “I added an alert.” Instead, “we now detect sustained blocking while there is still time to act.” Same technical work, much clearer business value.

Where This Argument Has Limits

Two honest caveats belong here, and the second is the stronger one.

Prevention has a ceiling. You can remove known failure classes. You cannot remove the unprecedented. Monitoring and explanation will never be worth zero, and anyone promising a world with no incidents is selling you something.

You cannot prevent a failure class you have never understood. The root cause analysis is the input to the prevention work. My complaint was never that we write them. It is that we write them, file them, and do not do the next thing.

So the claim is narrower than the title suggests. Explanation is necessary. Treating explanation as the destination is the mistake.

The Section I Now Add to Every Analysis

At the end of every incident document I write, there is now a section that is not about the incident. It is about making the same failure class less welcome next time.

It names the failure class, states what would have made it impossible rather than merely visible, and gives a rough cost. Sometimes the cost is an afternoon and a checkbox. Sometimes it is a planned project. Both are easier to fund when the cost and the failure being removed are explicit.

Some clients skip that section. A few do not, and those are the clients I eventually stop hearing from. It is the strangest form of professional success I have encountered, and I have decided to enjoy it.

The thread underneath all of this is who holds the judgment when the tooling sounds confident, which is the argument across all thirty essays in my book AI: Nobody’s in There: But we’re still in here. All thirty are free to read at pinaldave.com. If you would rather hold a copy, it is on Amazon in paperback, Kindle and audiobook.

If you take one thing from this, take the smallest one. Record the version and edition, then run the Query Store state query from check 2 against your busiest database. It takes about two minutes. If the answer is READ_ONLY, the most valuable performance history on the server may already be disappearing quietly.

This is not a story about explaining incidents better, it is a story about making the explanation a rarer thing to need.

Reference: Pinal Dave (https://blog.sqlauthority.com/), SQL Server Prevention, X

First appeared on A Better Fire Alarm Is Still a Fire

I write about databases. I write about things that are broken and can be fixed, because fixing things is the work I know how to do and it is the work that makes me feel useful.

This one cannot be fixed. I am publishing it anyway.

They completed the AI development plan their employer assigned them, paid for, and tracked in a system with a progress bar. Eleven days later they were on a video call with somebody they had never met, being read a paragraph about role elimination.

Before you read another line, I owe you the truth about what this is. This is not a transcript, and this is not one person. If you go looking for them you will not find them, because I built them. They are assembled out of two years of conversations with people this happened to, in hotel lobbies and airport queues and long messages sent to me at two in the morning by people who could not say any of it out loud in daylight. The questions are mine. The answers are theirs, taken apart and redistributed until nobody is identifiable, because nearly all of them are still job hunting and several of them signed a piece of paper promising not to say what they said to me. Every detail that could point at a company or a person has been changed, including every detail that would tell you who is speaking. The one thing I have not touched is what it feels like, and I did not have to, because that part came back identical every single time.

That is why there is no name here, and it is why I stopped worrying about which details belonged to which person. Somewhere around the ninth conversation I realised I was not collecting nine stories. I was hearing one story with the furniture moved around.

Every article about layoffs is written by somebody who still has a job. Including this one. Please hold that against me while you read it, because they would.

My questions are in bold. Their answers are in plain text. Where I wanted to soften something, I left it alone.

Part One: When They Felt Safe

Tell me what you did, and how long.

Fourteen years. Database side. I ran the platform that the operations business sits on, and by ran I mean I was the person who got called. Not the only one on paper. The only one in practice.

I knew where every body was buried in that system because I buried most of them.

What were you proudest of?

Nobody knows about it. Which is sort of the answer.

There was a night a few years ago. Something started going wrong at about half eleven, and it was the kind of wrong where if I get it right, nothing happens, and if I get it wrong, several thousand people find out about it in the morning and some of them are customers.

I got it right at ten to four. I sat in the dark kitchen for a while afterwards with the adrenaline draining out of me, and then I went to bed, and I got up at seven and went in.

Nobody mentioned it the next day, because there was nothing to mention. Nothing had happened. That is the entire job. You are only ever visible when you fail.

I was invisible for fourteen years, on purpose, and I was proud of it. I would like somebody to understand that being invisible that well, for that long, is not nothing. It is actually the whole thing.

How safe did you feel?

You are going to think I am being dramatic if I answer honestly.

I did not feel safe. I never thought about it. That is different and it is worse. Safe is something you feel when the question has occurred to you. The question had not occurred to me in about nine years.

I had a mortgage sized amount of confidence that if things got bad, I would be one of the last ones. Not because I was special. Because somebody has to keep the lights on and I was the person who knew where the switch was.

What was your last review?

Four months before. Highest band. The written comment said I was critical to continuity of operations.

Critical to continuity of operations. I have that phrase in a PDF. I have read it more times than I want to tell you.

Tell me about the AI development plan.

It came down in the spring. Every technical person got one. Mine was built with my manager in a one on one, and I want to be fair here, it was not a bad plan. It was actually thoughtful. Two certifications, a set of internal modules, and a project where I was supposed to apply it to something real in my own area.

It was in the performance system. There was a progress bar. My manager referenced it in our one on ones. There was a session where a director stood up and said the company was investing in its people rather than replacing them, and I remember thinking, alright. Good. That is the right answer.

Did you believe it?

Completely.

And I want to say something about that, because people are going to read this and think I was naive.

I believed it because believing it was the rational thing to do. They were spending money. My manager was spending time. The plan was specific to my role, not a generic slide deck. Everything a reasonable person uses to judge whether something is real, was there.

I was not naive. I was correct about all the evidence and wrong about the conclusion. Those feel identical from the inside and I have not worked out yet how you tell them apart.

How much time did you put in?

Around two hundred hours over five months. Most of it evenings. A lot of it weekends.

Do you regret the hours?

Not the way you mean.

We went away in July. A week, the coast, the kind of thing we do once a year and talk about for the other fifty one. I took the laptop because I had two modules left and I wanted to finish before the review cycle.

My youngest asked me to come in the water on the second day and I said in a bit. Asked again on the third day. On the fourth day they did not ask.

Nine years old. Not sulking, not making a point, just quietly updated the model of me and moved on. That is the part I cannot put down.

There is a photograph from that week. My partner took it, from the beach, and it is a lovely picture, the two of them up to their waists in the sea, both laughing at something.

And right at the edge of the frame, small and slightly out of focus, there is a plastic table with somebody sitting at it under a parasol.

That is me. I am in the photograph.

I am just not in it.

So no, I do not regret two hundred hours. I regret about forty minutes of it, on a Wednesday, and I know exactly which forty minutes.

Part Two: The Things They Can See Now

Looking back, were there signals?

There were seven. I have counted them. I counted them at three in the morning about two weeks after, which I do not recommend as an activity.

And every single one of them had a completely reasonable explanation at the time. That is what I want people to understand. They were not hidden. They were explained.

Go through them.

Hiring freeze in February. Explained as financial discipline in an uncertain market. Everybody said the same thing, which was that it is better than layoffs. We actually said that to each other. Out loud.

Reorg in March. I moved under a different VP. Never met them. Explained as aligning the technology function to the business units. It came with an org chart that was genuinely tidier than the old one.

Then in April I was asked to properly document my systems. Runbooks, dependencies, escalation, all of it. Explained as operational resilience, and here is the thing, it was overdue. I had been meaning to do it for years. I was pleased somebody finally made me.

I wrote a very good document. I was proud of it. I have thought about that document a lot.

Keep going.

In May my new VP asked me, in a corridor, how long it would take somebody else to pick up my systems if I were hit by a bus.

That is a normal question. It is a good question. A responsible manager should ask it. I gave a straight answer, which was that with the documentation I had just written, a competent person could be dangerous in three weeks and safe in three months.

I answered it well. I was pleased with my answer.

That is the one that is still with you.

That is the one, yes.

Not because I was being lied to. I do not think I was being lied to. Somebody was gathering information and I gave it to them accurately and helpfully, because that is what I am like and it is not a bad thing to be like.

But I answered the question about how quickly I could be replaced, and I answered it enthusiastically, and I made sure my answer was true by writing the document first.

The rest?

One on ones went from forty five minutes to twenty. Then they started being moved. Then twice they were cancelled and not rebooked, and I was relieved because I was busy. I was relieved.

Consultants on site in June. Assessment work. Told they were looking at process efficiency. Everybody assumed it meant a new tool was coming.

And the seventh one is the training. The AI plan itself.

Say more about that.

I read it as investment. Companies invest in people they intend to keep. That is not a stupid inference, it is the correct inference nearly all of the time.

What I would say now is that it is not evidence of anything. It costs a few thousand and it buys a great deal of goodwill and it makes the quarter feel like a company that is doing the right thing. It is the cheapest possible signal for the largest possible amount of trust.

I am not saying anybody set out to deceive me. Honestly, I do not think anybody sat in a room and planned it that way. I think the training was real and the decision was real and they were made by different people in different rooms who never had to reconcile with each other.

Which is somehow worse. If it had been a plan I could at least be angry at somebody specific.

Part Three: Eleven Minutes

Tell me about the day.

The invite arrived at 9:40 the night before. No title. Just a time, 9:00, and thirty minutes booked, and two attendees I had to look up. One was HR. One was from a function I had no dealings with.

I knew. I want to be honest about that. I knew at 9:41.

I did not sleep, and then I got up and showered and put on a proper shirt, because I did not want to be the person who found out in a t shirt. I have thought about why I did that. I think I wanted to arrive at my own execution with my dignity organised in advance.

And then I did something I find harder to talk about than any of the rest of it. I opened a notebook and I wrote down two questions.

Just in case. In case it was a project, or a restructure, or a promotion, or anything at all that a competent person might need to ask a sensible question about. In case I was wrong.

I still have that page. Two questions, in my own handwriting, written at ten to nine by somebody who had eleven minutes left and was trying to be useful.

What happened on the call?

The person from HR was reading. You can hear it. There is a particular flatness to somebody reading something they did not write.

They were not unkind. I want that on the record because it would be easy to make them the villain and they were not. They had a job to do that day and I would guess I was not the first call and I was probably not the last.

The phrase was role elimination. Said twice. Careful to separate it from performance, and careful to say your role, not you, several times, which I understand is the training and which does not land the way it is meant to when you are the person occupying the role.

Eleven minutes. I timed it afterwards from the calendar.

And then?

I was still asking a question when the laptop logged me out.

Not after. During. I was mid sentence, asking something about the handover, whether they wanted me to write anything else down, and the screen changed and asked for credentials that no longer worked.

I sat there for a moment thinking it was a glitch. That is the detail I would take out if this were a film. Too neat. But it is what happened, and it took me a few seconds to understand that it was not a glitch, it was the schedule working correctly.

Somebody had put a time on a calendar for the access revocation, and it was the same time as the call, and the call ran slightly long.

Did you say goodbye to anyone?

No.

Fourteen years. I did not say goodbye to a single person I worked with. I could not, because the mechanism by which I spoke to those people was the laptop, and the laptop was gone.

I did not have most of their personal numbers. Why would I. I saw them every day.

Did you ever get your things back?

A courier brought a box eight days later.

Somebody had packed my desk for me. A plant I had kept alive for six years, a photograph, a stack of notebooks, a phone charger, and my mug.

They had wrapped the mug in bubble wrap.

I sat on the floor in the hall with that box open for a long time, because whoever did that did not have to. Nobody was standing over them. There is no version of that afternoon where anybody would ever have known if they had just dropped a mug in a box with a plant on top of it.

Somebody who was still employed, and frightened, and busy, stopped and found bubble wrap.

I do not know who it was. I have never found out and I have no way to ask. I would like to know. Of everything in this whole story, that is the thing I would most like to know.

What did you do for the rest of that day?

I tidied the kitchen.

My partner was at work and the kids were at school and there was about four hours before anybody came home, and I could not think of a single thing to do with a Tuesday.

So I cleaned the kitchen properly. Behind the appliances, the whole thing. And then I sat in the clean kitchen until half past three.

What happened at half past three?

The eldest came in first. Fourteen, and notices nothing, which is normal and healthy and I would not want them any other way.

Stopped in the doorway with the bag still on, looked around, and said, why is the kitchen like that.

And I said I had a day off.

That was the first lie. It was not even a big one. But I have thought about it more than almost anything else, because I said I had a day off, and then I had to keep on having a day off, morning after morning, for nine days, while they walked past me at that table on the way to school.

Part Four: The Brutal Part

Do you think AI took your job?

No.

And I need you to understand that no is the worse answer. Everybody wants it to be yes. Yes is clean. Yes is a story with a villain in it, and it is a story where you were beaten by something historic, which is almost flattering.

No is worse.

Then what happened to the work?

The work is still there. Every bit of it. Those systems did not become simpler on the day I left the building.

Somebody is doing it. It is being done by fewer people, further away, for less money, using a document I wrote in April that explains exactly how.

That is all this was. It is the oldest thing in business and it has been happening since long before any of this. It is not a technology story at all.

Then where does AI come into it?

AI is the sentence you can say out loud.

You cannot stand up in front of a room and say we moved the work somewhere cheaper. It sounds like what it is. But you can say we are transforming how we operate, we are becoming AI enabled, we are reducing operational overhead through automation, and everybody nods, and the share price does not mind, and nobody has to feel like the kind of person who does the other thing.

AI did not take my job. AI provided the vocabulary that made taking my job sound like progress.

That is a hard thing to say.

It is a hard thing to have watched.

And look, I am not claiming this is universal. I am sure there are places where it genuinely is the technology. I am telling you what happened where I was, and where I was, the cost line moved and the story was written afterwards.

Was the training worthless, then?

No, and this is the part that I find hardest to explain to people, because they want it to be worthless. That would be tidier.

The training was good. I am better than I was. I can do things now that I could not do in March, and in interviews I can talk about them and it lands.

The company paid to make me a stronger candidate for a job that no longer exists at that company. They improved me and then released me, improved, into a market full of other people who had also been improved.

It was not a waste. It just was not for me. It went into my hands and it belonged to somebody else’s quarter.

Do you regret being loyal?

I have tried to make myself regret it and I cannot get there, and I have thought about why.

If I say yes, then the fourteen years were stupid. And they were not stupid. I was good at that job. I liked the people. I am proud of some of what I built and it is still running right now, which is a strange kind of continuing.

Regretting it means agreeing that the correct way to work is to hold something back, and I have met people who work like that and I do not want to be one of them and neither do you.

So no. I do not regret being loyal.

What I regret is thinking loyalty was reciprocal. Those are different things and it took me a while to separate them. I gave loyalty to an organization, which is not a thing that can hold it. Only people can hold it, and the people I gave it to had mostly moved on or moved up or left.

Be loyal. Be loyal to people, by name. Just do not send it upward into a structure and expect it to come back down.

Part Five: After

What were the first two weeks like?

Shame.

Which is insane. I want to say that clearly for anybody reading this in the same position. It is insane and it is not logical and you will feel it anyway.

Nothing I did caused this. I know that. I knew it on the day. And I still could not go to the shop at eleven in the morning on a weekday because I did not want to be seen being somebody who was not at work.

I went at seven. For a month, I went at seven, so that nobody would have to work out what I was.

Who did you tell?

My partner, that afternoon, before the kids came in. Better about it than I was, immediately and completely, which I had not expected and which broke something open in me a bit.

My parents, five weeks. Nearly six.

One of my parents worked for the same firm from twenty two until retirement. I did not know how to explain it in a way that would be heard as something other than me having failed. When I did tell them there was a long pause and then, that sounds like it was nothing to do with you, and that was the first time I properly cried about it.

And the children?

Nine days of days off, and then my partner said we have to tell them, and they were right and I knew they were right and I made them say it twice anyway.

The eldest took it the way they take everything, which is to say they asked two practical questions and then went upstairs, and I have no idea what happened up there and I have never been told.

The youngest asked if we were going to lose the house.

Nine years old. Nobody in that house had said one word about the house. That came from somewhere, a film or a playground or a thing half heard, and it had been carried around alone for however many days, doing sums that a nine year old should never be doing.

I said no. And it was true, I want to be clear, it was true, we were alright, the severance was decent and my partner works. But I had to say it twice before it landed, and the second time I heard my own voice do something I did not like the sound of.

That is the worst minute of the whole thing. Not the call. Not the laptop. A nine year old asking me a question they should never have known to ask.

They stopped here. It was a long stop. I have left the question in and I am not going to describe what it looked like, because they did not agree to that, and because you already know.

How is your partner?

Steadier than me. Has not once, not for a second, made me feel like a problem that needs solving.

Around week three I noticed the shopping was coming from a different supermarket. The cheap one, twenty minutes further out, the one we stopped going to years ago when we did not have to any more.

Nothing was said. No conversation, no let us sit down and look at the numbers, no sigh. The shopping simply started arriving from somewhere else.

I have never mentioned it. Neither has my partner. It has been months now.

I have tried twice to say thank you for it and both times the sentence would not come out, because to say thank you I have to say that I noticed, and the moment I say I noticed it stops being something quietly handled and becomes something we are dealing with. And I think that is exactly what was being protected. That is the whole point of doing it silently.

So we do not talk about the supermarket.

That is the most anybody has ever loved me and it is a shop twenty minutes away.

And the identity part?

Fourteen years of being the person who knows the system.

Not a job title. That was who I was in that building and in a small way outside it. There was a version of me that existed because those systems existed and I was the one who understood them.

That person got switched off at nine eleven on a Tuesday along with the laptop.

I still wake at ten past six. Every day. I have stopped fighting it. I get up and I make tea and I sit at the kitchen table with a laptop that has none of it on it and I apply for things.

And I dream about the systems. Not nightmares, that is the thing. Boring dreams. I am fixing something ordinary, something routine, and somebody is asking me a question and I know the answer.

Then I surface out of it and there is a gap of about four seconds before I remember.

Four seconds where I still have it. Every morning. Months of this now.

That is the worst part of my day and it happens before I have opened my eyes.

And I drink the tea out of that mug. The one from the box.

I did not decide to. It was just in the cupboard with the others and my hand goes to it.

What do you miss?

Not the things you would think.

I miss the Tuesday morning call, which I complained about for eleven years and would have told you was a waste of my life.

I miss the walk from the far car park, because the near one was always full. Four minutes. It was the only four minutes in the day when nobody could reach me and I resented every one of them.

And I miss somebody putting their head round the door and saying, have you got a minute.

I did not know that was a thing a person could miss. Nobody has needed a minute of my time since October.

That is the actual thing, underneath all of it. Not the salary. Being needed. I had it every single day for fourteen years, the way you have air, and I did not know what it was worth until it stopped.

What about the people you worked with?

This is the one I did not expect and it is the one I would warn people about most.

They went quiet.

Not all at once. There was a flurry the first week. Messages, calls, this is outrageous, let me know if you need anything, I will ask around. All of it meant sincerely, I think.

Then it thinned. By week three it was almost nothing. By week six, nothing.

Somebody posted a photograph in December. A team lunch. Twenty odd people squeezed into one frame doing the face people do in those pictures.

I looked at it for a lot longer than I am comfortable telling you.

And it was not because I was missing from it. It was because everybody in it looked completely fine. The thing I had been at the dead centre of for fourteen years had closed over the top of me like water, and it had taken about nine weeks to do it.

That is not a complaint. It is what is supposed to happen. It is exactly what I would have done.

It is just a very strange thing to watch.

Are you angry about that?

I was. For about a month I was very angry about it, more angry than I was at the company, which tells you something.

I am not now, and I will tell you why, and it is not a generous reason.

It is because I know exactly what I would have done. If it had been somebody else, I would have sent the message in week one and meant it, and then I would have had a difficult week, and I would have got busy, and I would have thought about them on a Thursday and not messaged, and then it would have been too long, and then I would have felt awkward, and then I would have said nothing at all.

They are not cold. They are frightened. Every one of them is doing arithmetic about whether they are next, and I am the walking evidence that being good at your job does not settle it. Nobody wants to look directly at that.

And here is the part I cannot get to sit still.

Whoever packed that box and found the bubble wrap is one of the people who went quiet. It has to be. It is the same twenty people. There is nobody else it could have been.

Somebody sat at my desk on a Tuesday afternoon, alone, and carefully wrapped my mug so that it would not break in the post, and then never sent me a message again.

I have turned that over for months and the only place I get to is that both of those are the same thing. Both of them are what frightened looks like. People will do you a kindness in an empty room that they cannot do with their name on it.

I do not blame them. But I would tell anybody still employed and reading this, message the person. In week five, not week one. Week one they are drowning in messages. Week five they are alone in a kitchen.

Was there anyone?

One. Somebody I worked with for six years and was never especially close to.

They have sent me a message every Friday since. Every single Friday. Not about jobs. Something stupid, usually something that went wrong that week, the kind of thing I would have laughed at if I were still there.

Never once asked how the search is going, which is the single kindest thing anybody has done, because everybody else asks and there is never any news and every time I have to say no, nothing yet, and watch them try to arrange their face.

I have not told this person what those messages have meant. I probably should. I might send them this.

What was the first practical thing that went wrong?

Writing my own CV.

I sat down in week two feeling quite businesslike about it, and I could not do it.

Fourteen years, and I could not turn any of it into bullet points. Everything I was genuinely good at came out as a sentence like, knew which things were fragile. Or, was the person you rang. Or, could tell from the shape of a complaint which system had caused it.

None of that goes on a page. The things that made me worth keeping are the exact things that do not survive being written down.

That is a joke I would enjoy a great deal more if it were happening to somebody else.

In the end I wrote something generic with the word stakeholder in it. I hated it. I have now sent it to over ninety places.

And the market?

I am going to be blunt because you asked me to be.

It is bad and it is bad in a way that nobody prepares you for. Not empty. Bad differently.

There are roles. I apply. Most of them go nowhere with no response at all, which I had expected. What I had not expected is the ones that go well. Four rounds, a technical exercise on a weekend, meeting the team, talking about the future, and then a role put on hold. Not rejected. On hold. Which is a word that means nothing and cannot be argued with.

That has happened to me three times. Three times I have been almost employed.

The rejections are fine. It is the almosts that take something out of you, because each one requires you to hope again from the start, and hoping gets more expensive every time.

Part Six: What They Would Tell You

What would you say to someone who feels safe right now?

I am not going to give you a lesson because I do not have one, and I want to say that plainly. I have not grown from this. I have not found myself. I am not grateful for the journey.

But here are things I would actually do.

Write down the names. The people you would want to be able to reach if your access stopped working in the middle of a sentence this afternoon. Get their personal contact into your own phone. Not because anything is coming. Because it takes four minutes and mine took eleven days to reconstruct, partly through somebody’s relative on social media, and I felt like a ghost doing it.

Know what your documentation is worth. I am not telling you to write bad documentation. Do not do that, it is beneath you and it hurts the people you work with. But be clear eyed. The document that makes you a responsible professional is the same document that makes you portable. Write it, and understand what you have written, and price yourself accordingly rather than assuming it will be noticed.

Watch what people ask, not what they announce. The announcements are written by people who want you calm. The questions are asked by people who are actually working something out. Nobody announced anything to me. But somebody asked me in a corridor how fast I could be replaced, and that was the whole thing, right there, four months early.

Treat training as a gift, not a contract. Take everything they will pay for. Take all of it. But the moment you have it, it is yours, and it is a thing you own that has nothing to do with them. Do not file it in your head under they must be planning to keep me.

Keep one thing that is yours. One skill, one relationship, one small thing that does not live inside the company’s systems and would survive the badge stopping working. Mine was almost nothing. Fourteen years of everything I was good at was on the other side of a login.

Is there anything you would not do differently?

Yes, and I want this in, because I do not want somebody reading this and becoming a worse colleague on Monday because of me.

I would still help people. I would still stay late for the outage. I would still explain the thing patiently to the new person for the fourth time.

I would do all of it again, because that was not a strategy that failed. That was just being a decent person at work, and it is most of what I liked about myself for fourteen years, and I am not handing that in as well. They got the job. They are not getting that.

What does a company owe you?

Money for work. Honestly, that is the list.

And I am not saying that bitterly, this is the part I have actually made peace with. A company is not a family and not a friend and not a person, and the mistake I made was not that I trusted mine too much. It is that I asked it for a kind of thing it is not built to give, and then I was hurt when it did not give it, which is a bit like being disappointed by a filing cabinet.

They owed me money for work. They paid it. They owed me notice and severance and they gave it, correctly, and the person on the call was professional and kind.

Everything else I felt about that place, I brought. That was mine. I gave it to something that had no way to hold it.

What I want is for somebody reading this to work that out four years earlier than I did, and not become cold about it, because those are two different things and the trick is doing the first one without the second.

Is there anything you want to say to the person reading this?

Yes. And I think I know who is reading this.

You are at your desk. You have this open in a second tab and you have glanced at the door twice since you started. You have read this far because something small happened in the last fortnight that you have not been able to put down. A meeting that got moved. A document somebody asked you to write. A question in a corridor.

I am not going to tell you it is nothing. I do not know, and every single person who told me it was nothing turned out to be wrong.

But I will give you the only thing I have that is worth anything.

You are not the job.

I know how that sounds. It sounds like a fridge magnet. I thought it was a fridge magnet too, for fourteen years, and then I found out it was load bearing, and I found out the hard way, on a Tuesday, in a clean kitchen.

Put the numbers in your phone tonight. It takes four minutes.

Then close the laptop and go and be with whoever is in your house.

Last question. Is there anything good?

One thing. And I feel ridiculous offering it after everything I have just said to you.

We went back to the coast in April. Same place. Off season, so it was half the price, which is why we could go at all, and the water was absolutely freezing.

The youngest asked me to come in on the first morning.

I said yes before the sentence was finished. I did not say in a bit.

I have not said in a bit to that child once since October and I am not going to say it again as long as I live.

I was in that sea for forty minutes. I could not feel my legs. It was the best I have felt since any of this started, and I want to be careful here because I am not telling you it was worth it, it was not worth it, I would take the job back tomorrow.

But I was in the water. I have that. I know exactly how that sounds and I do not care.

What I Have Done With This

Most of them are still looking. Some took something smaller and are quietly ashamed of it and should not be. Two found something better, and both of them told me so in a way that sounded almost like an apology, which I did not understand until I sat with it for a while.

Two of them cried while we were talking. Both of them apologised to me for it.

Apologised. To me. A person with a job, holding a notebook, who had asked them to go back through the worst thing that has ever happened to them so that I could write it down and put my name on it.

I did not know what to say either time. I still do not. I said it was alright, which is what you say, and it is not enough, and neither of those two moments has left me since.

I am not going to give you an ending, and it is not for effect. When I went back through my notes looking for one, the endings turned out to be the least interesting part and they were all different. It was the middles that were identical. The middles were the same story told to me nine times by people who had never met each other, and none of them knew that, and every one of them thought it had been something particular to them.

One of them said a thing I have not been able to put down. In the first month they went looking for something that told the truth about it, and everything they found was either a corporate statement or somebody selling a course on resilience. Not one honest account of what it actually does to a person. Then they said, and I wrote it down at the time because of how it was said: if you ever write it, write it for somebody at three in the morning.

So that is who this is for. Not for the people who feel safe. For the one person reading this in a clean kitchen at three in the morning, who is going to think it is only them.

And now the part I have rewritten more times than anything else here, and very nearly left out.

Go back and look at the sixth signal. Consultants on site in June, doing an assessment.

I have been that consultant.

Not at that company, because that company does not exist. But I have walked into buildings with a badge on a lanyard and spent a week working out how everything fits together. I have sat down with the person who knows the system inside out and asked them careful, intelligent questions, and they answered every one of them fully, because people in this profession are almost always helpful and it is the best thing about us.

Then I wrote a document. Then I was paid. Then I left, and I did not find out what happened next, because nobody tells the consultant what happened next.

I am not saying any assessment of mine was ever used that way. I do not know that. What I know is that I would have no way of knowing, and that in all those years it never once occurred to me to wonder.

So this is not a piece where I get to stand outside the thing and point at the people who did it. I am somewhere inside the machinery of it, and I cannot tell you exactly where, and that has cost me some sleep while writing this.

What it has changed in my own work is smaller than it should be, and I will be honest about that too. I have started asking a different question in client engagements. Not whether the team is being trained. Whether anybody has been asked how quickly they could be replaced. It is a rude question. I ask it anyway now, because it is the one that turned out to matter and it is the one nobody was listening for.

The uncomfortable thing underneath all of this is that none of it was done by a machine. Every decision here was made by people, in rooms, who then reached for the word AI because it made the sentence easier to say out loud. That is the argument running through my book AI: Nobody’s in There. But we’re still in here. All thirty essays are free to read at pinaldave.com. If you would rather hold a copy, it is on Amazon in paperback, Kindle and audiobook.

If you are the person in the kitchen: it was not your fault, the shame you are feeling is a liar, and somebody should message you in week five. If nobody has, I am sorry, and you can consider this the message.

And if you are reading this feeling safe, please notice that they were too, four months ago, holding a review that called them critical to continuity of operations.

This was never a story about a machine replacing a person, it is a story about a decision that borrowed a machine’s name so nobody would have to sign it.

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on They Finished the AI Training. Eleven Days Later, the Job Was Gone

I want to say something before the technical part, because it is the reason I wrote this at all.

The most satisfying work of my career has not been tuning a query. It has been walking into a room where a team is quietly terrified of their own database, and walking out three days later with them arguing confidently about it. That shift is the whole job. It is also the thing I did not expect AI to help with.

These nine are not the usual list. Nobody here is asking a chat window to write a SELECT. Every one of these is a workflow we designed together, tested, broke, and rebuilt, and every one of them solved a problem that had been sitting untouched for years because it was too big and too boring for anybody to start.

A quick note. Everything here is blended across many engagements and reshaped so that no client, person, or system is identifiable. Table names, numbers, and details have been changed.

1. Heterogeneous Migration, But for the Business Rules Instead of the Syntax

A client was moving off Oracle. Roughly 800 PL/SQL packages, about 400,000 lines, written over nineteen years.

Every migration tool on the market converts syntax. That is the easy half and it is largely solved. The hard half is that nobody in the building could tell you what the code decides. Somewhere in those packages were the actual business rules of the company, and the only copy of them was the code itself.

So we stopped converting and started extracting. Package by package, the question was not how do I write this in T-SQL. The question was what business decisions does this code make, and under what conditions.

The output was a rules inventory. Plain sentences, one per rule, with the package and line number beside it.

If the customer is in the wholesale tier and the order total is above 50,000, apply an additional 2 percent discount, unless the order contains any item from product group 14.

Then we did the part that mattered. We printed the inventory and put it in front of the business people.

They did not recognize about sixty of the rules. Some were obsolete promotions from 2009 that had never been switched off. A few were things the business genuinely wanted and thought they had lost years ago. And two of them were quietly wrong and had been quietly wrong for a very long time.

The technical detail that made it work. Feeding whole packages produced vague summaries. We got useful output only after splitting by procedure and passing the referenced table DDL alongside the code, so the model could see that P_CUST.TIER_CD was a three character code with a check constraint rather than a free text field. Context about the data changed the quality of the reasoning more than any prompt wording did.

The failure mode to watch. It will state intent it cannot know. It will tell you a block exists to handle tax exemptions when the block actually exists because a batch job used to fail at midnight. Every rule in that inventory got verified by a human against real data before anybody trusted it. Every single one.

That project had been on the roadmap for four years. The rules inventory took eleven weeks. I am still a little proud of that one.

2. Reverse Engineering a Vendor’s Closed Schema

This client ran a critical application from a vendor who provided no schema documentation. Not restricted documentation. None. The contract said the database was an implementation detail.

Their tables looked like T_ACCT_MSTR_01. Their columns looked like FLG_3, DT_02, and the immortal USR_DEF_5.

The team needed to report on this data. They had been guessing for six years.

What we built was a structured guessing machine, and the structure is what makes it work. For each table we assembled five things and fed them together:

The DDL, including constraints and defaults, because a check constraint of IN ('A','C','P','X') tells you more about FLG_3 than the name ever will.

The foreign key graph from sys.foreign_keys, because knowing that a table hangs off the invoice header narrows the search enormously.

Value distributions, not raw data. SELECT FLG_3, COUNT(*) FROM T_ACCT_MSTR_01 GROUP BY FLG_3. Nothing sensitive leaves the building, and a column that is 94 percent N and 6 percent Y is obviously a flag.

The vendor’s own views and report definitions. This one is free and almost everybody misses it. Vendors write cryptic tables and then write readable views on top of them, because their own developers cannot remember what FLG_3 means either. Pull everything out of sys.sql_modules and search it for aliases. SELECT a.ACCT_NM AS [Customer Name], a.DT_02 AS [Completion Date] is the documentation the contract said you could not have. They wrote it in the database instead of a PDF.

An Extended Events capture of one screen. Start a session filtered to the application’s login, have a user open exactly one screen and save exactly one record, stop the session. You now have the precise set of tables and columns that screen touches, tied to a screen you can name out loud. It also catches every column the interface writes and never displays, which is where the interesting ones hide.

With all five, the hypotheses were genuinely good. DT_02 is always populated within two days of DT_01 and never null when FLG_3 is C, so it is likely the completion date.

Then came verification, which is not optional and is the part everyone wants to skip. Change one value in the application, watch which column moves. We did that for 340 columns over about three weeks.

What they have now. A 60 page data dictionary for a database their vendor refuses to document. They maintain it themselves. When the vendor ships an upgrade, they diff the schema and re-run the process on whatever is new.

I will admit I enjoyed handing that document over more than was strictly professional.

3. Decoding a Twenty Year Old Foreign Language ERP

Related to the last one but harder, and this is the one that surprised me most.

The system was a German ERP from the early 2000s. Column names were German, and not full German words either. Abbreviated German. KNDNR. BSTDT. LIEFSPERRE.

The team was in three countries and nobody read German.

This turned out to need two distinct passes, and running them together produced worse results than running them apart.

Pass one, expansion. What is the likely full German word behind this abbreviation, in a business software context. KNDNR expands to Kundennummer. LIEFSPERRE expands to Liefersperre.

Pass two, domain meaning. Given the expansion, the table it lives in, and its value distribution, what does this represent in an order management system. Kundennummer is the customer number. Liefersperre is a delivery block, which in practice is a flag that stops shipment when a customer is over their credit limit.

That second pass is where the value is. A translation dictionary gives you the word. It does not tell you that this particular flag is why the warehouse team has been complaining for two years about orders that vanish.

The gotcha. German compound nouns are ambiguous when abbreviated, and it will pick one meaning and commit to it with total confidence. We had a column confidently identified as a delivery date that turned out to be a delivery week. Two rows of test data caught it. Nothing else would have.

4. M&A Due Diligence Schema Mapping

Two companies merge. Both have customers. Both have a customer table. Somebody has to work out how they fit together, and usually that somebody has ninety days.

We used AI for the first pass mapping. Column by column across both schemas, with three outputs required for every proposed match: the target column, a confidence level, and the specific reason.

Requiring the reason changed everything. A match justified by “both are named CUST_STATUS” is a weak match and now it looks weak on the page. A match justified by “both are nvarchar(10), both have a check constraint listing the same four values, both are referenced by the invoice table” is a strong match and it also looks strong. The confidence level on its own was useless because it was always high. The reason column is what let humans triage 900 columns in a week.

The finding that paid for the whole exercise. Both companies had a column called CUSTOMER_STATUS. Both used the value A. In one company A meant Active. In the other, A meant Archived.

A straight merge on matching names would have marked several thousand dead accounts as live customers, and the first anyone would have known about it is when the marketing emails went out.

The tool did not catch that, by the way. It proposed the match at high confidence. A human reading the reason column noticed that the value distributions were mirror images of each other and went looking. That is exactly the workflow working correctly. The machine narrows 900 columns to 40 worth arguing about, and the humans argue about the right 40.

5. Compliance Documents Turned Into Actual SQL Checks

A regulated client had an audit requirement document. Three hundred pages of prose, written by people who have never opened SQL Server Management Studio, describing controls that a database must satisfy.

Every year, a team of people read that document and manually checked things. It took six weeks and it was miserable and it was error prone because it was miserable.

We converted it into a test suite.

Section by section, the question was: what would you have to query in SQL Server to prove or disprove this requirement. The output was a set of checks, each one a query that returns zero rows when compliant and returns the offending rows when not.

A requirement that privileged access is restricted to approved personnel became a query against sys.server_role_members and sys.server_principals, joined to their approved list table, returning anybody who should not be there.

Encryption at rest became a check on sys.databases.is_encrypted alongside sys.dm_database_encryption_keys for state 3.

Backup retention became a query against msdb.dbo.backupset proving a full backup exists within the required window for every production database.

What made it credible to the auditors. Every check carries the section number of the requirement it came from, in a comment at the top of the query. When an auditor asks how you know you comply with 7.4.2, you run one query in front of them. That traceability is why they accepted it.

What it could not do. Maybe a third of the requirements are not testable in SQL at all. Things about staff training, approval processes, and physical access. It happily proposed queries for a few of those, and the queries were nonsense dressed as diligence. Those stayed manual, which is correct.

Six weeks became two days and a report. I have watched a compliance officer smile exactly once in my career and it was during that demo.

6. Column Level Data Lineage Across Forty Procedures

The question sounds simple and it is not. Where does this column come from, and what touches it.

A finance team had a number in a report. Nobody could say with certainty how it was calculated. It passed through views, procedures, and a couple of SSIS packages, and it had been that way since before most of the team joined.

SQL Server gives you a start. sys.dm_sql_referencing_entities and sys.sql_expression_dependencies will find static references. That gets you the skeleton.

Then it stops, because that shop used dynamic SQL, and the dependency views cannot see inside a string.

So the workflow was: pull every module definition out of sys.sql_modules, use the built in views to get the static dependency graph, then hand the actual code plus the graph over and ask what happens to this specific column at every hop. Read, written, aggregated, filtered, transformed.

Reading inside the dynamic SQL is the thing only this approach could do. A string being concatenated together is just text to SQL Server. It is also just text to a language model, and a language model is quite good at text.

What they found. The number was being rounded twice. Once to four decimal places in a view, then again to two in the procedure that consumed the view. On most rows it did not matter. On a small number of rows it produced a one cent difference, and that one cent had caused a reconciliation argument every month for years.

Nobody had found it because finding it required one person to read forty objects in one sitting while holding the whole chain in their head. That is not a hard task. It is an unpleasant one, and unpleasant tasks wait forever.

7. The SQL Agent Job Graveyard

Fifteen years of accumulated SQL Agent jobs. Two hundred and forty of them on one instance.

Everybody has this. Nobody deletes jobs, because deleting a job feels like the sort of thing that gets you a phone call, and leaving it running feels free. It is not free, but the cost is spread thin enough that nobody argues about it.

We pulled three things together for every job. The step commands from msdb.dbo.sysjobsteps. The execution history and durations from sysjobhistory. The schedule from sysschedules.

Then we asked for a classification of each job into one of four buckets: clearly still needed, clearly dead, unclear, and actively broken but failing silently.

That last bucket is the interesting one and it is bigger than you want it to be.

What turned up. Eleven jobs writing to tables that no longer existed, succeeding every night because the step was wrapped in a TRY block that swallowed the error and reported success. Six jobs copying files to a share that was decommissioned in 2019. Four jobs that ran an index maintenance script against a database that had been dropped, and one job whose only step was to call another job that had been disabled seven years earlier.

And one genuinely alarming discovery. A job that emailed a report to a distribution list containing two people who had left the company, going to addresses that still resolved to a mailbox nobody was reading.

The rule we set. Nothing gets deleted. Everything suspicious gets disabled with a note in the description saying who disabled it, when, and why. If nobody complains in ninety days, then it goes. That rule is not sophisticated but it is the reason the project actually finished, because it removed the fear that had been preserving 240 jobs in amber.

8. Explaining a Query Plan Regression After a Version Upgrade

This is the most technical one on the list and the one where AI is closest to being genuinely expert, because the problem is well documented and the inputs are structured.

A client upgraded and a handful of queries got dramatically slower. Same hardware, same data, same indexes, same code. Everybody’s least favorite kind of problem, because nothing you changed is what broke.

The workflow was to capture both plans, the good one and the bad one, and feed both together with a specific question. Not why is this slow. What is structurally different between these two plans, and what optimizer behavior change would explain the difference.

That framing matters. Asked to tune the query in isolation it gives you generic advice about indexes. Asked to compare two plans it does something much more useful, which is diffing.

What it identified correctly. The join order had changed and the estimates behind the change came from the newer cardinality estimator. The old estimator assumed correlation between predicates on the same table. The newer one assumes more independence. On a table where two columns are heavily correlated, and in this case city and postal code, that assumption produces a much smaller estimate, and a much smaller estimate produces a nested loop where a hash join belonged.

It then correctly listed the options. Test with LEGACY_CARDINALITY_ESTIMATION at the database scope, or per query with a USE HINT, or force the old plan through Query Store, or fix the estimate properly with filtered statistics.

Where it was wrong. It recommended a database wide legacy setting first. That would have fixed six queries and quietly pessimized several hundred others that were happily faster on the new estimator. The correct answer was Query Store plan forcing for the specific queries while the estimates got fixed properly.

It knew the mechanism. It did not know the blast radius. That is a good one line summary of this entire technology.

9. Dialect Drift Verification After a Migration

Back to migration, because this is where the last one bites you and it is the section I would keep if I could only keep one.

Your migration completes. Row counts match. Checksums match. Everyone signs off.

Three weeks later a report is subtly wrong, and it is wrong in a way that took three weeks to notice, which is the worst way for a thing to be wrong.

The queries were converted correctly. They are valid on the new platform. They just do not mean quite the same thing anymore.

So instead of asking for a conversion, we asked for a list of every semantic difference between the source and target platform that could affect this specific query. That list became a differential test suite, run against both systems, output compared.

The categories that catch people.

NULL ordering. SQL Server sorts NULLs first on an ascending sort. PostgreSQL sorts them last. Nothing errors. Your TOP 10 is just a different ten rows.

Collation and case sensitivity. A case insensitive SQL Server collation moving to a case sensitive target turns WHERE status = 'active' into a query that silently returns nothing when the data says Active.

Empty string and NULL. Oracle treats the empty string as NULL. SQL Server does not. Every IS NULL and every = '' in the entire codebase is now a question rather than a fact.

Integer division. 5/2 is 2 in SQL Server and 2.5 in MySQL depending on configuration. Financial calculations built on integer columns quietly change answer.

Date arithmetic. Adding a number to a date, week numbering, what the first day of the week is, how a fractional interval rounds. All of it varies and none of it errors.

Implicit conversion order. Comparing a string to a number gets resolved differently across platforms, and the difference shows up as a performance change or a result change depending on the day.

The pattern in every one of those is the same. No error message. No failed row count. Just a different answer, arriving confidently, discovered by a customer.

The test suite for that client had 180 cases. Nineteen of them failed on the first run. Nineteen bugs that would each have been found in production, individually, over about a year, by somebody angry.

The Thread Running Through All Nine

Look at what these have in common, because I did not see it until I wrote them out in one place.

Not one of them is a hard problem. Every single one is a large, tedious, low judgment reading task that a competent person could do perfectly, given three months and no interruptions, which is a resource that has never existed in any company I have ever visited.

These projects were not blocked on skill. They were blocked on tedium. Reading 800 packages, 240 job definitions, 900 column pairs, 300 pages of compliance prose. The work was always possible. It was just never worth starting, because it would never be finished.

What changed is not that the machine got smart. It is that starting got cheap, and these were all problems that only needed somebody to start.

And in all nine, the shape is identical. The machine reads at volume and proposes. A human verifies and decides. The moment anyone inverted that order, the project produced a beautiful document full of confident fiction.

The Part I Am Actually Proud Of

None of these clients bought a product. There was no platform, no license, no vendor with a booth at the conference.

What they got was a workflow, a verification step they were not allowed to skip, and a written note about where the thing lies. Their own people run all of it now. I am not needed for any of it, which is exactly how a consulting engagement is supposed to end and almost never does.

One of them told me the real change was not the time saved. It was that his team had stopped being frightened of the old system. They had a document now. They could argue about it. Being able to argue about your own database is an underrated form of wealth.

The question underneath every one of these projects was the same. Who is holding the judgment when the output looks perfect. That question sits behind all thirty essays in my book AI: Nobody’s in There. But we’re still in here. Every essay is free to read at pinaldave.com, and there is a paperback on Amazon if you would rather hold something real.

My consulting work is now taking a broader route. I continue to help teams solve difficult SQL Server performance problems through the Comprehensive Database Performance Health Check, and I am also helping businesses identify practical ways to enable AI in their daily work. This is not about adding AI because it is fashionable. It is about finding a valuable problem, building a workflow with clear verification, and giving the team the confidence to run it themselves. If your business has an old system nobody fully understands, a manual process that consumes weeks, or an AI idea that needs a safe and useful starting point, this is exactly the kind of consulting work I am now excited to take on.

This is not a story about AI understanding your database, it is a story about AI reading it fast enough that you finally can.

Reference: Pinal Dave (https://blog.sqlauthority.com/), AI for SQL Server, X

First appeared on Nine Unusual Ways My Clients Use AI With SQL Server

AI training at work is the most requested thing I hear about now, and almost nobody I meet has actually had any. I finished a session last month and a man waited until the room emptied before he came up to ask me something. That usually means the question is not about indexes.

He asked when his company was going to train them on AI.

Not whether. When.

A quick note. The people in this piece are composites, blended and reshaped so that no individual is identifiable. Nothing here refers to any specific person or organization.

He Was Not Being Lazy, and That Is the Interesting Part

My first instinct was the unkind one, and I am not proud of it. Learn it yourself. That is what the rest of us did.

Then we talked for a while and the unkind answer fell apart.

He had been at the same company for eleven years. In that time he had taught himself replication, then Always On, then two migrations he described in a way that made me wince in sympathy. Nobody had trained him on any of it. There had been no course, no rollout, no slide deck with a stock photo of a lighthouse on it.

So here is a man with a documented history of learning hard things without being asked to. And on this one thing, he is standing still.

That is not laziness. That is a signal. Something about this one is different, and it is worth working out what.

Every Other Skill Arrived Without a Warning Label

Here is the difference, and once you see it you cannot unsee it.

Nobody ever sent an email saying do not learn replication. There was no policy about indexes. When he stayed late reading about availability groups, the worst thing that could happen was that he was tired the next day.

AI did not arrive like that. For most people it arrived attached to a rule.

Sometime in the last two years, a lot of companies sent a version of the same email. Effective immediately, do not paste company data into these tools. Some went further and blocked them at the firewall. The email was usually correct, by the way. Somebody had already pasted a customer list into a chat window and everyone found out about it at the same meeting.

Every other skill he ever learned was neutral. This one had a memo about it.

Then came the part nobody talks about. The memo was never repealed.

Bans Get Abandoned. They Almost Never Get Withdrawn.

Watch what happens to a corporate ban over eighteen months.

Month one, it is enforced. Month four, the enterprise license gets signed and three teams are quietly using it. Month nine, an executive demos it on stage at the town hall. Month fourteen, there is a slide about AI in the annual strategy.

And in month eighteen, the original email is still the last written word on the subject.

Nobody ever sends the follow up. There is no memo saying the thing we banned is now encouraged, please carry on. That memo would require somebody to sign their name under a reversal, and reversals are the least popular document in any organization.

So the ban does not end. It just stops being enforced. And the difference between those two things is invisible from an executive floor and enormous from a desk.

The man in my session was not waiting for a course. He was waiting to be told he was allowed.

What Your Organization Genuinely Owes You

I want to be fair here, because the loud version of this argument is that everyone should stop complaining and go learn on Sunday, and that version is both smug and wrong.

If a company expects you to use a tool, the company owes you something. Four things, in fact, and none of them are a course.

A written yes. One sentence is enough. You may use these tools for these things, not for those things. Nobody needs a policy document with eleven appendices. They need one line they can point at when somebody asks what they think they are doing.

A place where mistakes are cheap. A sandbox, an anonymized copy, a scratch database that nobody bills for. You cannot learn where a tool goes wrong if the only place to find out is production.

Time that is actually protected. Two hours a week in the calendar, defended like a meeting with a client. Learning time that anyone can book over is not learning time. It is decoration.

Somebody senior being visibly bad at it in public. This one costs nothing and is worth more than the other three combined. People do not experiment when the only visible users are the ones who are already good.

Ask for those. They are reasonable, they are cheap, and asking for them is a completely different act from waiting.

And the Thing It Cannot Give You

Now the harder half.

Your organization can buy you a license, a course, a policy and a sandbox. It cannot buy you the hours. And the hours are the actual skill, because the tool is the one thing everybody already has.

Here is what I mean, and it happened to me.

I asked for help tidying a stored procedure that was slow. Inside it was a cursor walking a table row by row, updating a running balance. The suggestion that came back replaced the whole thing with a single set based UPDATE. Shorter, cleaner, faster, and I would have approved it in a code review without blinking.

It was also wrong. The cursor was accumulating. Each row depended on the row before it. The rewrite calculated every row against the same starting balance, so the numbers were tidy, fast, and completely fictional.

The tool was not confused about SQL Server. It was right about SQL Server. It was wrong about what the code was for. It had no way of being right about that. There is nobody in there to wonder why a person would write a cursor on purpose.

A course teaches you what the tool does. Only the hours teach you when it is lying to you with a straight face.

No training department can hand you that. It is not a module. It is a scar.

Who Do You Think Is Going to Write the Training?

This is the part I wish I had said to him properly at the time.

Your company will eventually run AI training. There will be a deck. There will be a session, probably on a Thursday, probably too long.

Somebody is going to build that deck. It will not be a vendor, because the vendor does not know what your systems are called. It will be somebody inside your company who already knows where the tool helps and where it quietly produces beautiful nonsense.

That person is learning right now. On a laptop. Without a memo. Being slightly wrong in private, on purpose, where it does not cost anything.

The training you are waiting for is currently being paid for by somebody who did not wait.

What I Would Actually Do on Monday

Send one email and ask for the line. Not a policy. A line. What am I allowed to use this for, and what is off limits. Whoever replies will be relieved somebody finally asked, because they have been wondering the same thing and outranking you does not make it clearer.

Practice on things that are not yours. Sample databases, public data, that side project you abandoned in 2019. You need reps, and reps do not require permission when there is nothing confidential in the room.

Keep a note of every time it was confidently wrong. Two lines each. What you asked, what it produced, why it was wrong. After twenty of those you will have something no course contains, which is a map of the failure modes in your own domain.

Then show somebody the list. Not the wins. The list of failures is more persuasive than any demo, and it is the fastest way to become the person they ask to build the training.

The Honest Version

I do think organizations are being unhelpful. The silence is real, it is cowardly, and it is costing people time. If you feel stuck because nobody has told you where the line is, you are not imagining it and it is not your fault.

But waiting is still a decision, and it is one with a price, and the price is paid entirely by you.

None of this is really about a tool. It is about who holds the judgment when the answer looks perfect and is wrong about the running balance. That question sits behind every one of the thirty essays in my book AI: Nobody’s in There. But we’re still in here. Every essay is free to read at pinaldave.com, and there is a paperback on Amazon if you would rather hold something real.

The man from that session emailed me a few weeks later. He had not been given any training. He had, however, started keeping the list of things it got wrong. He said it was now four pages long and that he had begun to enjoy it.

Nobody is coming with a certificate. That has been true of every useful thing you ever learned, and you already knew how this works.

This is not a training problem, it is a permission problem, and permission is the one thing you were always able to give yourself.

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on AI Training at Work: Nobody Is Coming

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

SQL Server resolves the cycle by choosing one transaction as the victim, rolling it back, and returning error 1205. The surviving transaction can then continue.

The two transactions

Demo compatibility: This demo is intended for SQL Server versions or databases where optimized locking is disabled. On SQL Server 2025 and Azure SQL platforms, optimized locking may prevent this exact pattern from reproducing.

Run this setup in a disposable test database, never production.

-- Run once in a disposable test database.
CREATE TABLE dbo.DeadlockDemoAccounts
(
    AccountID int NOT NULL PRIMARY KEY,
    Balance int NOT NULL
);

CREATE TABLE dbo.DeadlockDemoOrders
(
    OrderID int NOT NULL PRIMARY KEY,
    Status varchar(20) NOT NULL
);

INSERT dbo.DeadlockDemoAccounts (AccountID, Balance)
VALUES (1, 1000);

INSERT dbo.DeadlockDemoOrders (OrderID, Status)
VALUES (1, 'Open');

Open two query windows in the same database. Run each numbered step in order.

-- Step 1, in session 1.
BEGIN TRAN;
UPDATE dbo.DeadlockDemoAccounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Step 2, in session 2.
BEGIN TRAN;
UPDATE dbo.DeadlockDemoOrders
SET Status = 'Cancelled'
WHERE OrderID = 1;

-- Step 3, back in session 1. This batch blocks.
UPDATE dbo.DeadlockDemoOrders
SET Status = 'Paid'
WHERE OrderID = 1;
IF XACT_STATE() = 1 COMMIT;
IF XACT_STATE() = -1 ROLLBACK;

-- Step 4, back in session 2. This completes the cycle.
UPDATE dbo.DeadlockDemoAccounts
SET Balance = Balance + 100
WHERE AccountID = 1;
IF XACT_STATE() = 1 COMMIT;
IF XACT_STATE() = -1 ROLLBACK;

One session receives error 1205. The other update resumes and commits. Victim selection depends on deadlock priority and estimated rollback cost, so either session can be selected.

After both sessions have finished, clean up the two demo objects.

DROP TABLE dbo.DeadlockDemoOrders;
DROP TABLE dbo.DeadlockDemoAccounts;

Each UPDATE protects its uncommitted change from conflicting writers. Without optimized locking, this commonly appears as an X key or row lock held until COMMIT or ROLLBACK. With optimized locking, row and page locks can be released earlier, while a transaction ID lock protects the uncommitted change. The logical result is still that another writer cannot modify the same row until the transaction finishes, although the deadlock behavior and graph can differ.

Build the deadlock

Run one step from each session, then request the second row from both. Three pictures cover the whole thing.

Open the interactive version if you would rather click through it yourself and watch the cycle form.

State 1. Each session protects one row. Session 1 has updated Accounts. Session 2 has updated Orders. Both hold a lock, neither is waiting, and nothing is wrong yet.

State 2. Each session asks for the row the other one is holding. Session 1 requests Orders. Session 2 requests Accounts. Both are now blocked, and each is blocked by the other.

This is the deadlock. It is a cycle, not a slow query. No amount of waiting fixes it, because the only thing that could release either lock is the transaction that is waiting on the other one.

State 3. The lock monitor finds the cycle and breaks it. SQL Server picks a victim, rolls it back, and returns error 1205. The survivor gets both rows and continues.

Look closely at the victim. Its first UPDATE had already succeeded. It is undone anyway, because a rollback undoes the whole transaction and not only the statement that was blocked.

Victim selection depends on deadlock priority and estimated rollback cost, so either session can be chosen. Do not write code that assumes it is always the other one.

Change one thing and the cycle cannot form

Both sessions take the rows in the same order. Accounts first, then Orders. Session 2 still gets blocked, but look at the graph.

One arrow. A cycle needs the arrow to come back, and it never does. Session 1 finishes, releases both rows, and Session 2 carries on.

Blocking is not deadlock. Blocking can end when the blocking transaction commits or rolls back, but it can also persist indefinitely if the blocker does not release the resource. That is the whole difference, and it is the reason a consistent access order is the first fix to reach for.

What just happened

Session 1 protects its Accounts change and requests Orders. Session 2 protects its Orders change and requests Accounts. Each session is waiting for a resource protected by the other.

SQL Server’s lock monitor searches for cycles. The normal detection interval is five seconds, but it can fall as low as 100 milliseconds when deadlocks occur frequently.

When the lock monitor finds the cycle, SQL Server rolls back one transaction. The victim receives error 1205, severity 13. Its rollback releases the conflicting resource and the survivor continues. This differs from ordinary blocking. Blocking can end when the blocking transaction commits or rolls back, with no victim selected.

Why lowering the isolation level does not fix this example

READ UNCOMMITTED reduces shared read locking by allowing dirty reads. READ COMMITTED SNAPSHOT and SNAPSHOT use row versions for qualifying reads. These options can reduce some reader-writer deadlocks, but they do not remove this writer-writer dependency.

Both sessions still need to protect their uncommitted changes. For this pattern, the fix is consistent resource access order, not a lower isolation level.

Why each transaction looks correct alone

Each transaction performs a reasonable operation when tested alone. The defect appears only when they overlap and acquire the same resources in opposite order. This is why a deadlock must be analyzed as a workload interaction, not as a single failed statement.

Use a consistent access order

The fourth diagram above is the whole fix. Within this two-resource model, matching order prevents the cycle. One session may block behind the other, but it can proceed once the first transaction finishes.

Apply the same rule across related stored procedures, triggers, cascading actions, and application code. A convention such as parent before child can help. Confirm the actual resources and access paths in the deadlock graph.

Retry the complete transaction

SQL Server rolls back the victim’s entire transaction, including statements that completed before the deadlock. Handle error 1205 with a bounded retry, backoff, and jitter. Retry the complete transaction and ensure the business operation is safe to repeat.

Read the deadlock graph

On SQL Server and Azure SQL Managed Instance, the built-in system_health Extended Events session starts automatically and records detected deadlocks. Its event files roll over, so it preserves recent history rather than an unlimited archive. Azure SQL Database does not include this built-in session.

SELECT CAST(event_data AS xml) AS deadlock_graph
FROM   sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE  object_name = 'xml_deadlock_report';

Each returned event contains the deadlock XML, including the participating processes, requested and owned resources, execution context, and victim information. On SQL Server 2019 and earlier, reading the files requires VIEW SERVER STATE. On SQL Server 2022 and later, it can require VIEW SERVER PERFORMANCE STATE or VIEW DATABASE PERFORMANCE STATE.

Use the graph to identify the cycle before changing indexes, isolation levels, or transaction code.

What the demo leaves out

• Rows are shown as simply held or free. Real locking has multiple modes and resource types, plus intent locks and possible escalation. With optimized locking, transaction ID locks can also appear.
• Detection here is instant. SQL Server normally starts with a five-second detection interval, but can detect much sooner when deadlocks occur frequently.
• The demo estimates rollback cost from the number of completed steps. SQL Server checks DEADLOCK_PRIORITY first, then estimated rollback cost, and can choose randomly when both are equal.
• Real deadlocks are not limited to two sessions or to rows. Three or more sessions can form a longer ring, and the resources can be pages, keys or memory grants.

Production checklist

Document a resource order. Apply it across every transaction that touches the same objects.

Keep transactions short. Do not hold database locks while waiting for user input, network calls, or unrelated work.

Catch 1205 and use a bounded retry with backoff and jitter. Retry the complete transaction, log the failure, and stop after a sensible limit.

Verify collection and retention. system_health is active on SQL Server and Azure SQL Managed Instance, but rollover removes older events. Use a dedicated Extended Events session when longer retention is required.

Do not assume a lower isolation level fixes a writer-writer cycle. Row versioning can reduce reader-writer blocking and deadlocks. It also changes read semantics, and it does not remove the writer dependency shown here.

If recurring deadlocks require broader analysis, consider a Comprehensive Database Performance Health Check.

A deadlock is a workload-level concurrency defect. Two transactions can look correct in isolation and still fail when they acquire shared resources in an inconsistent order.

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on SQL Server Deadlock: Build One With Your Own Hands