sp_BlitzCache Got ChatGPT's Advice On My Deadlock

In the last post, I tested sp_BlitzCache's ChatGPT integration with index tuning and code review personalities. Both worked well. ChatGPT disagreed with the optimizer on index key order and was right about it. The code review caught a date boundary bug I missed. I ended that post saying I would test the blocking and deadlocking personality next. Here it is.

The Setup

Super simple. One table, two procs with an update and a waitfor.

CREATE TABLE dbo.DeadlockTest (ID INT PRIMARY KEY, Val INT);
INSERT INTO dbo.DeadlockTest VALUES (1, 0), (2, 0);

CREATE PROCEDURE dbo.usp_DeadlockSide1
AS
SET NOCOUNT ON;
BEGIN
    BEGIN TRAN;
    UPDATE DeadlockTest WITH (HOLDLOCK) SET Val = 1 WHERE ID = 1;
    WAITFOR DELAY '00:00:05';
    UPDATE DeadlockTest SET Val = 1 WHERE ID = 2;
    COMMIT;
END
GO

CREATE PROCEDURE dbo.usp_DeadlockSide2
AS
SET NOCOUNT ON;
BEGIN
    BEGIN TRAN;
    UPDATE DeadlockTest WITH (HOLDLOCK) SET Val = 2 WHERE ID = 2;
    WAITFOR DELAY '00:00:05';
    UPDATE DeadlockTest SET Val = 2 WHERE ID = 1;
    COMMIT;
END
GO

Ok. That creates the procedures that may have deadlocking issues... 😉 but I also need to create the new blocking/deadlocking personality for sp_BlitzCache. We get that into the Blitz_AI config table with this:

INSERT INTO DBA.dbo.Blitz_AI (
    Nickname, AI_Model, AI_URL, 
    AI_Database_Scoped_Credential_Name, 
    AI_System_Prompt_Override, 
    Timeout_Seconds, DefaultModel)
VALUES (
    'blocking deadlocking', 
    'gpt-5-mini', 
    'https://api.openai.com/v1/chat/completions', 
    'https://api.openai.com/', 
    'You are a senior SQL Server concurrency specialist. 
     Focus only on blocking, deadlocking, and lock contention. 
     Analyze the query text and execution plan for: lock 
     escalation risks, transaction scope issues, lock ordering 
     problems that cause deadlocks, long-held locks due to 
     WAITFOR or unnecessary work inside transactions, isolation 
     level concerns, and missing or misused lock hints. Do not 
     suggest index changes or query rewrites unless they directly 
     reduce lock duration or contention. For each finding, explain 
     the contention risk and recommend a fix. If two procedures 
     access the same resources in different order, call it out 
     explicitly. Render your output in Markdown.',
    230, 
    0);

Remember, that AI_System_Prompt_Override value is what we are saying to AI, and it must be very specific: lock ordering, transaction scope, escalation, isolation levels. I've also set the timeout to 230 seconds because execution plan XML can be large and ChatGPT needs time to process it. (lesson learned)

Then I ran each proc separately just to get them into the cache. No deadlocks:

EXEC dbo.usp_DeadlockSide1;
EXEC dbo.usp_DeadlockSide2;

And now the deadlock:

-- query window 1
EXEC dbo.usp_DeadlockSide1;
-- query window 2 (within 2 seconds):
EXEC dbo.usp_DeadlockSide2;

The two transactions collide and one is chosen as the deadlock victim. Now we are ready for sp_BlitzCache.

The sp_BlitzCache Call

EXEC dbo.sp_BlitzCache 
    @StoredProcName = 'usp_DeadlockSide1',
    @AI = 1, 
    @AIConfig = 'DBA.dbo.Blitz_AI', 
    @AIModel = 'blocking deadlocking';

It ran for 00:01:53 and output 3 rows for AI with the sp_BlitzCache findings grid:

What ChatGPT Found

The AI Advice column was populated for two of the three rows in the results. This one is from Row 3, the UPDATE DeadlockTest WITH (HOLDLOCK) one. Too large to include the whole thing here, so I've saved it to another page. It's great! Way more than I expected. You really should view the whole page.

What stands out:

• WAITFOR inside the transaction is the lock amplifier.
  ChatGPT flags the 5-second WAITFOR executing while the transaction is open, holding exclusive locks on the first row for the entire delay. Its top recommendation was to move the WAITFOR outside the transaction. Interesting. And correct. Keep transactions as short as possible.
• HOLDLOCK is redundant on a singleton PK update.
  The first UPDATE uses WITH (HOLDLOCK). ChatGPT noted that an UPDATE already acquires an exclusive lock on the modified row, and HOLDLOCK is unnecessary for a single-row primary key update. It can impose stricter lock behavior and increases lock duration without benefit. Recommendation: remove it.
• It inferred the other proc exists.
  This one really impressed me. I only ran sp_BlitzCache against Side 1, so ChatGPT never saw usp_DeadlockSide2 — but from the procedure name and the lock pattern, it deduced the deadlock partner:
  'Stored procedure name usp_DeadlockSide1 suggests there is (or may be) a side 2 that updates ID=2 then ID=1.'
  It then described the full deadlock sequence and recommended enforcing a consistent lock ordering across all procedures that touch the same rows — always update in the same deterministic order (ie., always ID 1 then ID 2).
  That is not generic advice. It read the execution plan, understood the structure, and inferred the concurrency problem from a single proc. I am impressed. Again.

What sp_BlitzCache Found on Its Own

Before ChatGPT ever got involved, sp_BlitzCache flagged the proc with CheckID 50: Long Running Low CPU under the Blocking findings group. That is the WAITFOR DELAY making duration high while CPU stays near zero. That's exactly what real blocking looks like from the plan cache — a query that runs for a long time but does almost no work. sp_BlitzCache caught it, too.

The Point

I purposely built a simple deadlock. One table, two procs with an update and a waitfor. Nothing complex. The question was whether AI would read the execution plan and find anything pertaining to concurrency problems. It did.

And sp_BlitzCache's own findings (CheckID 50 — Long Running Low CPU / Blocking) backed up the AI's analysis independently.

As a test, this took nothing to set up. But looking forward to real use cases, I can see big opportunity. I will reiterate what Brent said before: The quality of whatever you get back from AI is completely dependent upon how smart you write the prompt.

Next, I will test a refactoring personality — give ChatGPT a big and busy multi-step, row-by-row query and see if it can rewrite it as a set-based operation. Stay tuned.

More to Read:

sqlfingers: sp_BlitzCache Can Talk to ChatGPT Now. Here's What It Said.
Brent Ozar: Get ChatGPT's Advice On Your Queries with sp_BlitzCache