Possible table definition mismatch bug in sp_BlitzCache (AI)

[kfrancis]

Version of the script

Version 8.28, dated 20251124 (November 24, 2025)

What is the current behavior?

When running sp_BlitzCache with @AI = 2 (or @AI = 1), the procedure works correctly initially after running the creation script. However, after running it several times, it fails with:

Msg 207, Level 16, State 1, Procedure sp_BlitzCache, Line 4871 [Batch Start Line 0]
Invalid column name 'ai_prompt'.

The failure occurs at line 5115 during the "Building AI prompts for query plans" phase when attempting to UPDATE the ai_prompt column in ##BlitzCacheProcs.

Re-running the creation script temporarily fixes the issue until it fails again.

If the current behavior is a bug, please provide the steps to reproduce.

1. Run the sp_BlitzCache creation script against an Azure SQL Database
2. Execute: EXEC sp_BlitzCache @SortOrder = 'query hash', @AI = 2;
3. Verify it works successfully
4. Allow the ##BlitzCacheProcs global temp table to be dropped (e.g., close connection, restart session, or manually DROP TABLE ##BlitzCacheProcs)
5. Execute the same command again: EXEC sp_BlitzCache @SortOrder = 'query hash', @AI = 2;
6. Observe the "Invalid column name 'ai_prompt'" error

What is the expected behavior?

The @AI parameter should work consistently regardless of whether the ##BlitzCacheProcs table is being reused or recreated.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

Confirmed on Azure SQL Database. The issue is likely to affect all versions of SQL Server that support the AI features, as it's a table definition problem.

The root cause is a mismatch between two CREATE TABLE ##BlitzCacheProcs statements:

• Line 49-242 (outside procedure): Includes ai_prompt, ai_advice, ai_payload, ai_raw_response columns (lines 238-241)
• Line 2659-2845 (inside procedure): Missing these four AI-related columns, ending with only Pattern NVARCHAR(20) at line 2844

Suggested Fix:

Add the missing columns after line 2843 in the internal table creation:

        Pattern NVARCHAR(20),
        ai_prompt NVARCHAR(MAX),
        ai_advice NVARCHAR(MAX),
        ai_payload NVARCHAR(MAX),
        ai_raw_response NVARCHAR(MAX)
    );

This will make both table definitions consistent and ensure the AI features work regardless of when the table is created.

[kfrancis]

This is the message output from a failed run of EXEC sp_BlitzCache @SortOrder = 'query hash', @AI = 2; :

Setting up AI configuration defaults
Beginning query hash sort
Checking database validity
Checking sort order
Creating temp tables for internal processing
Checking plan cache age
Checking for single use plans and plans with many queries
Setting up filter for Query Hashes
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Including only chosen Query Hashes
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Creating temp tables for results and warnings.
Cleaning up old warnings for your SPID
Collecting execution plan information.
Attempting to aggregate stored proc info from separate statements
Computing CPU, duration, read, and write metrics
Update CPU percentage for stored procedures
Gather percentage information from grouped results
Begin XML nodes processing
Gathering high level plan information
Performing query level checks
Performing compile timeout checks
Performing compile memory limit exceeded checks
Performing unparameterized query checks
Performing index DML checks
Performing table DML checks
Gathering row estimates
Gathering trivial plans
Gathering statement costs
Updating statement costs
Checking for plan warnings
Checking for implicit conversion
Performing busy loops checks
Performing TVF join check
Checking for operator warnings
Checking for table variables
Checking for functions
Checking for expensive key lookups
Checking for expensive remote queries
Checking for expensive sorts
No cursor plans found, skipping
Checking for bad scans and plan forcing
Checking for computed columns that reference scalar UDFs
Checking for filters that reference scalar UDFs
Checking modification queries that hit lots of indexes
Checking for Spatial index use
Checking for wonky Index Spools
Checking for wonky Table Spools
Checking for selects that cause non-spill and index spool writes
Checking for forced serialization
Checking for ColumnStore queries operating in Row Mode instead of Batch Mode
Checking for downlevel cardinality estimators being used on SQL Server 2014.
Attempting to get stored procedure name for individual statements
Attempting to get function name for individual statements
Trace flag checks
Checking for MSTVFs
Checking for many to many merge joins
Is Paul White Electric?
Checking for non-sargable predicates
Getting information about implicit conversions and stored proc parameters
Getting variable info
Getting conversion info
Parsing conversion info
Updating variables for inserted procs
Inserting variables for other procs
Updating procs
Updating SET options
Updating conversion XML
Updating cached parameter XML for stored procs
Updating cached parameter XML for statements
Filling in implicit conversion and cached plan parameter info
Filling in missing index blanks
Setting configuration values
Checking for query level SQL Server issues.
Checking for forced parameterization and cursors.
Populating Warnings column
Populating Warnings column for stored procedures
Checking for plans with >128 levels of nesting
Checking for plans with no warnings
Building AI prompts for query plans
Msg 207, Level 16, State 1, Procedure sp_BlitzCache, Line 4871 [Batch Start Line 0]
Invalid column name 'ai_prompt'.

Completion time: 2025-12-03T12:54:22.9254605-05:00