A few weeks ago I published an interview with somebody who finished their company’s AI development plan and was let go eleven days later.

I expected messages about the training. Those came.

The other kind I did not expect. There were more of them. They were worse. And every one of them circled the same thing without naming it, so I am going to name it here.

Losing the building was not the painful part.

That came about six weeks later, from a distance, when they finally looked back and saw what had actually been filling each day.

The usual note, and this time it matters more. Everything here is blended across many messages, reshaped so that no person, company, industry or system is identifiable. Titles, numbers and details are changed. And nobody in this piece is a fraud. I want that on the record before you read a word of it, not after.

The Number

I will start with the one I cannot put down.

Somebody wrote to me whose job, for six years, was a weekly number. It went out every Monday to a list of about forty people.

It took most of Thursday and all of Friday morning, because the sources disagreed and reconciling them required knowing exactly where every body was buried.

They knew.

They were good at it. By every measure that existed inside that company, they were excellent at it.

Two months after they were let go, they asked a former colleague to check something. They could not fully explain to me why.

Nobody had asked for the number.

Not once. Eight Mondays. Forty people on that list, and not one had noticed the email had stopped. Or they had noticed, and it had not been worth a message.

They said they sat with the laptop open for a long time.

They said the strange thing was that they were not angry. They were embarrassed, which they knew made no sense. And underneath the embarrassment was something worse.

It was relief.

They did not want to talk about the relief. I understand why. Relief means part of you already knew, and that part has been sitting quietly at the back of every Monday for six years, waiting to be proved right.

Eleven Leads and One Thing Being Led

Another message opened with a sentence I have thought about every day since.

I was the Regional Lead.

They wrote it the way you write something you are proud of. They should have been. It took eleven years.

Two paragraphs later, almost in passing, they listed the other people who sat in the same weekly meeting.

The Global Lead. The Product Lead. The Platform Lead. The Security Lead. The Delivery Lead. The Data Lead. The Governance Lead. The Transformation Lead. The Value Realisation Lead. A Regional Lead for a different region. A Chief of Staff belonging to one of the above.

Eleven job titles. One thing being led.

They wrote: “I counted them on the train home. Then I counted them again, because I assumed I had made a mistake.”

Now here is the part that should worry you, and it is not the part you are expecting.

Every one of those eleven was real. Every one worked hard. Every one of them could have explained to you, in detail, with evidence, exactly why their role existed and precisely how it differed from the other ten.

And they would all have been right.

Because each of those functions was invented on a Tuesday, by somebody sensible, solving a genuine problem that genuinely existed at the time.

Nobody has ever been given the job of removing one.

So they accumulate. A ring of invented functions forms around one real one, the way a pearl forms, slowly and without anybody’s permission, and after fifteen years nobody in the room can tell you which one was the grain of sand.

Which Brings Me to the Uncomfortable Bit

I have gone back and forth on this and landed somewhere I did not expect.

The people were innocent. The ecosystem was not.

You did not invent your title. You applied for it. Somebody described it to you across a table, and you believed them, because why on earth would you not.

You did the work you were handed. You were measured against objectives you did not write, by a person who did not write them either, using a framework nobody in the building could name the origin of.

You are not guilty of anything.

But you were standing inside something that had stopped being honest with itself long before you arrived, and it needed you to keep standing there in order to keep going.

That is not guilt. It is not innocence either.

There is no word for it, and I think the absence of that word is a large part of why none of these people could say any of this out loud until a stranger wrote a blog post.

The Tuesday You Already Knew

Here is something almost every one of them mentioned, and not one of them mentioned first.

They already knew.

Not fully. Not in a way you could act on. But there was a Tuesday, three or four or nine years ago, in the middle of something ordinary, when a thought arrived fully formed and completely unwelcome.

What am I actually doing.

And you pushed it back down. Instantly. Expertly. The way you push down a thought about your own death, because you had a mortgage and a review coming up and two people at home who needed you to be fine.

You did not lie to yourself.

You simply declined to finish a sentence.

You did not lie to yourself. You just chose, every day for years, not to finish one sentence.

I think almost everybody reading this has had that Tuesday. I suspect some of you had it recently.

And I think you did exactly what they did, which is the only sane thing available at the time, which is nothing at all.

Twenty Two Meetings and Four Sentences

Somebody who had been a Senior Manager for Operational Excellence, a real title held by real people in a great many companies, which I have never once heard defined twice the same way.

In their final month, out of an instinct they could not name, they exported their own calendar.

Twenty two recurring meetings. Governance forums. Alignment calls. Steering committees. Working groups.

They went through them one at a time and asked the most dangerous question in corporate life.

In how many of these have I ever actually said anything?

Four.

In the other eighteen they were, in their own word, coverage. Their name on the invite meant the function was represented. The representation justified the function. The function justified the headcount. The headcount justified somebody more senior.

Nobody designed that. I need you to hear that clearly.

It accreted. Like coral. Beautiful, structural, and entirely made of things that used to be alive.

The Deck That Became a Department

Years ago, somebody noticed a real problem. A good observation, made by a person paying attention.

They wrote it up with evidence. It was well received, because it was correct.

They were asked to propose a solution. So they wrote a strategy document. Also well received.

Then came headcount. Two people. Then five. Then a Head of. Then a function on the org chart with a mission statement, quarterly objectives, and a shared inbox.

Somewhere in year three, the original problem was quietly solved by a platform migration that had nothing to do with any of them.

The function ran for another four years.

“I built a department out of being right once, and then spent four years protecting a thing whose reason had already gone. At the time it felt like leadership.”

It does not sound like failure to me. It sounds like almost every organisation I have ever walked into.

Eleven Months of Green

So many people wrote about status reporting that I could have stacked the messages.

A programme reports green. Then green. Then green.

Eleven consecutive months of green, on a dashboard seen by important people.

In month twelve it is cancelled.

Everybody knew by month three.

What none of them could do was be the first to write amber. Amber is not a colour in those meetings. It is an accusation. It points at a specific person’s judgment, and the room hears it that way however carefully you word it.

One of them told me about a colleague who did go amber. Early, honestly, with a plan attached.

That colleague was not punished. That would have been easier to describe.

They were offered support. There was a warm conversation about whether they were overloaded. Somebody senior was added to help them. Their scope reduced quietly over the following two quarters.

Everybody watched. Everybody learned. Nobody ever discussed it.

Green is not a measurement. Green is a social act.

The Boarding Pass

Somebody sent me a photograph of a boarding pass. It took me a moment to understand why, and then I found it almost unbearable.

They had been flown to another country for a two day alignment workshop. Sixteen people. Four flown in. A facilitator. Breakout groups. Sticky notes, first physical, then carefully transcribed onto a digital board so the outputs would be preserved.

They looked that board up recently.

Still there. Read only. Last opened four days after the workshop.

By them.

They were careful to tell me it was not a waste, and I believe them. They met people they went on to work well with for years. Two decisions came out of it that stuck.

Then they wrote this, and it is the line I keep returning to.

“If somebody had told me to stay home and just send an email, I would have argued. I would have argued hard. That is the part I have to sit with.”

The Award

Somebody won an internal award. A real one. Ceremony, photograph, their manager visibly proud in a way they told me they still think about, years later.

It was for delivering a migration ahead of schedule.

They know now, with the terrible clarity that only arrives from outside the building, that they migrated a system almost nobody used onto a platform almost nobody would use. On time. Under budget. Beautifully.

They still have it. They cannot throw it away and they cannot look at it.

So it lives in a box, which seems to me like exactly the right place for most of what this piece is about.

The One That Actually Hurts

Four separate people wrote to me about this, and every single one of them apologised for bringing it up.

You onboarded somebody.

A bright young person, three or four years ago. Genuinely talented. Delighted to be there. Nervous on the first morning in the way that is lovely to see.

And you taught them.

You explained which forum things get raised in. You showed them how to write a status update that is honest and survivable at the same time. You told them, kindly, patiently, with total sincerity, who has to be told before the meeting rather than during it.

You were a good mentor.

That is the terrible part. You were an excellent mentor. Several people have probably told you so.

You handed a twenty six year old the complete operating manual for a machine you had not yet noticed was hollow.

One of them wrote: “They are still there. They are doing my old job now. They are very good at it.”

I could not get that sentence out of my head for a week, and I am not sure I have managed it yet.

What Is Actually Happening Here

The cheap version of this argument is that these people were passengers and the market simply corrected.

That version is lazy, and it is cruel to people who worked extremely hard for a very long time.

Here is what I think is really going on.

Past a certain size, an organisation loses the ability to see value directly. It genuinely cannot tell, from the centre, whether the work at the edges produces anything.

So it measures what it can see. Attendance. Reports. Status. Delivery against a plan it wrote itself.

Activity.

And people, being reasonable, supply activity. Not cynically. Not as a scheme. They supply it because it is what gets noticed, and being noticed is how you keep a job, and keeping a job is how you feed the people in your house.

Every individual behaved rationally. The result was a building full of intelligent, sincere, hard working people producing artefacts for one another.

Manufactured work is not fraud. It is what grows in the gap between what an organisation values and what it is able to measure.

That gap has been widening for twenty years. And now something has arrived that produces artefacts instantly, for free, in unlimited quantity.

The gap is no longer possible to look away from.

The Cruel Part Nobody Warns You About

Several of them worked this out alone, in different countries, at different times. Each thought it was a private discovery.

The skills that kept you employed there do not exist anywhere else.

Knowing which forum to raise something in. Knowing who has to be told before the meeting rather than during it. Writing a status update that is honest and survivable at the same time. Knowing whose sign off is real and whose is decorative.

Those are genuine skills. They are hard won. Several of the people who wrote to me were world class at them.

None of it transfers. Not one item.

It is all local knowledge about one building, and the building is gone.

Which produces the specific cruelty in all of this. The people who were best at surviving that system are the ones it left with the least, and they cannot even name the problem in an interview, because the honest sentence is:

“I was extremely good at something I can no longer describe.”

The Sentence You Lost

And here is a thing that never comes up in a redundancy conversation, because there is no box for it on the form.

You did not only lose a job.

You lost a sentence.

Regional Lead was not a title. It was the sentence your parents used when their friends asked what you did. It was the answer you gave at your cousin’s wedding when somebody asked. It was on a lanyard that your child once wore around the house for an entire afternoon because they thought it was hilarious.

That sentence is gone. There is nothing to put in its place yet.

And you still have to attend things. People still ask.

Nobody prepares you for how much of your identity was living inside a sentence you did not even write.

The Message That Was Not From Somebody Laid Off

One of them still had their job.

It arrived at eleven at night. It was four lines long.

They had read the piece at their desk, twice, and then sat in their car in the car park for a while before driving home. They wanted me to know that. They asked me not to reply, which I have honoured until now.

The last line was this.

“I am not laid off. I am worse. I know.”

If that is you, reading this at your desk right now with the door open behind you, I do not have anything clever. I have only this. Knowing is the hard part and you have already done it. Everything after knowing is just decisions, and decisions are survivable.

The Question I Have Started Asking

I am not going to hand you a framework. Every person in this piece is cleverer than a framework.

But there is one question I now ask clients, and it is rude, and I ask it anyway.

If this person went on leave tomorrow and nobody backfilled them, what breaks, and how long before anybody outside this team notices?

The point is not to be indispensable. Indispensable people are a risk, and usually an unhappy one.

The point is that somebody should know the answer. In most places I visit, nobody has ever asked.

And the version for you, on a Monday, is smaller and harder.

Open last week’s calendar. Find the one thing you did that would still have been worth doing if the company had not existed.

If you find it, protect it. That is the part that travels with you.

If you cannot find one, you have not learned something shameful about yourself. You have learned something about the shape of the room you are standing in.

And it is very much better to learn that on a Monday morning with a coffee than eight weeks after somebody takes your laptop.

The Thing They All Did Without Noticing

I want to end somewhere kinder, and I can, because there is something in these messages that surprised me and not one of them saw themselves doing it.

Every single person, at some point in their email, stopped talking about the work.

And started talking about a person.

The colleague who quietly covered for them the fortnight their parent was ill. The one who always made two teas without being asked. The one who explained the same thing four times and never once let them feel stupid about it. The Friday messages.

Not one of those was invented. Not one was manufactured. Nobody was ever inserted into a meeting to justify headcount and accidentally produced eleven years of friendship.

The building was hollow. The people standing in it were not.

If you take one thing from all of this, take that one.

The work may or may not have been real. That question is worth asking and you should ask it.

But the person who noticed you were having a bad day and said nothing about it and simply put a coffee on your desk was completely real. They are still real. Nothing that happened to your company changed that.

And you can message them tonight.

This is the question underneath 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, and the book is available in paperback, Kindle and audiobook on Amazon.

One last message, and then I will stop.

It came at the end of a very long email describing a career I would have been proud of, containing real achievements that I could see clearly and the writer could not see at all.

The final line was this.

“I keep telling people I lost my job. I do not think that is what happened. I think I found out.”

This is not a story about people who were not needed, it is a story about a building that could not tell the difference and quietly asked them to prove it every single day.

Reference: Pinal Dave (https://blog.sqlauthority.com/), Layoffs and Meaningful Work, X

First appeared on The Job That Was Never There

I expected a comedy. I have read enough confident nonsense from chatbots about SQL Server to assume I would end up with a post full of terrible indexes and easy jokes.

That is not what happened, and the actual answer is more useful.

AI never connected to SQL Server or executed anything. I gave a general-purpose chat model one query at a time, manually reviewed its suggestions, and applied the candidates in a disposable lab database. This is a case study of that copy-and-paste workflow, not a benchmark of every model and not autonomous index management.

The Rules I Set Myself

One rule mattered more than the rest. AI only saw what a typical copy-and-paste chat receives. The table definitions and the query text. No execution plans, no statistics, no wait stats, no data distribution, and no list of the indexes that already existed.

That is not me being unfair to it. That is the situation almost every person is in when they paste a slow query into a chat window and ask what index they need.

Two honest notes about method. I structured the experiment as a Monday-to-Friday week of queries, but ran it in one sitting. I also started each query without telling the model what came before. Model answers can change over time, so treat this as one observed run, not a permanent score. Every reported result is measured rather than remembered.

Monday. The Blank Slate

A small e-commerce shape. Nothing exotic.

Every table had a clustered primary key and nothing else. No nonclustered indexes anywhere. The data is generated deterministically. The script at the end reproduces the write-cost test rather than the whole six-query benchmark.

Six queries, each run six times, whole set repeated three times, then averaged. Logical reads are stable to the page. Durations wander, so I never trusted a single run.

With only clustered primary keys available, each query relied on at least one clustered index scan. SQL Server was reading far more pages than the selective queries needed.

Tuesday. The Four Easy Ones

I handed over the first four queries, one at a time. Here is the first, and what came back.

SELECT COUNT(*), SUM(TotalDue)
FROM dbo.Orders
WHERE CustomerID = 24680
  AND OrderDate >= '2026-01-01' AND OrderDate < '2026-08-01';

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
    ON dbo.Orders (CustomerID, OrderDate) INCLUDE (TotalDue);

For this isolated query on this data, that was a strong candidate. Equality column first, range column second, the aggregated column included so the query is covered. I would have tested the same shape.

The next three were the same story.

SELECT COUNT(*) FROM dbo.Orders
WHERE Status = 'Pending'
  AND OrderDate >= '2026-07-01' AND OrderDate < '2026-08-01';
-- IX_Orders_Status_OrderDate (Status, OrderDate)

SELECT COUNT(*), SUM(Quantity) FROM dbo.OrderItems
WHERE ProductID = 1234;
-- IX_OrderItems_ProductID (ProductID) INCLUDE (Quantity)

SELECT CustomerID, FullName FROM dbo.Customers
WHERE Email = 'user38217@example.com';
-- IX_Customers_Email (Email) INCLUDE (FullName)

Four queries, four sensible candidates, no drama.

The duration values are averages rounded to one decimal place. Q4 showing 0.0 ms means the average was below 0.05 ms at that display precision, not that SQL Server did no work.

Q1 went from 3,965 pages and 26.6 ms to three pages and 0.1 ms. If a junior DBA handed me that on Tuesday afternoon I would be pleased.

Wednesday. The One It Only Half Solved

Then the query that actually looks like production.

SELECT TOP (10) oi.ProductID, SUM(oi.Quantity * oi.UnitPrice)
FROM dbo.OrderItems oi
JOIN dbo.Orders o ON o.OrderID = oi.OrderID
WHERE o.OrderDate >= '2026-06-01' AND o.OrderDate < '2026-08-01'
GROUP BY oi.ProductID
ORDER BY SUM(oi.Quantity * oi.UnitPrice) DESC;

It asked for two indexes, one on each side of the join.

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
    ON dbo.Orders (OrderDate);

CREATE NONCLUSTERED INDEX IX_OrderItems_OrderID
    ON dbo.OrderItems (OrderID) INCLUDE (ProductID, Quantity, UnitPrice);

The original reply redundantly listed OrderID as an included column in the first index. Because OrderID is the clustered key, SQL Server already carries it in every nonunique nonclustered index. I removed it before testing. Useful recommendation, small human correction.

Reasonable on paper. Reads fell from 7,538 to 3,260 and the query went from 119.8 ms to 80.5 ms.

That is the weakest result of the week, and it is the interesting one. When I checked usage afterwards, every other retained index registered seeks. The Q5 access path registered eighteen scans.

Eighteen scans matched the eighteen controlled executions, but the usage counter was a clue, not a diagnosis. Query text cannot tell you which operator SQL Server actually chose. The actual execution plan can. It can also show that a scan is sometimes the cheapest correct choice, especially when SQL Server must aggregate many rows. A seek is not automatically good and a scan is not automatically bad.

The sixth query was the last one, and it went the way Tuesday had.

SELECT COUNT(*), SUM(TotalDue)
FROM dbo.Orders
WHERE ShipCountry = 'IN' AND Status = 'Shipped';

CREATE NONCLUSTERED INDEX IX_Orders_ShipCountry_Status
    ON dbo.Orders (ShipCountry, Status) INCLUDE (TotalDue);

Two equality predicates, both in the key, the aggregate included. Reads went from 3,965 to 163 and the query from 41.1 ms to 3.8 ms.

All six queries, on a log scale because three pages and 7,538 pages will not share a linear axis. Five collapsed. One did not.

That makes four indexes from Tuesday, two from Wednesday and one from Q6. Seven in total, and that is the set Thursday’s write test ran against.

Thursday. Then I Looked at the Bill

Sixth query in, seven indexes down, everything faster. So I went looking for what I had not asked the chat model to price.

Where the seven landed. Six of them sit on the two tables the insert batch writes to, which is the whole of what follows.

I inserted 20,000 orders and 60,000 order items as one batch, took the median of three runs, and deleted the rows in between. That restores the row count but not the exact physical state, so read this as a strong signal rather than a laboratory benchmark.

This insert batch took 2.9 times as long. My benchmark notes also recorded the space occupied by the four lab tables and their indexes rising from 66 MB to 170 MB. I did not preserve the exact collection query, so that storage figure is context rather than a reproducible measurement.

Not one of those costs appeared in any recommendation. Every answer was about the query in front of it, because the query and the schema were the only evidence I gave it. Ask a model about write cost and it will describe the categories correctly. It cannot price mine without seeing my workload.

Friday. The One I Nearly Misread

A week means new queries arrive. So on Friday I did what everybody does. New slow query, paste it in, ask what index it needs.

SELECT OrderID, Status FROM dbo.Orders WHERE CustomerID = 24680;
-- IX_Orders_CustomerID (CustomerID) INCLUDE (Status)

That is a sensible candidate, and it overlaps an index I already had.

At first I called the new one redundant because CustomerID is the leftmost key of Tuesday’s index. That would have been a lovely ending and a wrong one. Friday’s query also returns Status, and Tuesday’s index does not contain it. OrderID is available implicitly through the clustered key, but Status is not.

Both seek on CustomerID. Only one of them can return Status without going back to the table.

The Tuesday index can seek to the customer and then look up Status. The Friday candidate can cover the query. It is overlapping, not automatically redundant. A narrower covering index might help a frequent query, or it might add seven megabytes and write work for a benefit too small to matter. Only the actual plan, measured reads, query frequency, and write workload can settle that.

This is still a context failure. Nobody showed the model the index list or the workload. It could propose a locally sensible index, but it could not compare that candidate with the rest of the portfolio. It answered the question it was asked, in a room with no windows. My first interpretation made the same mistake.

The Week, In One Line

Across the first six queries and the seven retained indexes, the week took logical reads from 23,870 down to 3,446, and total duration from 290.1 ms down to 84.7 ms. The combined insert batch moved from 592 ms to 1,703 ms. My benchmark notes recorded the space figure moving from 66 MB to 170 MB, with the collection-method limitation described above. The Friday candidate is not included in those retained-index totals because this experiment did not establish that it deserved to remain.

The whole week on one axis. Everything on the left was asked for. Nothing on the right was.

The Good, The Bad and The Ugly

The ugly one is the part that gets worse over time. Each overlapping index can sound defensible by itself while the portfolio becomes harder to justify.

Would I Do It Again

Yes, and I will. With three changes to how I ask.

Paste the complete existing index definitions in with the query. That means keys and included columns from sys.indexes, sys.index_columns, and sys.columns, not merely the index names. This would have turned Friday into a portfolio discussion instead of another isolated recommendation.

Ask what it might cost, then measure what it actually costs. A model can list likely write, storage, and maintenance effects. It cannot price my workload without workload evidence.

Give it the actual plan, not just the query. Wednesday’s scan would have been visible immediately. That would not guarantee a better index, but it would stop anybody from treating a lower read count as the whole diagnosis.

The pattern I keep landing on this year is always the same. Ask AI a narrow question and you can get a useful narrow answer. The danger is treating that answer as a system-wide decision. A database is a collection of tradeoffs, and a stateless chat sees only the evidence you paste into it.

A stateless answer can be correct about one query and still know nothing about the workload surrounding it.

If you want the wider argument about where this technology genuinely helps and where it quietly does not, that is the subject of all thirty essays in my book AI: Nobody’s in There. But we’re still in here. Every essay is free to read in the complete online collection, and there is a paperback on Amazon if you would rather hold something real.

Run the Smaller Write Test

This smaller lab demonstrates the write cost of one candidate index on the Orders table. It does not reproduce the 2.9x result above because it omits OrderItems and six of the seven retained indexes. Everything lives in temporary tables, so the test leaves nothing behind when the session ends.

Build the 300,000-row temporary table first. Then run the timed block six times without the candidate index, discard the first run, and take the median of the other five.

DROP TABLE IF EXISTS #Orders;
DROP TABLE IF EXISTS #Numbers;

CREATE TABLE #Orders (
    OrderID     int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerID  int NOT NULL,
    OrderDate   datetime2(0) NOT NULL,
    Status      varchar(12) NOT NULL,
    TotalDue    decimal(12,2) NOT NULL,
    ShipCountry char(2) NOT NULL,
    Filler      char(60) NOT NULL DEFAULT ''
);

WITH n(x) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
              SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
              SELECT 1 UNION ALL SELECT 1),
     t(x) AS (SELECT 1 FROM n a, n b, n c, n d, n e, n f)
SELECT TOP (300000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
INTO #Numbers FROM t;

INSERT #Orders (CustomerID, OrderDate, Status, TotalDue, ShipCountry)
SELECT 1 + (rn * 7919) % 50000,
       DATEADD(minute, -((rn * 37) % 1051200), '2026-08-01T00:00:00'),
       CASE WHEN rn % 10 < 7 THEN 'Shipped'
            WHEN rn % 10 < 9 THEN 'Pending'
            ELSE 'Cancelled' END,
       CAST(10 + (rn % 90000) / 100.0 AS decimal(12,2)),
       CHOOSE(1 + rn % 6, 'US','GB','IN','DE','AU','CA')
FROM #Numbers;

DROP TABLE #Numbers;

Here is the timed block. The transaction restores the row count even if the test is repeated. The identity value still advances, which is harmless for this temporary lab table and is one more reason not to describe the database as physically identical afterwards.

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRANSACTION;

DECLARE @t0 datetime2(7) = SYSDATETIME();

;WITH NewRows AS
(
    SELECT TOP (20000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
)
INSERT #Orders (CustomerID, OrderDate, Status, TotalDue, ShipCountry)
SELECT 1 + (rn * 7919) % 50000,
       '2026-08-01', 'Shipped', 99.00, 'IN'
FROM NewRows;

SELECT DATEDIFF(millisecond, @t0, SYSDATETIME()) AS InsertMs;

ROLLBACK TRANSACTION;

Now create the candidate index below and run the same timed block six more times. Again, discard the first run and take the median of the other five.

CREATE NONCLUSTERED INDEX IX_Test_Orders_CustomerID_OrderDate
    ON #Orders (CustomerID, OrderDate) INCLUDE (TotalDue);

As a final sanity check, rebuild the temporary table and repeat the comparison in reverse order. Create the index, take the indexed measurements first, run DROP INDEX IX_Test_Orders_CustomerID_OrderDate ON #Orders;, and then take the baseline measurements. This helps expose caching and run-order effects. The comparison answers one narrow question: how much write cost did this one index add to Orders? It does not validate the seven-index, two-table multiplier reported above.

I have run a lot of index reviews over the years, and the ones that go wrong almost never go wrong because somebody chose a bad column. They go wrong because thirty good decisions were made one at a time, by people who could each only see one query.

That is a very old problem. It has just found a very fast new way to happen.

This is not a story about whether AI can suggest an index, it is a story about why a stateless recommendation cannot price a database-wide decision.

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

First appeared on I Let AI Recommend SQL Server Indexes Across a Week of Queries

He said I could use this if it helped somebody. Names and details are changed so nobody is identifiable. The emails are trimmed for length and nothing else.

“Pinal, I dont know why I am writing this to you. Yesterday they called me at 10:40 and the call was nine minutes. I gave the laptop back before lunch. 16 years.

I am not asking you for a job. I want to ask one thing and I cannot ask anyone here. Who is going to do my job now. There is the Thursday call. There is the vendor event next month and I have that whole thing in my head. Nobody asked me to hand anything over. Nobody asked. I keep waiting for someone to call and ask me how it works.

Sorry for the long mail. It is 4:41 here.”

He found me through the piece about the man who finished his company’s AI training and was let go eleven days later. This one is different. This one is waiting for an answer.

What He Did With His Last Afternoon

Here is the part I cannot get past.

They gave him until five to clear his desk.

He did not clear his desk. He wrote a handover document.

Fourteen pages. Which vendor calls you back and which one needs chasing on a Tuesday. Whose yes means yes and whose yes means ask again next week. Why the two systems disagree and which number to trust. The name of the woman at the hotel in October who holds the block booking.

Sixteen years of knowing things, typed out by a man who had been let go four hours earlier, so that whoever came next would not have to struggle.

He sent it to three people at 4:52.

He checked from his phone that night, the way you check a message you should not still be thinking about.

Nobody had opened it.

Not out of cruelty. They were busy, it was late, and he was already gone.

He wrote a goodbye note too. Something short, thanking people. The account had already been closed, so it came straight back to him.

Delivery has failed to these recipients.

Sixteen years and the building would not even take the goodbye.

His Week, Because You Will Recognize It

He described the job well, because he had described it a thousand times at family dinners.

Monday. The 9:00 status call. Forty minutes, eleven people, he speaks for three. Then he forwards the vendor thread with “Looping in Dan for visibility.” Dan does not reply. Dan never replies. Then the tracker, a spreadsheet with a tab called Master and a tab called Master FINAL.

Tuesday. The pre-meeting for Wednesday’s meeting. This is a real thing and everybody reading this knows it is a real thing. Then the deck. Fourteen slides. They will discuss slide four and run out of time.

Wednesday. The buyer is on the call. The seller is on the call. He is on the call. He makes two good points, and they genuinely are good, because he knows where the last three deals went sideways and nobody else does. Somebody says let us take this offline. He says he will set something up.

Thursday. The something he set up. Forty five minutes because thirty was not enough. Six action items with owners and dates, typed up that evening, subject line “Recap and next steps.”

Friday. Chasing the six action items. Four of them come back done. Two of them come back as questions, which means another Thursday.

Some weeks he stayed late to get it right. Nobody asked him to. He did it because his name was on it.

Nothing in that week is a lie. I want that said before the next part.

And if you just read those five days and recognized your own, you are not the only one.

So Who Does It Now

Nobody.

Not a junior, not a tool, not a smaller team. Nobody, because none of it is waiting somewhere to be done.

The buyer will still buy. The seller will still sell. The Thursday call will not get booked, and the thing it was going to unblock will get sorted in a two minute phone call between two people who were on the invite anyway.

Somebody from marketing will stand at the booth. The hotel will release the block booking in September and no human being will be involved.

He already knows this. That is why the message came at 4:41 and not at noon.

The Reminder That Kept Going Off

Years ago he put the Thursday call into his personal phone too, because he never wanted to be the reason a meeting slipped.

They took the laptop. They took the account. Nobody thought about the phone.

So at 1:45 it buzzed in his pocket while he stood in his own kitchen.

Recap and next steps. In 15 minutes.

He let it finish buzzing. He almost joined. He still knew the link by heart.

Then he opened the calendar and looked at the rest of his year. The Thursdays. The quarterly reviews. The event in October.

He deleted them one at a time. It took eleven minutes.

The Mortgage Does Not Pause for Any of This

He mentioned the mortgage once, in a short sentence near the end, the way people mention the thing they are actually afraid of.

So here is what I wrote back, because I do not think he is the only one awake at 4:41.

Do the money math before the meaning math. Count the months you have tonight. A number is survivable. A fog is not.

Then open that handover document and read it as a stranger would. Fourteen pages of judgment that took sixteen years to build. Inside nine thousand people it was invisible. In a company of forty it is a job with a title, nobody is doing it, and it is costing them money right now.

Do not spend the first month building the case that you were needed. That is full time work that pays nothing.

And send those fourteen pages to somebody who will open them. It is the best resume he will ever write, and he wrote it with his hands shaking.

Then He Wrote Back

His second email came in the afternoon. Four lines.

“One more thing and then I will leave you alone. My daughter asked me this morning why I was home. I told her I had the day off. She said good, can we get pancakes. So we got pancakes and she talked the whole time.

I will have to say something different on Monday. I dont know what yet.

Thank you for writing back. You are the first person who answered.”

Fourteen pages. Three recipients. Sixteen years of being the one who made sure nothing ever slipped.

And a stranger on the internet was the first person who answered.

That is the only part of this that is fixable, and it takes ninety seconds.

Somebody you worked with got their nine minute call this month. Their goodbye note bounced too, which is why it never reached you, and you decided they probably wanted to be left alone.

They did not. There is no right thing to say and there never was.

Send the wrong thing. Be the first person who answered.

This is the question underneath all thirty essays in my book AI: Nobody’s in There. But we’re still in here. They are free to read at pinaldave.com, and the book is on Amazon.

He is going to be fine. I believe that, and I told him so, and I am not saying it to make this end nicely.

But somewhere tonight, fourteen pages are sitting unopened in three inboxes, written by a man with nowhere to be in the morning, so the next person would have it easier.

There is no next person. He wrote it anyway.

This is not a story about work that turned out not to matter, it is a story about a man who spent his last afternoon making sure somebody else would not struggle, and that is the part no company ever puts on the form.

Reference: Pinal Dave (https://blog.sqlauthority.com/), Layoffs and Meaningful Work, X

First appeared on Who Is Going to Do My Job Now?

Using historical data to confirm performance improvements starts with one fair question. Did the same work become cheaper under comparable conditions? In an illustrative case, a DBA deploys an index at 9:00 AM and watches average reads fall. The release channel fills with check marks.

Before deployment, the query averaged 42,000 logical reads across 18,400 executions. Afterward, it averaged 6,100 reads across 11,200 executions. Total reads fell 91 percent, but executions fell 39 percent too.

That missing context changes the decision. The team now needs matched windows, stable plans, and comparable parameters. Enough executions must pass before anyone calls the index a success.

One Fast Execution Proves Almost Nothing

A clean test confirms a change for one parameter set. It cannot represent every customer, data distribution, or concurrency pattern. Cached pages and quiet servers can flatter results while hiding blocking, memory pressure, or storage latency.

Start with per-execution duration, CPU, logical reads, writes, and waits. Then add execution count. If executions double after latency halves, aggregate duration stays level. Total CPU and reads still require separate calculations.

Both views matter because users experience individual calls, while servers absorb the complete workload. Application growth may raise daily CPU despite better per-call latency. Modest per-call gains may also save substantial capacity on frequent statements. Report both perspectives instead of selecting the flattering number.

Use a Before-and-After Scorecard

Build Comparable Workload Windows

Compare Monday morning with another normal Monday morning, not Sunday night. Match business cycles, batch schedules, release activity, and expected traffic. Keep important database, hardware, and configuration conditions consistent.

The plan may change when a large customer replaces a small one. Match applications, databases, users, query signatures, and parameter patterns. Note any statistics update or plan change inside either window.

Use several windows when the workload varies naturally. One favorable hour may be noise, while repeated improvements establish a pattern. Include enough executions to limit isolated outliers, and preserve the exact deployment time.

Normalize totals when windows cover different durations, but never hide the raw values. Rates per minute help compare uneven windows, while counts preserve capacity impact. Separate scheduled jobs from interactive traffic when their patterns differ. Otherwise, one overnight process can make a healthy daytime change look unsuccessful.

Read Product History With Context

SQL DM from IDERA charts query history for average duration, CPU, reads, writes, waits, blocking, deadlocks, and CPU per second. Event occurrences add execution-level statistics and SQL text. Now the graph answers the useful question: did the query stay faster during real traffic?

That history still reflects collection settings. Filters, thresholds, disabled monitoring, and retention choices can create gaps. Older query records may be aggregated into daily summaries, which suppresses some statement, client, and user detail. Repository grooming can also remove data beyond the configured retention period.

Use Query Store as a Second Witness

Query Store persists query text, plans, and runtime statistics. SQL Server 2017 and later can capture query-level waits. This historical evidence can connect an improvement with an index, plan, or workload change.

Query Store is not a recording of every execution. Runtime statistics are aggregated into configurable time intervals. Its averages, minimums, maximums, and standard deviations describe each plan within those intervals. Capture policies, cleanup settings, and storage limits determine what remains available.

Compare plan identifiers as well as query identifiers, because lower duration may come from an unrelated new plan. A forced plan or statistics refresh may alter the result. The claim gets stronger when the plan, change, and result share one clear timeline.

Measure the Cost of the Improvement

An index can reduce reads for selected queries while increasing work for data changes. Check insert, update, and delete activity on the affected table. Review index size, maintenance time, logging, lock behavior, and storage consumption. Confirm that neighboring queries did not regress.

Native index usage counters can reveal seeks, scans, lookups, and update maintenance. However, those counters reset after events such as a server restart. Record the observation start time, because a short window may miss monthly reports depending on the index.

Define success and guardrails before deployment, such as lower reads without raising write latency beyond an agreed threshold. Capture the same metrics after deployment for an equivalent business window. Keep a rollback script available until the evidence remains stable.

The Fair Counterargument

Controlled benchmarks can demonstrate causality better than messy production history. A test regression costs nothing, while a production regression costs customers. That argument holds when test data and execution conditions represent production. Laboratory testing makes repeated measurements safer, especially when schema changes carry real risk.

However, controlled tests remove the concurrency, parameter diversity, and operational surprises that often determine production performance. Historical monitoring supplies that missing context. The strongest conclusion combines controlled testing with comparable production windows. Neither source should carry the decision alone.

A Result Worth Keeping

Baselines provide a comparison. SQL DM from IDERA supports a moving seven-day dynamic baseline and fixed custom periods. Choose normal periods and exclude quiet hours that distort expected behavior. A baseline is a reference, not an automatic verdict.

A trustworthy report shows the gain and every reason it might be misleading. Name the change, workload window, execution count, plans, and resource effect. Document competing deployments, missing data, and the period of stable behavior.

The DBA from 9:00 AM should wait through the next comparable peak. If reads stay lower and the guardrails hold, the change has earned its place. Then write the result down, because next quarter nobody will remember the details.

A fast test opens the case. A faster workload earns the decision.

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

First appeared on Using Historical Data to Confirm Performance Improvements

I’ve been answering the same question for twenty years. Somebody has a full backup, a pile of differentials, and a few hundred log backups, and they want to know which ones go back in and in what order.

I wrote it up in July 2009 with a static diagram. That post still gets traffic every single week, which tells you the question never went away.

So this time I’ve animated it, and I’ve split it in two. Full and log backups first, then differentials once the first part makes sense.

Everything here assumes the FULL recovery model. SQL Server does not allow transaction log backups in SIMPLE. Just switched a database from SIMPLE to FULL? Take a full or differential data backup first. That data backup is what starts the log chain.

Start With Just Full and Log

Give it a minute to run all the way through. It loops.

Full and Log Backups, In Plain Words

A full backup gives the restore its starting image of the database. It copies the whole thing. Differentials measure their changes from a full backup, and log backups carry on their own chain straight across any later full backups.

A log backup contains the log records that the previous log backup did not capture. These are links in a chain. Each one hands over to the next. If one goes missing, your restore stops dead at that point and nothing after it can be applied.

The Restore Order Without a Differential

Restore the full backup, then apply every log backup taken after it, in order, with none skipped. That is the whole sequence.

Every statement except the last carries NORECOVERY. That’s the flag telling SQL Server you have more files coming. The final statement carries RECOVERY, and that’s the one that opens the database for use.

Run RECOVERY too early and there’s no undo. The database comes online and you start the sequence again from a data backup. I explained that flag properly in a companion post the following day in 2009, and the behavior hasn’t changed since.

Now Add the Differential

Here’s the part that makes the whole thing click, and it’s the reason for the second animation.

A differential holds everything that changed since the last full. Not since the last differential. Since the last full. That is the single most misunderstood sentence in SQL Server backups. It is also why differentials generally grow as their base gets older, though the size is not guaranteed to rise every single day.

The word people reach for is incremental, and that’s the wrong word. Incremental would mean each one picks up where the previous one stopped. Differentials are cumulative. Each one starts again from the full.

So the sequence gains one step. Restore the full backup you chose. Then restore the newest usable differential that is based on that full. Then apply every log backup taken after it, in order.

That middle sentence matters more than it looks. A differential only applies to the specific full backup it was based on, so “just grab the newest differential” is the wrong instinct if you have restored an older full.

Once a newer usable differential exists, you normally skip the earlier ones. Keep them until the new file has been verified, though. An older differential on the same full is a perfectly good fallback if the newest one turns out to be corrupt.

What the Differential Actually Buys You

The numbers in that animation are worth spelling out, because they’re the entire argument.

Take a full backup at Sunday 22:00, log backups every fifteen minutes, and a disaster on Wednesday at 14:35. The last usable log is 14:30.

Sunday 22:00 to Wednesday 14:30 is 64.5 hours. At four log backups an hour that’s 258 log backups. Add the full and you’re restoring 259 files, every one of them in the correct order, every one of them needing to work.

Now add a single differential at Wednesday noon. You restore the full, then that differential, then the logs from 12:15 to 14:30. Ten of them. Twelve files instead of 259.

Same data. Same second in time. Twelve files to get right while your phone is ringing, instead of two hundred and fifty nine.

In this FULL recovery example, that is what a differential buys you. It replaces hundreds of log restores with one data backup and the handful of logs that follow it.

One thing I have skipped on purpose. This example stops at the 14:30 log backup. In a real failure, if the tail of the log is still readable and you want the latest possible moment, take a tail-log backup before you start restoring, and that becomes the final log in the sequence.

The Strategy Most People Actually Have

Here is what I find on real servers more often than anything else. A full backup every night, log backups every so often, and no differentials at all.

It works. That is the problem. It works right up until the day you need it, and then somebody discovers that recovering to lunchtime means feeding two hundred and fifty eight files through in order while the business waits.

The other version I see is worse. Full backups only, no log backups, and the database sitting in FULL recovery. That setup quietly grows the transaction log until a disk fills up at three in the morning.

Differentials fix the first problem. Log backups fix the second. Most shops need both.

A Starting Point, Not a Prescription

If you have nothing today, start here and adjust.

That last row is the one worth sitting with. Your log backup interval is your data loss window. Back up every fifteen minutes and you can lose fifteen minutes. Back up hourly and you can lose an hour. Nobody sets that number for you. The business does, whether they realize it or not.

Now the disclaimer, and I mean it. These are starting numbers, not answers. A busy database might need log backups every five minutes. A quiet reporting database might be fine with a nightly full and nothing else. Big databases sometimes cannot finish a weekly full inside the window at all.

Try a schedule, measure it, and change it. What works on my servers may be wrong on yours.

Five Questions I Get Very Frequently

Why does my log file keep growing and never shrink?

Almost always the same answer. The database is in FULL recovery and nobody is taking log backups. SQL Server holds on to every log record until a log backup releases it, so the file grows forever.

Take log backups, or switch to SIMPLE if you genuinely do not need point in time recovery. Shrinking the file without fixing the cause just means you get to do it again next month. I wrote this up properly in How to Stop Growing Log File Too Big, and more recently in Transaction Logs: The Good, The Bad, and The Ugly.

I take a full backup every night. Do I still need log backups?

If the database is in FULL recovery, yes. A full backup does not release the log. That surprises people constantly. You can back up nightly for a year and still run out of disk.

How often should I take a differential?

Ask how long you are willing to sit and watch a restore. Nightly differentials mean you never replay more than a day of logs. Every six hours means you never replay more than six. It costs you a little storage and buys you a much shorter bad afternoon.

Can I skip all this and just use SIMPLE recovery?

You can, and for some databases it is the right call. In SIMPLE there are no log backups, so you recover to your last full or differential and lose everything after it. If that is acceptable for a reporting copy, use it. If it is a system your business runs on, it is not. There is more on choosing in Recovery Models and Selection.

How do I know any of this actually works?

You restore it. There is no other way to know, and there never has been.

Put a restore test in the calendar the way you put a backup job in the scheduler. Once a month, pick a real backup set, restore it somewhere harmless, and run a query against it. That is the whole test.

A backup is not a backup until it has been restored. Until then it is a file you are hoping about.

Three Things That Cost People Their Afternoon

Restoring every differential. You only need the newest one that matches your full. I’ve watched people restore four in a row, waiting through each, before somebody points out that the last one already contained the other three.

Assuming a full backup breaks the log chain. It doesn’t. This one surprises people every time. You can take a full backup in the middle of the day and your log backups carry on chaining exactly as before. What actually breaks the chain is switching the database to the SIMPLE recovery model, and switching back does not repair it. You need a full or differential data backup to start the new chain.

Forgetting that a COPY_ONLY full does not reset the differential base. That’s the whole point of it. Take a copy for a developer with COPY_ONLY and tonight’s differential still measures from your real Sunday full. Take a normal full instead and it quietly becomes the new differential base, so every differential after it depends on a file you never meant to keep.

The Old Post, Seventeen Years On

If you want the original written version with the static diagram, it’s still here: Backup Timeline and Understanding of Database Restore Process in Full Recovery Model, from 14 July 2009.

Reading it back, I wouldn’t change the explanation. I’d just draw it better, which is what I’ve finally done.

The thing I’d add is the practice. Knowing this order and having run it are not the same skill, and the gap between them only shows up on the worst day of your year.

Your backups are not a backup strategy. They’re a restore strategy that hasn’t been tested yet.

This is not a story about which backup types exist, it is a story about the ten minutes on a bad afternoon when you have to remember what order they go back in.

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

First appeared on Full, Differential and Log Backups: A Practical Guide