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)
    );
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 ,
    @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 BIT = 0,
	@CheckDateOverride DATETIMEOFFSET = NULL,
	@MinutesBack INT = NULL,
	@Version     VARCHAR(30) = NULL OUTPUT,
	@VersionDate DATETIME = NULL OUTPUT,
	@VersionCheckMode BIT = 0
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT @Version = '8.16', @VersionDate = '20230820';
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:
	 - SQL Server 2008 and 2008R2 have a bug in trigger stats, so that output is
	   excluded by default.
	 - @IgnoreQueryHashes and @OnlyQueryHashes require a CSV list of hashes
	   with no spaces between the hash values.

	Unknown limitations of this version:
	 - May or may not be vulnerable to the wick effect.

	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". 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.'

	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 negtive.';


	/* 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.' ;


           
	/* 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.';
	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);

/* If they want to sort by query hash, populate the @OnlyQueryHashes list for them */
IF @SortOrder LIKE 'query hash%'
	BEGIN
	RAISERROR('Beginning query hash sort', 0, 1) WITH NOWAIT;

    SELECT qs.query_hash, 
           MAX(qs.max_worker_time) AS max_worker_time,
           COUNT_BIG(*) AS records
    INTO #query_hash_grouped
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY (   SELECT pa.value
                    FROM   sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
                    WHERE  pa.attribute = 'dbid' ) AS ca
    GROUP BY qs.query_hash, ca.value
    HAVING COUNT_BIG(*) > 1
    ORDER BY max_worker_time DESC,
             records DESC;
    
    SELECT TOP (1)
	         @OnlyQueryHashes = STUFF((SELECT DISTINCT N',' + CONVERT(NVARCHAR(MAX), qhg.query_hash, 1) 
    FROM #query_hash_grouped AS qhg 
    WHERE qhg.query_hash <> 0x00
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'')
	OPTION(RECOMPILE);

	/* When they ran it, @SortOrder probably looked like 'query hash, cpu', so strip the first sort order out: */
    SELECT @SortOrder = LTRIM(REPLACE(REPLACE(@SortOrder,'query hash', ''), ',', ''));
	
	/* If they just called it with @SortOrder = 'query hash', set it to 'cpu' for backwards compatibility: */
	IF @SortOrder = '' SET @SortOrder = 'cpu';

	END


/* 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;

/* validate user inputs */
IF @Top IS NULL 
    OR @SortOrder IS NULL 
    OR @QueryFilter IS NULL 
    OR @Reanalyze IS NULL
BEGIN
    RAISERROR(N'Several parameters (@Top, @SortOrder, @QueryFilter, @renalyze) are required. Do not set them to NULL. Please try again.', 16, 1) WITH NOWAIT;
    RETURN;
END;

RAISERROR(N'Checking @MinutesBack validity.', 0, 1) WITH NOWAIT;
IF @MinutesBack IS NOT NULL
    BEGIN
        IF @MinutesBack > 0
            BEGIN
                RAISERROR(N'Setting @MinutesBack to a negative number', 0, 1) WITH NOWAIT;
				SET @MinutesBack *=-1;
            END;
        IF @MinutesBack = 0
            BEGIN
                RAISERROR(N'@MinutesBack can''t be 0, setting to -1', 0, 1) WITH NOWAIT;
				SET @MinutesBack = -1;
            END;
    END;



DECLARE @DurationFilter_i INT,
		@MinMemoryPerQuery INT,
        @msg NVARCHAR(4000),
		@NoobSaibot BIT = 0,
		@VersionShowsAirQuoteActualPlans BIT,
        @ObjectFullName NVARCHAR(2000),
        @user_perm_sql NVARCHAR(MAX) = N'',
        @user_perm_gb_out DECIMAL(10,2),
        @common_version DECIMAL(10,2),
        @buffer_pool_memory_gb DECIMAL(10,2),
        @user_perm_percent DECIMAL(10,2),
        @is_tokenstore_big BIT = 0,
        @sort NVARCHAR(MAX) = N'',
		@sort_filter NVARCHAR(MAX) = N'';


IF @SortOrder = 'sp_BlitzIndex'
BEGIN
	RAISERROR(N'OUTSTANDING!', 0, 1) WITH NOWAIT;
	SET @SortOrder = 'reads';
	SET @NoobSaibot = 1;

END


/* Change duration from seconds to milliseconds */
IF @DurationFilter IS NOT NULL
  BEGIN
  RAISERROR(N'Converting Duration Filter to milliseconds', 0, 1) WITH NOWAIT;
  SET @DurationFilter_i = CAST((@DurationFilter * 1000.0) AS INT);
  END; 

RAISERROR(N'Checking database validity', 0, 1) WITH NOWAIT;
SET @DatabaseName = LTRIM(RTRIM(@DatabaseName)) ;

IF SERVERPROPERTY('EngineEdition') IN (5, 6) AND DB_NAME() <> @DatabaseName
BEGIN
   RAISERROR('You specified a database name other than the current database, but Azure SQL DB does not allow you to change databases. Execute sp_BlitzCache from the database you want to analyze.', 16, 1);
   RETURN;
END;
IF (DB_ID(@DatabaseName)) IS NULL AND @DatabaseName <> N''
BEGIN
   RAISERROR('The database you specified does not exist. Please check the name and try again.', 16, 1);
   RETURN;
END;
IF (SELECT DATABASEPROPERTYEX(ISNULL(@DatabaseName, 'master'), 'Collation')) IS NULL AND SERVERPROPERTY('EngineEdition') NOT IN (5, 6, 8)
BEGIN
   RAISERROR('The database you specified is not readable. Please check the name and try again. Better yet, check your server.', 16, 1);
   RETURN;
END;

SELECT @MinMemoryPerQuery = CONVERT(INT, c.value) FROM sys.configurations AS c WHERE c.name = 'min memory per query (KB)';

SET @SortOrder = REPLACE(REPLACE(@SortOrder, 'average', 'avg'), '.', '');

SET @SortOrder = CASE 
                     WHEN @SortOrder IN ('executions per minute','execution per minute','executions / minute','execution / minute','xpm') THEN 'avg executions'
                     WHEN @SortOrder IN ('recent compilations','recent compilation','compile') THEN 'compiles'
                     WHEN @SortOrder IN ('read') THEN 'reads'
                     WHEN @SortOrder IN ('avg read') THEN 'avg reads'
                     WHEN @SortOrder IN ('write') THEN 'writes'
                     WHEN @SortOrder IN ('avg write') THEN 'avg writes'
                     WHEN @SortOrder IN ('memory grants') THEN 'memory grant'
                     WHEN @SortOrder IN ('avg memory grants') THEN 'avg memory grant'
                     WHEN @SortOrder IN ('unused grants','unused memory', 'unused memory grant', 'unused memory grants') THEN 'unused grant'
                     WHEN @SortOrder IN ('spill') THEN 'spills'
                     WHEN @SortOrder IN ('avg spill') THEN 'avg spills'
                     WHEN @SortOrder IN ('execution') THEN 'executions'
                 ELSE @SortOrder END							  
							  
RAISERROR(N'Checking sort order', 0, 1) WITH NOWAIT;
IF @SortOrder NOT IN ('cpu', 'avg cpu', 'reads', 'avg reads', 'writes', 'avg writes',
                       'duration', 'avg duration', 'executions', 'avg executions',
                       'compiles', 'memory grant', 'avg memory grant', 'unused grant',
					   'spills', 'avg spills', 'all', 'all avg', 'sp_BlitzIndex',
					   'query hash')
  BEGIN
  RAISERROR(N'Invalid sort order chosen, reverting to cpu', 16, 1) WITH NOWAIT;
  SET @SortOrder = 'cpu';
  END; 

SET @QueryFilter = LOWER(@QueryFilter);

IF LEFT(@QueryFilter, 3) NOT IN ('all', 'sta', 'pro', 'fun')
  BEGIN
  RAISERROR(N'Invalid query filter chosen. Reverting to all.', 0, 1) WITH NOWAIT;
  SET @QueryFilter = 'all';
  END;

IF @SkipAnalysis = 1
  BEGIN
  RAISERROR(N'Skip Analysis set to 1, hiding Summary', 0, 1) WITH NOWAIT;
  SET @HideSummary = 1;
  END; 

DECLARE @AllSortSql NVARCHAR(MAX) = N'';
DECLARE @VersionShowsMemoryGrants BIT;
IF EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_exec_query_stats') AND name = 'max_grant_kb')
    SET @VersionShowsMemoryGrants = 1;
ELSE
    SET @VersionShowsMemoryGrants = 0;

DECLARE @VersionShowsSpills BIT;
IF EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_exec_query_stats') AND name = 'max_spills')
    SET @VersionShowsSpills = 1;
ELSE
    SET @VersionShowsSpills = 0;

IF EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_exec_query_plan_stats') AND name = 'query_plan')
    SET @VersionShowsAirQuoteActualPlans = 1;
ELSE
    SET @VersionShowsAirQuoteActualPlans = 0;

IF @Reanalyze = 1 
  BEGIN
  IF OBJECT_ID('tempdb..##BlitzCacheResults') IS NULL
    BEGIN
    RAISERROR(N'##BlitzCacheResults does not exist, can''t reanalyze', 0, 1) WITH NOWAIT;
    SET @Reanalyze = 0;
	END
  ELSE
	BEGIN
	RAISERROR(N'Reanalyzing current data, skipping to results', 0, 1) WITH NOWAIT;
    GOTO Results;
	END;
  END;


IF @SortOrder IN ('all', 'all avg')
	BEGIN
	RAISERROR(N'Checking all sort orders, please be patient', 0, 1) WITH NOWAIT;
    GOTO AllSorts;
	END;

RAISERROR(N'Creating temp tables for internal processing', 0, 1) WITH NOWAIT;
IF OBJECT_ID('tempdb..#only_query_hashes') IS NOT NULL
    DROP TABLE #only_query_hashes ;

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

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