What a Deadlock Taught Me About Working with AI

 Why I’m Writing This?

Last week, my coworker sent me a report about a deadlock and asked me to help troubleshoot it. The event gave me so many thoughts that it made me feel like writing a blog post sharing them and exploring how AI has been for me in my day-to-day job as a DBA. 

I was afraid the blog would go on too long by mixing technical demos with personal reflections, so I'm splitting this into two posts. In this one, I will share my perspective on AI and my realizations after the deadlock event. In a separate future post, I will cover the deadlock event itself with a demo and how I used AI as a second opinion to solve the problem. 

 (Spoiler Alert: Claude gave me several options to solve the deadlock. Annoyingly, the first option, according to it, the least risky solution, was to create a covering index spanning every single column, and you know it's against best practice. But the second option that it suggested, using “UPDATE…OUTPUT”, was the one that worked, and that impressed me because I hadn't even considered it, having barely seen or used that syntax in my four years working with T-SQL.)

From Ignorance to Awareness

Amid the chaos and excitement of the AI boom, 

I gradually found myself incorporating AI more into my daily work.

I had been using AI since the ChatGPT’s release, and later my company adopted Claude, but only to ask them to help me with things like writing a DMV query, proofreading my emails and messages, or comparing two pieces of code to make sure an improved version was doing the same thing as the original. These are tasks I believe AI can do better than me. 

I didn’t trust it to help me rewrite or tune queries, troubleshoot deadlocks, tasks I thought I would handle better myself, since I can collect more data about an issue than the AI can, I know my system better than it does, and sometimes when I did try, the AI just gave me wrong answers that I felt would only waste my time. (I still now resist asking it to rewrite production queries for me, though I can accept its tuning ideas and rewrite them myself.)

Thankfully, I kept myself updated; I followed Brent’s newsletters and read every tech blog he shared. And of course, among those were posts about AI and how DBAs are using it in their work (everyone is crazy about AI now). I learned about writing good prompts, giving the AI as much useful information as I would need myself (like the query text, the query plan, the deadlock report, ect.), and establishing a workflow that makes it simple enough to collect that data in the first place. For example, the sp_BlitzCache proc (First Responder Kit) has incorporated AI. I haven't tried that functionality yet, but my understanding is that you provide it the stored procedure name you're trying to tune along with an AI configuration (e.g., the model, a tailored prompt for the problem at hand). And the proc will automatically collect the query text and execution plan, sends them to the AI with your prompt, and returns a response. It is very neat. There is some setup involved, I believe SQL Server 2025 is required, but it should be straightforward and one time setup. I'll definitely give it a try sometime.

I picked up all of this gradually over time. But it wasn't until last week that I actually used Claude to troubleshoot a deadlock issue. And honestly, I was reluctant at first. I had already analyzed the deadlock report myself and exhausted every solution I could think of, ruling each one out due to its limitations. I sent my findings to my coworker, a lengthy write-up explaining how the deadlock occurred, what the potential solutions were and why none of them would work, and concluding that adding retry logic might be our only fix.

Then, about five minutes later, I had an idea: why not give AI a try? I was confident there wouldn't be any workable solution beyond retry logic, but I was also curious whether the AI would even understand how the deadlock had occurred in the first place. (Looking back, I think the flood of posts about AI replacing white-collar workers, and all the advice to start using AI before it replaces you, had quietly gotten to me. That fear had probably crept in.) So I gave it everything that I used to understand the problem: the query text, the query plans, and the deadlock report. 

Claude understood the problem very well. It gave me an answer, not a perfect one (it recommended a covering index with almost every columns in the table as included columns, and referred to this solution as “Best fix, lowest risk”). Then I read the second suggestion.

I stopped for a moment and thought:

“Oh… why didn’t I think of that?”

Claude suggested rewriting the logic using UPDATE…OUTPUT, collapsing the SELECT and UPDATE into a single statement and eliminating the deadlock path entirely. I realized AI has been getting smarter. Or had it always been, and I simply hadn't been using it correctly?

The Advisor

I think people relate to AI differently. Some see it as nothing more than a fancy search engine, others treat it like a personal assistant, and some have even started calling it a friend. For me, the word that feels most right is advisor.

You know those advisors in Civilization? I've been playing that game recently, and they pop up to offer help and suggestions for production and research choices based on where your civilization stands. I almost always ignored them, stubbornly convinced that I should figure everything out myself. And honestly, that might be exactly why I never play well.

But here's the thing: AI is a genuinely smart advisor. It proposed an elegant solution to my deadlock problem, one I never would have found on my own, and probably wouldn't have stumbled across online either. I fully understood the problem. I knew what needed fixing. I just hadn't thought of UPDATE…OUTPUT because I barely use it, and it simply didn't cross my mind. That's exactly what a good advisor is for, not to replace your thinking, but to catch the blind spots you didn't know you had.

So from now on, that's how I plan to use it: as a second opinion from someone smarter than me in ways I haven't figured out yet.

Of course, a good advisor still needs the right information to work with. In my case, that means a well-crafted prompt with everything I would gather if I were solving the problem myself. 

Maybe AI Won't Replace Me

Remember that covering index recommendation I mentioned? That one has more than 10 included columns and that goes against general best practices. The more columns you pack into an index, the heavier your modification costs (INSERT, UPDATE, DELETE) and the more painful your maintenance becomes. I still haven't figured out why Claude flagged that as the "Best fix, lowest risk" solution. But honestly? I'm not complaining. It's a reminder that someone needs to catch these things, and that someone is us.

I genuinely believe the best setup for any company right now is a knowledgeable human working alongside a capable AI. Not one replacing the other, but each covering the other's blind spots.

Here’s another story. Before my coworker brought the deadlock to me, he had already tried fixing it by adding a query hint. It didn’t work. My guess is that he had asked Claude for help and applied the suggestion it gave him. Since he’s a developer and not a DBA, he likely pasted only the query into Claude without including the deadlock report or execution plans. I tested that theory by doing the same thing myself and got a similar answer.

The lesson isn’t that AI failed him. It simply worked with the information it was given. Without the right context, even a smart advisor will point you in the wrong direction. That’s where domain knowledge matters. A developer might not know what a deadlock report is, why an execution plan matters, or what we can give so AI has a better understanding of the problem at hand. A DBA does. And that gap is why we're still in the room.

Control what you can control

There's a Stoic idea I keep coming back to: control what you can control.

I can't control whether AI will eventually change my role or reshape my industry. But I can control whether I stay curious, keep learning, and actually engage with the tools that are changing the world around me. Worrying about replacement is wasted energy. Using that same energy to grow; that's a choice I can make every day.

That mindset shift is really what this whole blog is about. I went from ignoring AI for anything serious, to letting it surprise me with a creative solution for my deadlock problem. That's not a small thing. It changed how I think about what AI is for and what my role alongside it looks like.

I'm also genuinely grateful. Grateful to still have a job when so many others haven't been so fortunate. Grateful for my employer. And grateful for the DBA community, for the newsletters, the blog posts, the shared knowledge that quietly kept me moving forward even when I didn't realize it.