sp_BlitzCache.sql

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

IF (
SELECT
  CASE 
     WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '8%' THEN 0
     WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '9%' THEN 0
	 ELSE 1
  END 
) = 0
BEGIN
	DECLARE @msg VARCHAR(8000); 
	SELECT @msg = 'Sorry, sp_BlitzCache doesn''t work on versions of SQL prior to 2008.' + REPLICATE(CHAR(13), 7933);
	PRINT @msg;
	RETURN;
END;

IF OBJECT_ID('dbo.sp_BlitzCache') IS NULL
  EXEC ('CREATE PROCEDURE dbo.sp_BlitzCache AS RETURN 0;');
GO

IF OBJECT_ID('dbo.sp_BlitzCache') IS NOT NULL AND OBJECT_ID('tempdb.dbo.##BlitzCacheProcs', 'U') IS NOT NULL
    EXEC ('DROP TABLE ##BlitzCacheProcs;');
GO

IF OBJECT_ID('dbo.sp_BlitzCache') IS NOT NULL AND OBJECT_ID('tempdb.dbo.##BlitzCacheResults', 'U') IS NOT NULL
    EXEC ('DROP TABLE ##BlitzCacheResults;');
GO

CREATE TABLE ##BlitzCacheResults (
    SPID INT,
    ID INT IDENTITY(1,1),
    CheckID INT,
    Priority TINYINT,
    FindingsGroup VARCHAR(50),
    Finding VARCHAR(500),
    URL VARCHAR(200),
    Details VARCHAR(4000) 
);

CREATE TABLE ##BlitzCacheProcs (
        SPID INT ,
        QueryType NVARCHAR(258),
        DatabaseName sysname,
        AverageCPU DECIMAL(38,4),
        AverageCPUPerMinute DECIMAL(38,4),
        TotalCPU DECIMAL(38,4),
        PercentCPUByType MONEY,
        PercentCPU MONEY,
        AverageDuration DECIMAL(38,4),
        TotalDuration DECIMAL(38,4),
        PercentDuration MONEY,
        PercentDurationByType MONEY,
        AverageReads BIGINT,
        TotalReads BIGINT,
        PercentReads MONEY,
        PercentReadsByType MONEY,
        ExecutionCount BIGINT,
        PercentExecutions MONEY,
        PercentExecutionsByType MONEY,
        ExecutionsPerMinute MONEY,
        TotalWrites BIGINT,
        AverageWrites MONEY,
        PercentWrites MONEY,
        PercentWritesByType MONEY,
        WritesPerMinute MONEY,
        PlanCreationTime DATETIME,
		PlanCreationTimeHours AS DATEDIFF(HOUR, PlanCreationTime, SYSDATETIME()),
        LastExecutionTime DATETIME,
		LastCompletionTime DATETIME,
        PlanHandle VARBINARY(64),
		[Remove Plan Handle From Cache] AS 
			CASE WHEN [PlanHandle] IS NOT NULL 
			THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [PlanHandle], 1) + ');'
			ELSE 'N/A' END,
		SqlHandle VARBINARY(64),
			[Remove SQL Handle From Cache] AS 
			CASE WHEN [SqlHandle] IS NOT NULL 
			THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [SqlHandle], 1) + ');'
			ELSE 'N/A' END,
		[SQL Handle More Info] AS 
			CASE WHEN [SqlHandle] IS NOT NULL 
			THEN 'EXEC sp_BlitzCache @OnlySqlHandles = ''' + CONVERT(VARCHAR(128), [SqlHandle], 1) + '''; '
			ELSE 'N/A' END,
		QueryHash BINARY(8),
		[Query Hash More Info] AS 
			CASE WHEN [QueryHash] IS NOT NULL 
			THEN 'EXEC sp_BlitzCache @OnlyQueryHashes = ''' + CONVERT(VARCHAR(32), [QueryHash], 1) + '''; '
			ELSE 'N/A' END,
        QueryPlanHash BINARY(8),
        StatementStartOffset INT,
        StatementEndOffset INT,
		PlanGenerationNum BIGINT,
        MinReturnedRows BIGINT,
        MaxReturnedRows BIGINT,
        AverageReturnedRows MONEY,
        TotalReturnedRows BIGINT,
        LastReturnedRows BIGINT,
		/*The Memory Grant columns are only supported 
		  in certain versions, giggle giggle.
		*/
		MinGrantKB BIGINT,
		MaxGrantKB BIGINT,
		MinUsedGrantKB BIGINT, 
		MaxUsedGrantKB BIGINT,
		PercentMemoryGrantUsed MONEY,
		AvgMaxMemoryGrant MONEY,
		MinSpills BIGINT,
		MaxSpills BIGINT,
		TotalSpills BIGINT,
		AvgSpills MONEY,
        QueryText NVARCHAR(MAX),
        QueryPlan XML,
        /* these next four columns are the total for the type of query.
            don't actually use them for anything apart from math by type.
            */
        TotalWorkerTimeForType BIGINT,
        TotalElapsedTimeForType BIGINT,
        TotalReadsForType DECIMAL(30),
        TotalExecutionCountForType BIGINT,
        TotalWritesForType DECIMAL(30),
        NumberOfPlans INT,
        NumberOfDistinctPlans INT,
        SerialDesiredMemory FLOAT,
        SerialRequiredMemory FLOAT,
        CachedPlanSize FLOAT,
        CompileTime FLOAT,
        CompileCPU FLOAT ,
        CompileMemory FLOAT ,
		MaxCompileMemory FLOAT ,
        min_worker_time BIGINT,
        max_worker_time BIGINT,
        is_forced_plan BIT,
        is_forced_parameterized BIT,
        is_cursor BIT,
		is_optimistic_cursor BIT,
		is_forward_only_cursor BIT,
		is_fast_forward_cursor BIT,
		is_cursor_dynamic BIT,
        is_parallel BIT,
		is_forced_serial BIT,
		is_key_lookup_expensive BIT,
		key_lookup_cost FLOAT,
		is_remote_query_expensive BIT,
		remote_query_cost FLOAT,
        frequent_execution BIT,
        parameter_sniffing BIT,
        unparameterized_query BIT,
        near_parallel BIT,
        plan_warnings BIT,
        plan_multiple_plans INT,
        long_running BIT,
        downlevel_estimator BIT,
        implicit_conversions BIT,
        busy_loops BIT,
        tvf_join BIT,
        tvf_estimate BIT,
        compile_timeout BIT,
        compile_memory_limit_exceeded BIT,
        warning_no_join_predicate BIT,
        QueryPlanCost FLOAT,
        missing_index_count INT,
        unmatched_index_count INT,
        min_elapsed_time BIGINT,
        max_elapsed_time BIGINT,
        age_minutes MONEY,
        age_minutes_lifetime MONEY,
        is_trivial BIT,
		trace_flags_session VARCHAR(1000),
		is_unused_grant BIT,
		function_count INT,
		clr_function_count INT,
		is_table_variable BIT,
		no_stats_warning BIT,
		relop_warnings BIT,
		is_table_scan BIT,
	    backwards_scan BIT,
	    forced_index BIT,
	    forced_seek BIT,
	    forced_scan BIT,
		columnstore_row_mode BIT,
		is_computed_scalar BIT ,
		is_sort_expensive BIT,
		sort_cost FLOAT,
		is_computed_filter BIT,
		op_name VARCHAR(100) NULL,
		index_insert_count INT NULL,
		index_update_count INT NULL,
		index_delete_count INT NULL,
		cx_insert_count INT NULL,
		cx_update_count INT NULL,
		cx_delete_count INT NULL,
		table_insert_count INT NULL,
		table_update_count INT NULL,
		table_delete_count INT NULL,
		index_ops AS (index_insert_count + index_update_count + index_delete_count + 
					  cx_insert_count + cx_update_count + cx_delete_count +
					  table_insert_count + table_update_count + table_delete_count),
		is_row_level BIT,
		is_spatial BIT,
		index_dml BIT,
		table_dml BIT,
		long_running_low_cpu BIT,
		low_cost_high_cpu BIT,
		stale_stats BIT, 
		is_adaptive BIT,
		index_spool_cost FLOAT,
		index_spool_rows FLOAT,
		table_spool_cost FLOAT,
		table_spool_rows FLOAT,
		is_spool_expensive BIT,
		is_spool_more_rows BIT,
		is_table_spool_expensive BIT,
		is_table_spool_more_rows BIT,
		estimated_rows FLOAT,
		is_bad_estimate BIT, 
		is_paul_white_electric BIT,
		is_row_goal BIT,
		is_big_spills BIT,
		is_mstvf BIT,
		is_mm_join BIT,
        is_nonsargable BIT,
		select_with_writes BIT,
		implicit_conversion_info XML,
		cached_execution_parameters XML,
		missing_indexes XML,
        SetOptions VARCHAR(MAX),
        Warnings VARCHAR(MAX),
    	Pattern NVARCHAR(20),
        ai_prompt NVARCHAR(MAX),
        ai_advice NVARCHAR(MAX),
        ai_payload NVARCHAR(MAX),
        ai_raw_response NVARCHAR(MAX)
    );
GO 

ALTER PROCEDURE dbo.sp_BlitzCache
    @Help BIT = 0,
    @Top INT = NULL,
    @SortOrder VARCHAR(50) = 'CPU',
    @UseTriggersAnyway BIT = NULL,
    @ExportToExcel BIT = 0,
    @ExpertMode TINYINT = 0,
	@OutputType VARCHAR(20) = 'TABLE' ,
    @OutputServerName NVARCHAR(258) = NULL ,
    @OutputDatabaseName NVARCHAR(258) = NULL ,
    @OutputSchemaName NVARCHAR(258) = NULL ,
    @OutputTableName NVARCHAR(258) = NULL , -- do NOT use ##BlitzCacheResults or ##BlitzCacheProcs as they are used as work tables in this procedure
    @ConfigurationDatabaseName NVARCHAR(128) = NULL ,
    @ConfigurationSchemaName NVARCHAR(258) = NULL ,
    @ConfigurationTableName NVARCHAR(258) = NULL ,
    @DurationFilter DECIMAL(38,4) = NULL ,
    @HideSummary BIT = 0 ,
    @IgnoreSystemDBs BIT = 1 ,
    @IgnoreReadableReplicaDBs BIT = 1 ,
    @OnlyQueryHashes VARCHAR(MAX) = NULL ,
    @IgnoreQueryHashes VARCHAR(MAX) = NULL ,
    @OnlySqlHandles VARCHAR(MAX) = NULL ,
	@IgnoreSqlHandles VARCHAR(MAX) = NULL ,
    @QueryFilter VARCHAR(10) = 'ALL' ,
    @DatabaseName NVARCHAR(128) = NULL ,
    @StoredProcName NVARCHAR(128) = NULL,
	@SlowlySearchPlansFor NVARCHAR(4000) = NULL,
    @Reanalyze BIT = 0 ,
    @SkipAnalysis BIT = 0 ,
    @BringThePain BIT = 0 ,
    @MinimumExecutionCount INT = 0,
	@Debug TINYINT = 0, /* 0 = no debugging info, 1 = normal debugging info, 2 = AI debugging info */
	@CheckDateOverride DATETIMEOFFSET = NULL,
	@MinutesBack INT = NULL,
    @AI TINYINT = 0, /* 1 = ask for advice, 2 = build prompt but don't actually call AI. Only works with a single query plan: automatically sets @ExpertMode = 1, @KeepCRLF = 1. */
    @AIModel VARCHAR(200) = NULL, /* Defaults to gpt-4.1-mini */
    @AIURL VARCHAR(200) = NULL, /* Defaults to https://api.openai.com/v1/chat/completions */
    @AICredential VARCHAR(200) = NULL, /* Defaults to 'https://api.openai.com/' or the root of your AIURL, trailing slash included */
    @AIConfig NVARCHAR(500) = NULL, /* Table where AI config data is stored - can be in the format db.schema.table, schema.table, or just table. */
	@Version     VARCHAR(30) = NULL OUTPUT,
	@VersionDate DATETIME = NULL OUTPUT,
	@VersionCheckMode BIT = 0,
	@KeepCRLF BIT = 0
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT @Version = '8.29', @VersionDate = '20260203';
SET @OutputType = UPPER(@OutputType);

IF(@VersionCheckMode = 1)
BEGIN
	RETURN;
END;

DECLARE @nl NVARCHAR(2) = NCHAR(13) + NCHAR(10) ;
	
IF @Help = 1 
	BEGIN
	PRINT '
	sp_BlitzCache from http://FirstResponderKit.org
	
	This script displays your most resource-intensive queries from the plan cache,
	and points to ways you can tune these queries to make them faster.

	To learn more, visit http://FirstResponderKit.org where you can download new
	versions for free, watch training videos on how it works, get more info on
	the findings, contribute your own code, and more.

	Known limitations of this version:
	 - @IgnoreQueryHashes and @OnlyQueryHashes require a CSV list of hashes
	   with no spaces between the hash values.

	Changes - for the full list of improvements and fixes in this version, see:
	https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/



	MIT License

	Copyright (c) Brent Ozar Unlimited

	Permission is hereby granted, free of charge, to any person obtaining a copy
	of this software and associated documentation files (the "Software"), to deal
	in the Software without restriction, including without limitation the rights
	to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
	copies of the Software, and to permit persons to whom the Software is
	furnished to do so, subject to the following conditions:

	The above copyright notice and this permission notice shall be included in all
	copies or substantial portions of the Software.

	THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
	IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
	FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
	AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
	LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
	OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
	SOFTWARE.
	';

	
	SELECT N'@Help' AS [Parameter Name] ,
			N'BIT' AS [Data Type] ,
			N'Displays this help message.' AS [Parameter Description]

	UNION ALL
	SELECT N'@Top',
			N'INT',
			N'The number of records to retrieve and analyze from the plan cache. The following DMVs are used as the plan cache: dm_exec_query_stats, dm_exec_procedure_stats, dm_exec_trigger_stats.'

	UNION ALL
	SELECT N'@SortOrder',
			N'VARCHAR(10)',
			N'Data processing and display order. @SortOrder will still be used, even when preparing output for a table or for excel. Possible values are: "CPU", "Reads", "Writes", "Duration", "Executions", "Recent Compilations", "Memory Grant", "Unused Grant", "Spills", "Query Hash", "Duplicate". Additionally, the word "Average" or "Avg" can be used to sort on averages rather than total. "Executions per minute" and "Executions / minute" can be used to sort by execution per minute. For the truly lazy, "xpm" can also be used. Note that when you use all or all avg, the only parameters you can use are @Top and @DatabaseName. All others will be ignored.'

	UNION ALL
	SELECT N'@UseTriggersAnyway',
			N'BIT',
			N'On SQL Server 2008R2 and earlier, trigger execution count is incorrect - trigger execution count is incremented once per execution of a SQL agent job. If you still want to see relative execution count of triggers, then you can force sp_BlitzCache to include this information.'

	UNION ALL
	SELECT N'@ExportToExcel',
			N'BIT',
			N'Prepare output for exporting to Excel. Newlines and additional whitespace are removed from query text and the execution plan is not displayed.'

	UNION ALL
	SELECT N'@ExpertMode',
			N'TINYINT',
			N'Default 0. When set to 1, results include more columns. When 2, mode is optimized for Opserver, the open source dashboard.'
    UNION ALL
	SELECT N'@OutputType',
			N'NVARCHAR(258)',
			N'If set to "NONE", this will tell this procedure not to run any query leading to a results set thrown to caller.'
			
	UNION ALL
	SELECT N'@OutputDatabaseName',
			N'NVARCHAR(128)',
			N'The output database. If this does not exist SQL Server will divide by zero and everything will fall apart.'

	UNION ALL
	SELECT N'@OutputSchemaName',
			N'NVARCHAR(258)',
			N'The output schema. If this does not exist SQL Server will divide by zero and everything will fall apart.'

	UNION ALL
	SELECT N'@OutputTableName',
			N'NVARCHAR(258)',
			N'The output table. If this does not exist, it will be created for you.'

	UNION ALL
	SELECT N'@DurationFilter',
			N'DECIMAL(38,4)',
			N'Excludes queries with an average duration (in seconds) less than @DurationFilter.'

	UNION ALL
	SELECT N'@HideSummary',
			N'BIT',
			N'Hides the findings summary result set.'

	UNION ALL
	SELECT N'@IgnoreSystemDBs',
			N'BIT',
			N'Ignores plans found in the system databases (master, model, msdb, tempdb, dbmaintenance, dbadmin, dbatools and resourcedb)'

	UNION ALL
	SELECT N'@OnlyQueryHashes',
			N'VARCHAR(MAX)',
			N'A list of query hashes to query. All other query hashes will be ignored. Stored procedures and triggers will be ignored.'

	UNION ALL
	SELECT N'@IgnoreQueryHashes',
			N'VARCHAR(MAX)',
			N'A list of query hashes to ignore.'
    
	UNION ALL
	SELECT N'@OnlySqlHandles',
			N'VARCHAR(MAX)',
			N'One or more sql_handles to use for filtering results.'
    
	UNION ALL
	SELECT N'@IgnoreSqlHandles',
			N'VARCHAR(MAX)',
			N'One or more sql_handles to ignore.'

	UNION ALL
	SELECT N'@DatabaseName',
			N'NVARCHAR(128)',
			N'A database name which is used for filtering results.'

	UNION ALL
	SELECT N'@StoredProcName',
			N'NVARCHAR(128)',
			N'Name of stored procedure you want to find plans for.'

	UNION ALL
	SELECT N'@SlowlySearchPlansFor',
			N'NVARCHAR(4000)',
			N'String to search for in plan text. % wildcards allowed.'

	UNION ALL
	SELECT N'@BringThePain',
			N'BIT',
			N'When using @SortOrder = ''all'' and @Top > 10, we require you to set @BringThePain = 1 so you understand that sp_BlitzCache will take a while to run.'

	UNION ALL
	SELECT N'@QueryFilter',
			N'VARCHAR(10)',
			N'Filter out stored procedures or statements. The default value is ''ALL''. Allowed values are ''procedures'', ''statements'', ''functions'', or ''all'' (any variation in capitalization is acceptable).'

	UNION ALL
	SELECT N'@Reanalyze',
			N'BIT',
			N'The default is 0. When set to 0, sp_BlitzCache will re-evalute the plan cache. Set this to 1 to reanalyze existing results'
           
	UNION ALL
	SELECT N'@MinimumExecutionCount',
			N'INT',
			N'Queries with fewer than this number of executions will be omitted from results.'
    
	UNION ALL
	SELECT N'@Debug',
			N'BIT',
			N'Setting this to 1 will print dynamic SQL and select data from all tables used. Setting to 2 will debug AI calls.'

	UNION ALL
	SELECT N'@MinutesBack',
			N'INT',
			N'How many minutes back to begin plan cache analysis. If you put in a positive number, we''ll flip it to negative.'

	UNION ALL
	SELECT N'@AI',
			N'TINYINT',
			N'1 = ask for advice. Only works with a single query plan for now. Automatically sets @ExpertMode = 1, @KeepCRLF = 1.'

	UNION ALL
	SELECT N'@AIModel',
			N'VARCHAR(200)',
			N'Defaults to gpt-4.1-mini. Can accept other models, or if you have a dbo.AI_Services table, we will look up services there.'

	UNION ALL
	SELECT N'@AIURL',
			N'VARCHAR(200)',
			N'Defaults to https://api.openai.com/v1/chat/completions. Can accept other URLs, or if you have a dbo.AI_Services table, we will look up services there.'

	UNION ALL
	SELECT N'@AICredential',
			N'VARCHAR(200)',
			N'The database scoped credential that you configured. Defaults to https://api.openai.com/ or the root URL of your @AIURL, trailing slash included. Must be a subset of the @AIURL parameter.'

	UNION ALL
	SELECT N'@Version',
			N'VARCHAR(30)',
			N'OUTPUT parameter holding version number.'
	
	UNION ALL
	SELECT N'@VersionDate',
			N'DATETIME',
			N'OUTPUT parameter holding version date.'

	UNION ALL
	SELECT N'@VersionCheckMode',
			N'BIT',
			N'Setting this to 1 will make the procedure stop after setting @Version and @VersionDate.'

	UNION ALL
	SELECT N'@KeepCRLF',
			N'BIT',
			N'Retain CR/LF in query text to avoid issues caused by line comments.';


	/* Column definitions */
	SELECT N'# Executions' AS [Column Name],
			N'BIGINT' AS [Data Type],
			N'The number of executions of this particular query. This is computed across statements, procedures, and triggers and aggregated by the SQL handle.' AS [Column Description]

	UNION ALL
	SELECT N'Executions / Minute',
			N'MONEY',
			N'Number of executions per minute - calculated for the life of the current plan. Plan life is the last execution time minus the plan creation time.'

	UNION ALL
	SELECT N'Execution Weight',
			N'MONEY',
			N'An arbitrary metric of total "execution-ness". A weight of 2 is "one more" than a weight of 1.'

	UNION ALL
	SELECT N'Database',
			N'sysname',
			N'The name of the database where the plan was encountered. If the database name cannot be determined for some reason, a value of NA will be substituted. A value of 32767 indicates the plan comes from ResourceDB.'

	UNION ALL
	SELECT N'Total CPU',
			N'BIGINT',
			N'Total CPU time, reported in milliseconds, that was consumed by all executions of this query since the last compilation.'

	UNION ALL
	SELECT N'Avg CPU',
			N'BIGINT',
			N'Average CPU time, reported in milliseconds, consumed by each execution of this query since the last compilation.'

	UNION ALL
	SELECT N'CPU Weight',
			N'MONEY',
			N'An arbitrary metric of total "CPU-ness". A weight of 2 is "one more" than a weight of 1.'

	UNION ALL
	SELECT N'Total Duration',
			N'BIGINT',
			N'Total elapsed time, reported in milliseconds, consumed by all executions of this query since last compilation.'

	UNION ALL
	SELECT N'Avg Duration',
			N'BIGINT',
			N'Average elapsed time, reported in milliseconds, consumed by each execution of this query since the last compilation.'

	UNION ALL
	SELECT N'Duration Weight',
			N'MONEY',
			N'An arbitrary metric of total "Duration-ness". A weight of 2 is "one more" than a weight of 1.'

	UNION ALL
	SELECT N'Total Reads',
			N'BIGINT',
			N'Total logical reads performed by this query since last compilation.'

	UNION ALL
	SELECT N'Average Reads',
			N'BIGINT',
			N'Average logical reads performed by each execution of this query since the last compilation.'

	UNION ALL
	SELECT N'Read Weight',
			N'MONEY',
			N'An arbitrary metric of "Read-ness". A weight of 2 is "one more" than a weight of 1.'

	UNION ALL
	SELECT N'Total Writes',
			N'BIGINT',
			N'Total logical writes performed by this query since last compilation.'

	UNION ALL
	SELECT N'Average Writes',
			N'BIGINT',
			N'Average logical writes performed by each execution this query since last compilation.'

	UNION ALL
	SELECT N'Write Weight',
			N'MONEY',
			N'An arbitrary metric of "Write-ness". A weight of 2 is "one more" than a weight of 1.'

	UNION ALL
	SELECT N'Query Type',
			N'NVARCHAR(258)',
			N'The type of query being examined. This can be "Procedure", "Statement", or "Trigger".'

	UNION ALL
	SELECT N'Query Text',
			N'NVARCHAR(4000)',
			N'The text of the query. This may be truncated by either SQL Server or by sp_BlitzCache(tm) for display purposes.'

	UNION ALL
	SELECT N'% Executions (Type)',
			N'MONEY',
			N'Percent of executions relative to the type of query - e.g. 17.2% of all stored procedure executions.'

	UNION ALL
	SELECT N'% CPU (Type)',
			N'MONEY',
			N'Percent of CPU time consumed by this query for a given type of query - e.g. 22% of CPU of all stored procedures executed.'

	UNION ALL
	SELECT N'% Duration (Type)',
			N'MONEY',
			N'Percent of elapsed time consumed by this query for a given type of query - e.g. 12% of all statements executed.'

	UNION ALL
	SELECT N'% Reads (Type)',
			N'MONEY',
			N'Percent of reads consumed by this query for a given type of query - e.g. 34.2% of all stored procedures executed.'

	UNION ALL
	SELECT N'% Writes (Type)',
			N'MONEY',
			N'Percent of writes performed by this query for a given type of query - e.g. 43.2% of all statements executed.'

	UNION ALL
	SELECT N'Total Rows',
			N'BIGINT',
			N'Total number of rows returned for all executions of this query. This only applies to query level stats, not stored procedures or triggers.'

	UNION ALL
	SELECT N'Average Rows',
			N'MONEY',
			N'Average number of rows returned by each execution of the query.'

	UNION ALL
	SELECT N'Min Rows',
			N'BIGINT',
			N'The minimum number of rows returned by any execution of this query.'

	UNION ALL
	SELECT N'Max Rows',
			N'BIGINT',
			N'The maximum number of rows returned by any execution of this query.'

	UNION ALL
	SELECT N'MinGrantKB',
			N'BIGINT',
			N'The minimum memory grant the query received in kb.'

	UNION ALL
	SELECT N'MaxGrantKB',
			N'BIGINT',
			N'The maximum memory grant the query received in kb.'

	UNION ALL
	SELECT N'MinUsedGrantKB',
			N'BIGINT',
			N'The minimum used memory grant the query received in kb.'

	UNION ALL
	SELECT N'MaxUsedGrantKB',
			N'BIGINT',
			N'The maximum used memory grant the query received in kb.'

	UNION ALL
	SELECT N'MinSpills',
			N'BIGINT',
			N'The minimum amount this query has spilled to tempdb in 8k pages.'

	UNION ALL
	SELECT N'MaxSpills',
			N'BIGINT',
			N'The maximum amount this query has spilled to tempdb in 8k pages.'

	UNION ALL
	SELECT N'TotalSpills',
			N'BIGINT',
			N'The total amount this query has spilled to tempdb in 8k pages.'

	UNION ALL
	SELECT N'AvgSpills',
			N'BIGINT',
			N'The average amount this query has spilled to tempdb in 8k pages.'

	UNION ALL
	SELECT N'PercentMemoryGrantUsed',
			N'MONEY',
			N'Result of dividing the maximum grant used by the minimum granted.'

	UNION ALL
	SELECT N'AvgMaxMemoryGrant',
			N'MONEY',
			N'The average maximum memory grant for a query.'

	UNION ALL
	SELECT N'# Plans',
			N'INT',
			N'The total number of execution plans found that match a given query.'

	UNION ALL
	SELECT N'# Distinct Plans',
			N'INT',
			N'The number of distinct execution plans that match a given query. '
			+ NCHAR(13) + NCHAR(10)
			+ N'This may be caused by running the same query across multiple databases or because of a lack of proper parameterization in the database.'

	UNION ALL
	SELECT N'Created At',
			N'DATETIME',
			N'Time that the execution plan was last compiled.'

	UNION ALL
	SELECT N'Last Execution',
			N'DATETIME',
			N'The last time that this query was executed.'

	UNION ALL
	SELECT N'Query Plan',
			N'XML',
			N'The query plan. Click to display a graphical plan or, if you need to patch SSMS, a pile of XML.'

	UNION ALL
	SELECT N'Plan Handle',
			N'VARBINARY(64)',
			N'An arbitrary identifier referring to the compiled plan this query is a part of.'

	UNION ALL
	SELECT N'SQL Handle',
			N'VARBINARY(64)',
			N'An arbitrary identifier referring to a batch or stored procedure that this query is a part of.'

	UNION ALL
	SELECT N'Query Hash',
			N'BINARY(8)',
			N'A hash of the query. Queries with the same query hash have similar logic but only differ by literal values or database.'

	UNION ALL
	SELECT N'Warnings',
			N'VARCHAR(MAX)',
			N'A list of individual warnings generated by this query.'

	UNION ALL
	SELECT N'AI Advice',
			N'NVARCHAR(MAX)',
			N'If called with @AI parameters, the results from your AI provider.' ;


           
	/* Configuration table description */
	SELECT N'Frequent Execution Threshold' AS [Configuration Parameter] ,
			N'100' AS [Default Value] ,
			N'Executions / Minute' AS [Unit of Measure] ,
			N'Executions / Minute before a "Frequent Execution Threshold" warning is triggered.' AS [Description]

	UNION ALL
	SELECT N'Parameter Sniffing Variance Percent' ,
			N'30' ,
			N'Percent' ,
			N'Variance required between min/max values and average values before a "Parameter Sniffing" warning is triggered. Applies to worker time and returned rows.'

	UNION ALL
	SELECT N'Parameter Sniffing IO Threshold' ,
			N'100,000' ,
			N'Logical reads' ,
			N'Minimum number of average logical reads before parameter sniffing checks are evaluated.'

	UNION ALL
	SELECT N'Cost Threshold for Parallelism Warning' AS [Configuration Parameter] ,
			N'10' ,
			N'Percent' ,
			N'Trigger a "Nearly Parallel" warning when a query''s cost is within X percent of the cost threshold for parallelism.'

	UNION ALL
	SELECT N'Long Running Query Warning' AS [Configuration Parameter] ,
			N'300' ,
			N'Seconds' ,
			N'Triggers a "Long Running Query Warning" when average duration, max CPU time, or max clock time is higher than this number.'

	UNION ALL
	SELECT N'Unused Memory Grant Warning' AS [Configuration Parameter] ,
			N'10' ,
			N'Percent' ,
			N'Triggers an "Unused Memory Grant Warning" when a query uses >= X percent of its memory grant.'

	UNION ALL
	SELECT N'AIModel Default' AS [Configuration Parameter] ,
			N'1' ,
			N'URL' ,
			N'Default provider is https://api.openai.com/v1/chat/completions. Override goes in the value_varchar_1, override system prompt in value_varchar_2. We use the lowest value column first.'

	RETURN;
	END; /* IF @Help = 1  */



/*Validate version*/
IF (
SELECT
  CASE 
     WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '8%' THEN 0
     WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '9%' THEN 0
	 ELSE 1
  END 
) = 0
BEGIN
	DECLARE @version_msg VARCHAR(8000); 
	SELECT @version_msg = 'Sorry, sp_BlitzCache doesn''t work on versions of SQL prior to 2008.' + REPLICATE(CHAR(13), 7933);
	PRINT @version_msg;
	RETURN;
END;

IF(@OutputType = 'NONE' AND (@OutputTableName IS NULL OR @OutputSchemaName IS NULL OR @OutputDatabaseName IS NULL))
BEGIN
    RAISERROR('This procedure should be called with a value for all @Output* parameters, as @OutputType is set to NONE',12,1);
    RETURN;
END;

IF(@OutputType = 'NONE') 
BEGIN
    SET @HideSummary = 1;
END;

/* Lets get @SortOrder set to lower case here for comparisons later */
SET @SortOrder = LOWER(@SortOrder);

/* Set @Top based on sort */
IF (
     @Top IS NULL
     AND @SortOrder IN ( 'all', 'all sort' )
   )
   BEGIN
         SET @Top = 5;
   END;

IF (
     @Top IS NULL
     AND @SortOrder NOT IN ( 'all', 'all sort' )
   )
   BEGIN
         SET @Top = 10;
   END;


IF OBJECT_ID ('tempdb..#configuration') IS NOT NULL
    DROP TABLE #configuration;

CREATE TABLE #configuration (
    parameter_name VARCHAR(100),
    value DECIMAL(38,0)
);

DECLARE @config_sql NVARCHAR(MAX)
IF @ConfigurationDatabaseName IS NOT NULL
BEGIN
   RAISERROR(N'Reading values from Configuration Table', 0, 1) WITH NOWAIT;
   SET @config_sql = N'INSERT INTO #configuration SELECT parameter_name, value FROM '
        + QUOTENAME(@ConfigurationDatabaseName)
        + '.' + QUOTENAME(@ConfigurationSchemaName)
        + '.' + QUOTENAME(@ConfigurationTableName)
        + ' ; ' ;
   EXEC(@config_sql);
END;

CREATE TABLE #ai_configuration
(Id INT PRIMARY KEY CLUSTERED,
 AI_Model NVARCHAR(100) INDEX AI_Model,
 AI_URL NVARCHAR(500),
 AI_Database_Scoped_Credential_Name NVARCHAR(500),
 AI_System_Prompt_Override NVARCHAR(4000),
 AI_Parameters NVARCHAR(4000),
 Payload_Template_Override NVARCHAR(4000),
 Timeout_Seconds TINYINT,
 Context INT,
 DefaultModel BIT DEFAULT 0);

DECLARE
    @AIConfigDatabaseName NVARCHAR(128) = CASE WHEN @AIConfig IS NULL THEN NULL ELSE PARSENAME(@AIConfig, 3) END,
    @AIConfigSchemaName NVARCHAR(258) = CASE WHEN @AIConfig IS NULL THEN NULL ELSE PARSENAME(@AIConfig, 2) END,
    @AIConfigTableName NVARCHAR(258) = CASE WHEN @AIConfig IS NULL THEN NULL ELSE PARSENAME(@AIConfig, 1) END,
    @AISystemPrompt NVARCHAR(4000),
    @AIParameters NVARCHAR(4000),
    @AIPayloadTemplate NVARCHAR(MAX),
    @AITimeoutSeconds TINYINT,
    @AIAdviceText NVARCHAR(MAX),
    @AIContext INT;


IF @AIConfig IS NOT NULL
BEGIN
   RAISERROR(N'Reading values from AI Configuration Table', 0, 1) WITH NOWAIT;
   SET @config_sql = N'INSERT INTO #ai_configuration (Id, AI_Model, AI_URL, AI_Database_Scoped_Credential_Name, AI_System_Prompt_Override, AI_Parameters, Payload_Template_Override, Timeout_Seconds, Context, DefaultModel)
        SELECT Id, AI_Model, AI_URL, AI_Database_Scoped_Credential_Name, AI_System_Prompt_Override, AI_Parameters, Payload_Template_Override, Timeout_Seconds, Context, DefaultModel FROM '
        + CASE WHEN @AIConfigDatabaseName IS NOT NULL THEN (QUOTENAME(@AIConfigDatabaseName) + N'.') ELSE N'' END
        + CASE WHEN @AIConfigSchemaName IS NOT NULL THEN (QUOTENAME(@AIConfigSchemaName) + N'.') ELSE N'' END
        + QUOTENAME(@AIConfigTableName) + N' WHERE (@AIModel IS NULL AND DefaultModel = 1) OR @AIModel IN (AI_Model, Nickname) ; ';
   EXEC sp_executesql @config_sql, N'@AIModel NVARCHAR(100)', @AIModel;
END;


IF @AI > 0
    BEGIN
    RAISERROR(N'Setting up AI configuration defaults', 0, 1) WITH NOWAIT;

    SELECT @ExpertMode = 1, @KeepCRLF = 1;

    IF @Debug = 2
        SELECT N'ai_configuration' AS TableLabel, * FROM #ai_configuration;

    IF @AI = 1 AND NOT EXISTS(SELECT * FROM sys.all_objects WHERE name = 'sp_invoke_external_rest_endpoint')
        BEGIN
        /* If someone was ambitious and they wanted to code a drop-in replacement for that stored proc,
        and use it in earlier versions of SQL Server, they could, and just use the same name and
        parameters, and we'd use it. I'm not coding support for different proc names. */
        SET @AI = 2
        RAISERROR(N'@AI was set to 1, but sp_invoke_external_rest_endpoint does not exist here, so we can''t call AI services. Setting @AI to 2 instead to just generate prompts.', 0, 1) WITH NOWAIT;
        END

    IF @AIModel IS NULL
        /* Check the config table */
        SELECT TOP 1 @AIModel = AI_Model, @AIURL = AI_URL,
            @AICredential = AI_Database_Scoped_Credential_Name,
            @AISystemPrompt = AI_System_Prompt_Override,
            @AIParameters = AI_Parameters,
            @AITimeoutSeconds = COALESCE(Timeout_Seconds, 230),
            @AIContext = Context,
            @AIPayloadTemplate = Payload_Template_Override
            FROM #ai_configuration
            WHERE DefaultModel = 1
            ORDER BY Id;
    ELSE
        SELECT TOP 1 @AIModel = AI_Model, 
            @AIURL = COALESCE(@AIURL, AI_URL),
            @AICredential = COALESCE(@AICredential, AI_Database_Scoped_Credential_Name),
            @AISystemPrompt = AI_System_Prompt_Override,
            @AIParameters = AI_Parameters,
            @AITimeoutSeconds = COALESCE(Timeout_Seconds, 230),
            @AIContext = Context,
            @AIPayloadTemplate = Payload_Template_Override
            FROM #ai_configuration
            ORDER BY Id;
        
    IF @AIModel IS NULL
        SET @AIModel = N'gpt-5-nano';
    
    IF @AIURL IS NULL OR @AIURL NOT LIKE N'http%'
        SET @AIURL = CASE 
            WHEN @AIModel LIKE 'gemini%' THEN N'https://generativelanguage.googleapis.com/v1beta/models/' + @AIModel + N':generateContent'
            ELSE N'https://api.openai.com/v1/chat/completions' /* Default to ChatGPT */
            END;

    /* Try to guess the credential based on the root of their URL: */
    IF @AICredential IS NULL
        SET @AICredential = LEFT(@AIURL, CHARINDEX('/', @AIURL, CHARINDEX('://', @AIURL) + 3));

    IF @AITimeoutSeconds IS NULL OR @AITimeoutSeconds < 1 OR @AITimeoutSeconds > 230
        SET @AITimeoutSeconds = 230;

    IF @AISystemPrompt IS NULL OR @AISystemPrompt = N''
        SET @AISystemPrompt = N'You are a very senior database developer working with Microsoft SQL Server and Azure SQL DB. You focus on real-world, actionable advice that will make a big difference, quickly. You value everyone''s time, and while you are friendly and courteous, you do not waste time with pleasantries or emoji because you work in a fast-paced corporate environment.

    You have a query that isn''t performing to end user expectations. You have been tasked with making serious improvements to it, quickly. You are not allowed to change server-level settings or make frivolous suggestions like updating statistics. Instead, you need to focus on query changes or index changes. 
    
    Do not offer followup options: the customer can only contact you once, so include all necessary information, tasks, and scripts in your initial reply. Render your output in Markdown, as it will be shown in plain text to the customer.';

    IF @AIModel LIKE 'gemini%' AND @AIPayloadTemplate IS NULL
        SET @AIPayloadTemplate = N'{
          "contents": [
            {
              "parts": [
                {"text": "@AISystemPrompt @CurrentAIPrompt"}
              ]
            }
          ]
        }';
    ELSE IF @AIPayloadTemplate IS NULL /* Default to ChatGPT format */
        SET @AIPayloadTemplate = N'{
                    "model": "@AIModel",
                    "messages": [
                        {
                            "role": "system",
                            "content": "@AISystemPrompt"
                        },
                        {
                            "role": "user",
                            "content": "@CurrentAIPrompt"
                        }
                    ]
                }';

    IF @Debug = 2 OR (@AI = 1 AND (@AIModel IS NULL OR @AIURL IS NULL OR @AISystemPrompt IS NULL OR @AICredential IS NULL OR @AIPayloadTemplate IS NULL))
        BEGIN