sp_BlitzQueryStore.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

DECLARE @msg NVARCHAR(MAX) = N'';

	-- Must be a compatible, on-prem version of SQL (2016+)
IF  (	(SELECT SERVERPROPERTY ('EDITION')) <> 'SQL Azure' 
	AND (SELECT PARSENAME(CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4)) < 13 
	)
	-- or Azure Database (not Azure Data Warehouse), running at database compat level 130+
OR	(	(SELECT SERVERPROPERTY ('EDITION')) = 'SQL Azure'
	AND (SELECT SERVERPROPERTY ('ENGINEEDITION')) <> 5
	AND (SELECT [compatibility_level] FROM sys.databases WHERE [name] = DB_NAME()) < 130
	)
BEGIN
	SELECT @msg = N'Sorry, sp_BlitzQueryStore doesn''t work on versions of SQL prior to 2016, or Azure Database compatibility < 130.' + REPLICATE(CHAR(13), 7933);
	PRINT @msg;
	RETURN;
END;

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

ALTER PROCEDURE dbo.sp_BlitzQueryStore
    @Help BIT = 0,
    @DatabaseName NVARCHAR(128) = NULL ,
    @Top INT = 3,
	@StartDate DATETIME2 = NULL,
	@EndDate DATETIME2 = NULL,
    @MinimumExecutionCount INT = NULL,
    @DurationFilter DECIMAL(38,4) = NULL ,
    @StoredProcName NVARCHAR(128) = NULL,
	@Failed BIT = 0,
	@PlanIdFilter INT = NULL,
	@QueryIdFilter INT = NULL,
    @ExportToExcel BIT = 0,
    @HideSummary BIT = 0 ,
	@SkipXML BIT = 0,
	@Debug BIT = 0,
	@ExpertMode BIT = 0,
	@VersionDate DATETIME = NULL OUTPUT
WITH RECOMPILE
AS
BEGIN /*First BEGIN*/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @Version NVARCHAR(30);
	SET @Version = '2.6';
	SET @VersionDate = '20180601';

DECLARE /*Variables for the variable Gods*/
		@msg NVARCHAR(MAX) = N'', --Used to format RAISERROR messages in some places
		@sql_select NVARCHAR(MAX) = N'', --Used to hold SELECT statements for dynamic SQL
		@sql_where NVARCHAR(MAX) = N'', -- Used to hold WHERE clause for dynamic SQL
		@duration_filter_ms DECIMAL(38,4) = (@DurationFilter * 1000.), --We accept Duration in seconds, but we filter in milliseconds (this is grandfathered from sp_BlitzCache)
		@execution_threshold INT = 1000, --Threshold at which we consider a query to be frequently executed
        @ctp_threshold_pct TINYINT = 10, --Percentage of CTFP at which we consider a query to be near parallel
        @long_running_query_warning_seconds BIGINT = 300 * 1000 ,--Number of seconds (converted to milliseconds) at which a query is considered long running
		@memory_grant_warning_percent INT = 10,--Percent of memory grant used compared to what's granted; used to trigger unused memory grant warning
		@ctp INT,--Holds the CTFP value for the server
		@min_memory_per_query INT,--Holds the server configuration value for min memory per query
		@cr NVARCHAR(1) = NCHAR(13),--Special character
		@lf NVARCHAR(1) = NCHAR(10),--Special character
		@tab NVARCHAR(1) = NCHAR(9),--Special character
		@error_severity INT,--Holds error info for try/catch blocks
		@error_state INT,--Holds error info for try/catch blocks
		@sp_params NVARCHAR(MAX) = N'@sp_Top INT, @sp_StartDate DATETIME2, @sp_EndDate DATETIME2, @sp_MinimumExecutionCount INT, @sp_MinDuration INT, @sp_StoredProcName NVARCHAR(128), @sp_PlanIdFilter INT, @sp_QueryIdFilter INT',--Holds parameters used in dynamic SQL
		@is_azure_db BIT = 0, --Are we using Azure? I'm not. You might be. That's cool.
		@compatibility_level TINYINT = 0, --Some functionality (T-SQL) isn't available in lower compat levels. We can use this to weed out those issues as we go.
		@log_size_mb DECIMAL(38,2) = 0,
		@avg_tempdb_data_file DECIMAL(38,2) = 0;

/*Grabs CTFP setting*/
SELECT  @ctp = NULLIF(CAST(value AS INT), 0)
FROM    sys.configurations
WHERE   name = N'cost threshold for parallelism'
OPTION (RECOMPILE);

/*Grabs min query memory setting*/
SELECT @min_memory_per_query = CONVERT(INT, c.value)
FROM   sys.configurations AS c
WHERE  c.name = N'min memory per query (KB)'
OPTION (RECOMPILE);

/*Grabs log size for datbase*/
SELECT @log_size_mb = AVG(((mf.size * 8) / 1024.))
FROM sys.master_files AS mf
WHERE mf.database_id = DB_ID(@DatabaseName)
AND mf.type_desc = 'LOG';

/*Grab avg tempdb file size*/
SELECT @avg_tempdb_data_file = AVG(((mf.size * 8) / 1024.))
FROM sys.master_files AS mf
WHERE mf.database_id = DB_ID('tempdb')
AND mf.type_desc = 'ROWS';


/*Help section*/

IF @Help = 1
	BEGIN
	
	SELECT N'You have requested assistance. It will arrive as soon as humanly possible.' AS [Take four red capsules, help is on the way];

	PRINT N'
	sp_BlitzQueryStore from http://FirstResponderKit.org
		
	This script displays your most resource-intensive queries from the Query Store,
	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:
	 - This query will not run on SQL Server versions less than 2016.
	 - This query will not run on Azure Databases with compatibility less than 130.
	 - This query will not run on Azure Data Warehouse.

	Unknown limitations of this version:
	 - Could be tickling
	
	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) 2016 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.
	';
	RETURN;

END;

/*Making sure your version is copasetic*/
IF  ( (SELECT SERVERPROPERTY ('EDITION')) = 'SQL Azure' )
	BEGIN
		SET @is_azure_db = 1;

		IF	(	(SELECT SERVERPROPERTY ('ENGINEEDITION')) <> 5
			OR	(SELECT [compatibility_level] FROM sys.databases WHERE [name] = DB_NAME()) < 130 
			)
		BEGIN
			SELECT @msg = N'Sorry, sp_BlitzQueryStore doesn''t work on Azure Data Warehouse, or Azure Databases with DB compatibility < 130.' + REPLICATE(CHAR(13), 7933);
			PRINT @msg;
			RETURN;
		END;
	END;
ELSE IF  ( (SELECT PARSENAME(CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4) ) < 13 )
	BEGIN
		SELECT @msg = N'Sorry, sp_BlitzQueryStore doesn''t work on versions of SQL prior to 2016.' + REPLICATE(CHAR(13), 7933);
		PRINT @msg;
		RETURN;
	END;

/*Making sure at least one database uses QS*/
IF  (	SELECT COUNT(*)
		FROM sys.databases AS d
		WHERE d.is_query_store_on = 1
		AND d.user_access_desc='MULTI_USER'
		AND d.state_desc = 'ONLINE'
		AND d.name NOT IN ('master', 'model', 'msdb', 'tempdb', '32767') 
		AND d.is_distributor = 0 ) = 0
	BEGIN
		SELECT @msg = N'You don''t currently have any databases with Query Store enabled.' + REPLICATE(CHAR(13), 7933);
		PRINT @msg;
		RETURN;
	END;

/*Making sure your databases are using QDS.*/
RAISERROR('Checking database validity', 0, 1) WITH NOWAIT;

IF (@is_azure_db = 1)
	SET @DatabaseName = DB_NAME();
ELSE
BEGIN	

	/*If we're on Azure we don't need to check all this @DatabaseName stuff...*/

	SET @DatabaseName = LTRIM(RTRIM(@DatabaseName));

	/*Did you set @DatabaseName?*/
	RAISERROR('Making sure [%s] isn''t NULL', 0, 1, @DatabaseName) WITH	NOWAIT;
	IF (@DatabaseName IS NULL)
	BEGIN
		RAISERROR('@DatabaseName cannot be NULL', 0, 1) WITH NOWAIT;
		RETURN;
	END;

	/*Does the database exist?*/
	RAISERROR('Making sure [%s] exists', 0, 1, @DatabaseName) WITH NOWAIT;
	IF ((DB_ID(@DatabaseName)) IS NULL)
	BEGIN
		RAISERROR('The @DatabaseName you specified ([%s]) does not exist. Please check the name and try again.', 0, 1, @DatabaseName) WITH	NOWAIT;
		RETURN;
	END;

	/*Is it online?*/
	RAISERROR('Making sure [%s] is online', 0, 1, @DatabaseName) WITH NOWAIT;
	IF (DATABASEPROPERTYEX(@DatabaseName, 'Status')) <> 'ONLINE'
	BEGIN
		RAISERROR('The @DatabaseName you specified ([%s]) is not readable. Please check the name and try again. Better yet, check your server.', 0, 1, @DatabaseName);
		RETURN;
	END;
END;

/*Does it have Query Store enabled?*/
RAISERROR('Making sure [%s] has Query Store enabled', 0, 1, @DatabaseName) WITH NOWAIT;
IF 	
	((DB_ID(@DatabaseName)) IS NOT NULL AND @DatabaseName <> '')
AND		
	(   SELECT DB_NAME(d.database_id)
		FROM sys.databases AS d
		WHERE d.is_query_store_on = 1
		AND d.user_access_desc='MULTI_USER'
		AND d.state_desc = 'ONLINE'
		AND DB_NAME(d.database_id) = @DatabaseName ) IS NULL
BEGIN
	RAISERROR('The @DatabaseName you specified ([%s]) does not have the Query Store enabled. Please check the name or settings, and try again.', 0, 1, @DatabaseName) WITH	NOWAIT;
	RETURN;
END;

/*Check database compat level*/

RAISERROR('Checking database compatibility level', 0, 1) WITH NOWAIT;

SELECT @compatibility_level = d.compatibility_level
FROM sys.databases AS d
WHERE d.name = @DatabaseName;

RAISERROR('The @DatabaseName you specified ([%s])is running in compatibility level ([%d]).', 0, 1, @DatabaseName, @compatibility_level) WITH NOWAIT;


/*Making sure top is set to something if NULL*/
IF ( @Top IS NULL )
   BEGIN
       SET @Top = 3;
   END;

/*
This section determines if you have the Query Store wait stats DMV
*/

RAISERROR('Checking for query_store_wait_stats', 0, 1) WITH NOWAIT;

DECLARE @ws_out INT,
		@waitstats BIT,
		@ws_sql NVARCHAR(MAX) = N'SELECT @i_out = COUNT(*) FROM ' + QUOTENAME(@DatabaseName) + N'.sys.all_objects WHERE name = ''query_store_wait_stats'' OPTION (RECOMPILE);',
		@ws_params NVARCHAR(MAX) = N'@i_out INT OUTPUT';

EXEC sys.sp_executesql @ws_sql, @ws_params, @i_out = @ws_out OUTPUT;

SELECT @waitstats = CASE @ws_out WHEN 0 THEN 0 ELSE 1 END;

SET @msg = N'Wait stats DMV ' + CASE @waitstats 
									WHEN 0 THEN N' does not exist, skipping.'
									WHEN 1 THEN N' exists, will analyze.'
							   END;
RAISERROR(@msg, 0, 1) WITH NOWAIT;

/*
This section determines if you have some additional columns present in 2017, in case they get back ported.
*/

RAISERROR('Checking for new columns in query_store_runtime_stats', 0, 1) WITH NOWAIT;

DECLARE @nc_out INT,
		@new_columns BIT,
		@nc_sql NVARCHAR(MAX) = N'SELECT @i_out = COUNT(*) 
							      FROM ' + QUOTENAME(@DatabaseName) + N'.sys.all_columns AS ac
								  WHERE OBJECT_NAME(object_id) = ''query_store_runtime_stats''
								  AND ac.name IN (
								  ''avg_num_physical_io_reads'',
								  ''last_num_physical_io_reads'',
								  ''min_num_physical_io_reads'',
								  ''max_num_physical_io_reads'',
								  ''avg_log_bytes_used'',
								  ''last_log_bytes_used'',
								  ''min_log_bytes_used'',
								  ''max_log_bytes_used'',
								  ''avg_tempdb_space_used'',
								  ''last_tempdb_space_used'',
								  ''min_tempdb_space_used'',
								  ''max_tempdb_space_used''
								  ) OPTION (RECOMPILE);',
		@nc_params NVARCHAR(MAX) = N'@i_out INT OUTPUT';

EXEC sys.sp_executesql @nc_sql, @ws_params, @i_out = @nc_out OUTPUT;

SELECT @new_columns = CASE @nc_out WHEN 12 THEN 1 ELSE 0 END;

SET @msg = N'New query_store_runtime_stats columns ' + CASE @new_columns 
									WHEN 0 THEN N' do not exist, skipping.'
									WHEN 1 THEN N' exist, will analyze.'
							   END;
RAISERROR(@msg, 0, 1) WITH NOWAIT;

 
/*
These are the temp tables we use
*/


/*
This one holds the grouped data that helps use figure out which periods to examine
*/

RAISERROR(N'Creating temp tables', 0, 1) WITH NOWAIT;

DROP TABLE IF EXISTS #grouped_interval;

CREATE TABLE #grouped_interval
(
    flat_date DATE NULL,
    start_range DATETIME NULL,
    end_range DATETIME NULL,
    total_avg_duration_ms DECIMAL(38, 2) NULL,
    total_avg_cpu_time_ms DECIMAL(38, 2) NULL,
    total_avg_logical_io_reads_mb DECIMAL(38, 2) NULL,
    total_avg_physical_io_reads_mb DECIMAL(38, 2) NULL,
    total_avg_logical_io_writes_mb DECIMAL(38, 2) NULL,
    total_avg_query_max_used_memory_mb DECIMAL(38, 2) NULL,
    total_rowcount DECIMAL(38, 2) NULL,
    total_count_executions BIGINT NULL,
	total_avg_log_bytes_mb DECIMAL(38, 2) NULL,
	total_avg_tempdb_space DECIMAL(38, 2) NULL,
	INDEX gi_ix_dates CLUSTERED (start_range, end_range)
);


/*
These are the plans we focus on based on what we find in the grouped intervals
*/
DROP TABLE IF EXISTS #working_plans;

CREATE TABLE #working_plans
(
    plan_id BIGINT,
    query_id BIGINT,
	pattern NVARCHAR(258),
	INDEX wp_ix_ids CLUSTERED (plan_id, query_id)
);


/*
These are the gathered metrics we get from query store to generate some warnings and help you find your worst offenders
*/
DROP TABLE IF EXISTS #working_metrics;

CREATE TABLE #working_metrics 
(
    database_name NVARCHAR(258),
	plan_id BIGINT,
    query_id BIGINT,
	/*these columns are from query_store_query*/
	proc_or_function_name NVARCHAR(258),
	batch_sql_handle VARBINARY(64),
	query_hash BINARY(8),
	query_parameterization_type_desc NVARCHAR(258),
	parameter_sniffing_symptoms NVARCHAR(4000),
	count_compiles BIGINT,
	avg_compile_duration DECIMAL(38,2),
	last_compile_duration DECIMAL(38,2),
	avg_bind_duration DECIMAL(38,2),
	last_bind_duration DECIMAL(38,2),
	avg_bind_cpu_time DECIMAL(38,2),
	last_bind_cpu_time DECIMAL(38,2),
	avg_optimize_duration DECIMAL(38,2),
	last_optimize_duration DECIMAL(38,2),
	avg_optimize_cpu_time DECIMAL(38,2),
	last_optimize_cpu_time DECIMAL(38,2),
	avg_compile_memory_kb DECIMAL(38,2),
	last_compile_memory_kb DECIMAL(38,2),
	/*These come from query_store_runtime_stats*/
	execution_type_desc NVARCHAR(128),
	first_execution_time DATETIME2,
	last_execution_time DATETIME2,
	count_executions BIGINT,
	avg_duration DECIMAL(38,2) ,
	last_duration DECIMAL(38,2),
	min_duration DECIMAL(38,2),
	max_duration DECIMAL(38,2),
	avg_cpu_time DECIMAL(38,2),
	last_cpu_time DECIMAL(38,2),
	min_cpu_time DECIMAL(38,2),
	max_cpu_time DECIMAL(38,2),
	avg_logical_io_reads DECIMAL(38,2),
	last_logical_io_reads DECIMAL(38,2),
	min_logical_io_reads DECIMAL(38,2),
	max_logical_io_reads DECIMAL(38,2),
	avg_logical_io_writes DECIMAL(38,2),
	last_logical_io_writes DECIMAL(38,2),
	min_logical_io_writes DECIMAL(38,2),
	max_logical_io_writes DECIMAL(38,2),
	avg_physical_io_reads DECIMAL(38,2),
	last_physical_io_reads DECIMAL(38,2),
	min_physical_io_reads DECIMAL(38,2),
	max_physical_io_reads DECIMAL(38,2),
	avg_clr_time DECIMAL(38,2),
	last_clr_time DECIMAL(38,2),
	min_clr_time DECIMAL(38,2),
	max_clr_time DECIMAL(38,2),
	avg_dop BIGINT,
	last_dop BIGINT,
	min_dop BIGINT,
	max_dop BIGINT,
	avg_query_max_used_memory DECIMAL(38,2),
	last_query_max_used_memory DECIMAL(38,2),
	min_query_max_used_memory DECIMAL(38,2),
	max_query_max_used_memory DECIMAL(38,2),
	avg_rowcount DECIMAL(38,2),
	last_rowcount DECIMAL(38,2),
	min_rowcount DECIMAL(38,2),
	max_rowcount  DECIMAL(38,2),
	/*These are 2017 only, AFAIK*/
	avg_num_physical_io_reads DECIMAL(38,2),
	last_num_physical_io_reads DECIMAL(38,2),
	min_num_physical_io_reads DECIMAL(38,2),
	max_num_physical_io_reads DECIMAL(38,2),
	avg_log_bytes_used DECIMAL(38,2),
	last_log_bytes_used DECIMAL(38,2),
	min_log_bytes_used DECIMAL(38,2),
	max_log_bytes_used DECIMAL(38,2),
	avg_tempdb_space_used DECIMAL(38,2),
	last_tempdb_space_used DECIMAL(38,2),
	min_tempdb_space_used DECIMAL(38,2),
	max_tempdb_space_used DECIMAL(38,2),
	/*These are computed columns to make some stuff easier down the line*/
	total_compile_duration AS avg_compile_duration * count_compiles,
	total_bind_duration AS avg_bind_duration * count_compiles,
	total_bind_cpu_time AS avg_bind_cpu_time * count_compiles,
	total_optimize_duration AS avg_optimize_duration * count_compiles,
	total_optimize_cpu_time AS avg_optimize_cpu_time * count_compiles,
	total_compile_memory_kb AS avg_compile_memory_kb * count_compiles,
	total_duration AS avg_duration * count_executions,
	total_cpu_time AS avg_cpu_time * count_executions,
	total_logical_io_reads AS avg_logical_io_reads * count_executions,
	total_logical_io_writes AS avg_logical_io_writes * count_executions,
	total_physical_io_reads AS avg_physical_io_reads * count_executions,
	total_clr_time AS avg_clr_time * count_executions,
	total_query_max_used_memory AS avg_query_max_used_memory * count_executions,
	total_rowcount AS avg_rowcount * count_executions,
	total_num_physical_io_reads AS avg_num_physical_io_reads * count_executions,
	total_log_bytes_used AS avg_log_bytes_used * count_executions,
	total_tempdb_space_used AS avg_tempdb_space_used * count_executions,
	xpm AS NULLIF(count_executions, 0) / NULLIF(DATEDIFF(MINUTE, first_execution_time, last_execution_time), 0),
	INDEX wm_ix_ids CLUSTERED (plan_id, query_id, query_hash)
);


/*
This is where we store some additional metrics, along with the query plan and text
*/
DROP TABLE IF EXISTS #working_plan_text;

CREATE TABLE #working_plan_text 
(
	database_name NVARCHAR(258),
    plan_id BIGINT,
    query_id BIGINT,
	/*These are from query_store_plan*/
	plan_group_id BIGINT,
	engine_version NVARCHAR(64),
	compatibility_level INT,
	query_plan_hash BINARY(8),
	query_plan_xml XML,
	is_online_index_plan BIT,
	is_trivial_plan BIT,
	is_parallel_plan BIT,
	is_forced_plan BIT,
	is_natively_compiled BIT,
	force_failure_count BIGINT,
	last_force_failure_reason_desc NVARCHAR(258),
	count_compiles BIGINT,
	initial_compile_start_time DATETIME2,
	last_compile_start_time DATETIME2,
	last_execution_time DATETIME2,
	avg_compile_duration DECIMAL(38,2),
	last_compile_duration BIGINT,
	min_grant_kb DECIMAL(38,2), --This column is updated from dm_exec_query_stats when sql_handle for query exists there
	max_used_grant_kb DECIMAL(38,2), --This column is updated from dm_exec_query_stats when sql_handle for query exists there
	percent_memory_grant_used AS CONVERT(MONEY, ISNULL(NULLIF(( max_used_grant_kb * 1.00 ), 0) / NULLIF(min_grant_kb, 0), 0) * 100.),
	/*These are from query_store_query*/
	query_sql_text NVARCHAR(MAX),
	statement_sql_handle VARBINARY(64),
	is_part_of_encrypted_module BIT,
	has_restricted_text BIT,
	/*This is from query_context_settings*/
	context_settings NVARCHAR(512),
	/*This is from #working_plans*/
	pattern NVARCHAR(512),
	top_three_waits NVARCHAR(MAX),
	INDEX wpt_ix_ids CLUSTERED (plan_id, query_id, query_plan_hash)
); 


/*
This is where we store warnings that we generate from the XML and metrics
*/
DROP TABLE IF EXISTS #working_warnings;

CREATE TABLE #working_warnings 
(
    plan_id BIGINT,
    query_id BIGINT,
	query_hash BINARY(8),
	sql_handle VARBINARY(64),
	proc_or_function_name NVARCHAR(258),
	plan_multiple_plans BIT,
    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,
    long_running BIT,
    downlevel_estimator BIT,
    implicit_conversions BIT,
    tvf_estimate BIT,
    compile_timeout BIT,
    compile_memory_limit_exceeded BIT,
    warning_no_join_predicate BIT,
    query_cost FLOAT,
    missing_index_count INT,
    unmatched_index_count INT,
    is_trivial BIT,
	trace_flags_session NVARCHAR(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 NVARCHAR(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,
	is_slow_plan BIT,
	is_compile_more BIT,
	index_spool_cost FLOAT,
	index_spool_rows FLOAT,
	is_spool_expensive BIT,
	is_spool_more_rows BIT,
	estimated_rows FLOAT,
	is_bad_estimate BIT, 
	is_big_log BIT,
	is_big_tempdb BIT,
	is_paul_white_electric BIT,
	is_row_goal BIT,
	is_mstvf BIT,
	is_mm_join BIT,
    is_nonsargable BIT,
	busy_loops BIT,
	tvf_join BIT,
	implicit_conversion_info XML,
	cached_execution_parameters XML,
	missing_indexes XML,
    warnings NVARCHAR(4000)
	INDEX ww_ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);


DROP TABLE IF EXISTS #working_wait_stats;

CREATE TABLE #working_wait_stats
(
    plan_id BIGINT,
	wait_category TINYINT,
	wait_category_desc NVARCHAR(258),
	total_query_wait_time_ms BIGINT,
	avg_query_wait_time_ms	 DECIMAL(38, 2),
	last_query_wait_time_ms	BIGINT,
	min_query_wait_time_ms	BIGINT,
	max_query_wait_time_ms	BIGINT,
	wait_category_mapped AS CASE wait_category
								WHEN 0  THEN N'UNKNOWN'
								WHEN 1  THEN N'SOS_SCHEDULER_YIELD'
								WHEN 2  THEN N'THREADPOOL'
								WHEN 3  THEN N'LCK_M_%'
								WHEN 4  THEN N'LATCH_%'
								WHEN 5  THEN N'PAGELATCH_%'
								WHEN 6  THEN N'PAGEIOLATCH_%'
								WHEN 7  THEN N'RESOURCE_SEMAPHORE_QUERY_COMPILE'
								WHEN 8  THEN N'CLR%, SQLCLR%'
								WHEN 9  THEN N'DBMIRROR%'
								WHEN 10 THEN N'XACT%, DTC%, TRAN_MARKLATCH_%, MSQL_XACT_%, TRANSACTION_MUTEX'
								WHEN 11 THEN N'SLEEP_%, LAZYWRITER_SLEEP, SQLTRACE_BUFFER_FLUSH, SQLTRACE_INCREMENTAL_FLUSH_SLEEP, SQLTRACE_WAIT_ENTRIES, FT_IFTS_SCHEDULER_IDLE_WAIT, XE_DISPATCHER_WAIT, REQUEST_FOR_DEADLOCK_SEARCH, LOGMGR_QUEUE, ONDEMAND_TASK_QUEUE, CHECKPOINT_QUEUE, XE_TIMER_EVENT'
								WHEN 12 THEN N'PREEMPTIVE_%'
								WHEN 13 THEN N'BROKER_% (but not BROKER_RECEIVE_WAITFOR)'
								WHEN 14 THEN N'LOGMGR, LOGBUFFER, LOGMGR_RESERVE_APPEND, LOGMGR_FLUSH, LOGMGR_PMM_LOG, CHKPT, WRITELOG'
								WHEN 15 THEN N'ASYNC_NETWORK_IO, NET_WAITFOR_PACKET, PROXY_NETWORK_IO, EXTERNAL_SCRIPT_NETWORK_IOF'
								WHEN 16 THEN N'CXPACKET, EXCHANGE, CXCONSUMER'
								WHEN 17 THEN N'RESOURCE_SEMAPHORE, CMEMTHREAD, CMEMPARTITIONED, EE_PMOLOCK, MEMORY_ALLOCATION_EXT, RESERVED_MEMORY_ALLOCATION_EXT, MEMORY_GRANT_UPDATE'
								WHEN 18 THEN N'WAITFOR, WAIT_FOR_RESULTS, BROKER_RECEIVE_WAITFOR'
								WHEN 19 THEN N'TRACEWRITE, SQLTRACE_LOCK, SQLTRACE_FILE_BUFFER, SQLTRACE_FILE_WRITE_IO_COMPLETION, SQLTRACE_FILE_READ_IO_COMPLETION, SQLTRACE_PENDING_BUFFER_WRITERS, SQLTRACE_SHUTDOWN, QUERY_TRACEOUT, TRACE_EVTNOTIFF'
								WHEN 20 THEN N'FT_RESTART_CRAWL, FULLTEXT GATHERER, MSSEARCH, FT_METADATA_MUTEX, FT_IFTSHC_MUTEX, FT_IFTSISM_MUTEX, FT_IFTS_RWLOCK, FT_COMPROWSET_RWLOCK, FT_MASTER_MERGE, FT_PROPERTYLIST_CACHE, FT_MASTER_MERGE_COORDINATOR, PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC'
								WHEN 21 THEN N'ASYNC_IO_COMPLETION, IO_COMPLETION, BACKUPIO, WRITE_COMPLETION, IO_QUEUE_LIMIT, IO_RETRY'
								WHEN 22 THEN N'SE_REPL_%, REPL_%, HADR_% (but not HADR_THROTTLE_LOG_RATE_GOVERNOR), PWAIT_HADR_%, REPLICA_WRITES, FCB_REPLICA_WRITE, FCB_REPLICA_READ, PWAIT_HADRSIM'
								WHEN 23 THEN N'LOG_RATE_GOVERNOR, POOL_LOG_RATE_GOVERNOR, HADR_THROTTLE_LOG_RATE_GOVERNOR, INSTANCE_LOG_RATE_GOVERNOR'
							END,
    INDEX wws_ix_ids CLUSTERED ( plan_id)
);


/*
The next three tables hold plan XML parsed out to different degrees 
*/
DROP TABLE IF EXISTS #statements;

CREATE TABLE #statements 
(
    plan_id BIGINT,
    query_id BIGINT,
	query_hash BINARY(8),
	sql_handle VARBINARY(64),
	statement XML,
	INDEX s_ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);


DROP TABLE IF EXISTS #query_plan;

CREATE TABLE #query_plan 
(
    plan_id BIGINT,
    query_id BIGINT,
	query_hash BINARY(8),
	sql_handle VARBINARY(64),
	query_plan XML,
	INDEX qp_ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);


DROP TABLE IF EXISTS #relop;

CREATE TABLE #relop 
(
    plan_id BIGINT,
    query_id BIGINT,
	query_hash BINARY(8),
	sql_handle VARBINARY(64),
	relop XML,
	INDEX ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);


DROP TABLE IF EXISTS #plan_cost;

CREATE TABLE #plan_cost 
(
	query_plan_cost DECIMAL(38,2),
	sql_handle VARBINARY(64),
	plan_id INT,
	INDEX px_ix_ids CLUSTERED (sql_handle, plan_id)
);


DROP TABLE IF EXISTS #est_rows;

CREATE TABLE #est_rows 
(
	estimated_rows DECIMAL(38,2),
	query_hash BINARY(8),
	INDEX px_ix_ids CLUSTERED (query_hash)
);


DROP TABLE IF EXISTS #stats_agg;

CREATE TABLE #stats_agg
(
    sql_handle VARBINARY(64),
    last_update DATETIME2,
    modification_count DECIMAL(38, 2),
    sampling_percent DECIMAL(38, 2),
    [statistics] NVARCHAR(258),
    [table] NVARCHAR(258),
    [schema] NVARCHAR(258),
    [database] NVARCHAR(258),
	INDEX sa_ix_ids CLUSTERED (sql_handle)
);


DROP TABLE IF EXISTS #trace_flags;

CREATE TABLE #trace_flags 
(
	sql_handle VARBINARY(54),
	global_trace_flags NVARCHAR(4000),
	session_trace_flags NVARCHAR(4000),
	INDEX tf_ix_ids CLUSTERED (sql_handle)
);


DROP TABLE IF EXISTS #warning_results;	

CREATE TABLE #warning_results 
(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CheckID INT,
    Priority TINYINT,
    FindingsGroup NVARCHAR(50),
    Finding NVARCHAR(200),
    URL NVARCHAR(200),
    Details NVARCHAR(4000)
);

/*These next three tables hold information about implicit conversion and cached parameters */
DROP TABLE IF EXISTS #stored_proc_info;	

CREATE TABLE #stored_proc_info
(
	sql_handle VARBINARY(64),
    query_hash BINARY(8),
    variable_name NVARCHAR(258),
    variable_datatype NVARCHAR(258),
	converted_column_name NVARCHAR(258),
    compile_time_value NVARCHAR(258),
    proc_name NVARCHAR(1000),
    column_name NVARCHAR(4000),
    converted_to NVARCHAR(258),
	set_options NVARCHAR(1000)
	INDEX tf_ix_ids CLUSTERED (sql_handle, query_hash)
);

DROP TABLE IF EXISTS #variable_info;

CREATE TABLE #variable_info
(
    query_hash BINARY(8),
    sql_handle VARBINARY(64),
    proc_name NVARCHAR(1000),
    variable_name NVARCHAR(258),
    variable_datatype NVARCHAR(258),
    compile_time_value NVARCHAR(258),
	INDEX vif_ix_ids CLUSTERED (sql_handle, query_hash)
);

DROP TABLE IF EXISTS #conversion_info;

CREATE TABLE #conversion_info
(
    query_hash BINARY(8),
    sql_handle VARBINARY(64),
    proc_name NVARCHAR(128),
    expression NVARCHAR(4000),
    at_charindex AS CHARINDEX('@', expression),
    bracket_charindex AS CHARINDEX(']', expression, CHARINDEX('@', expression)) - CHARINDEX('@', expression),
    comma_charindex AS CHARINDEX(',', expression) + 1,
    second_comma_charindex AS
        CHARINDEX(',', expression, CHARINDEX(',', expression) + 1) - CHARINDEX(',', expression) - 1,
    equal_charindex AS CHARINDEX('=', expression) + 1,
    paren_charindex AS CHARINDEX('(', expression) + 1,
    comma_paren_charindex AS
        CHARINDEX(',', expression, CHARINDEX('(', expression) + 1) - CHARINDEX('(', expression) - 1,
    convert_implicit_charindex AS CHARINDEX('=CONVERT_IMPLICIT', expression),
	INDEX cif_ix_ids CLUSTERED (sql_handle, query_hash)
);

/* These tables support the Missing Index details clickable*/


DROP TABLE IF EXISTS #missing_index_xml;

CREATE TABLE #missing_index_xml
(
    query_hash BINARY(8),
    sql_handle VARBINARY(64),
    impact FLOAT,
    index_xml XML
);

DROP TABLE IF EXISTS #missing_index_schema;

CREATE TABLE #missing_index_schema
(
    query_hash BINARY(8),
    sql_handle VARBINARY(64),
    impact FLOAT,
    database_name NVARCHAR(128),
    schema_name NVARCHAR(128),
    table_name NVARCHAR(128),
    index_xml XML
);


DROP TABLE IF EXISTS #missing_index_usage;

CREATE TABLE #missing_index_usage
(
    query_hash BINARY(8),
    sql_handle VARBINARY(64),
    impact FLOAT,
    database_name NVARCHAR(128),
    schema_name NVARCHAR(128),
    table_name NVARCHAR(128),
	usage NVARCHAR(128),
    index_xml XML
);

DROP TABLE IF EXISTS #missing_index_detail;

CREATE TABLE #missing_index_detail
(
    query_hash BINARY(8),
    sql_handle VARBINARY(64),
    impact FLOAT,
    database_name NVARCHAR(128),
    schema_name NVARCHAR(128),
    table_name NVARCHAR(128),
    usage NVARCHAR(128),
    column_name NVARCHAR(128)
);


DROP TABLE IF EXISTS #missing_index_pretty;

CREATE TABLE #missing_index_pretty
(
    query_hash BINARY(8),
    sql_handle VARBINARY(64),
    impact FLOAT,
    database_name NVARCHAR(128),
    schema_name NVARCHAR(128),
    table_name NVARCHAR(128),
	equality NVARCHAR(MAX),
	inequality NVARCHAR(MAX),
	[include] NVARCHAR(MAX),
	details AS N'/* '
	           + CHAR(10) 
			   + N'The Query Processor estimates that implementing the following index could improve the query cost by ' 
			   + CONVERT(NVARCHAR(30), impact)
			   + '%.'
			   + CHAR(10)
			   + N'*/'
			   + CHAR(10) + CHAR(13) 
			   + N'/* '
			   + CHAR(10)
			   + N'USE '
			   + database_name
			   + CHAR(10)
			   + N'GO'
			   + CHAR(10) + CHAR(13)
			   + N'CREATE NONCLUSTERED INDEX ix_'
			   + ISNULL(REPLACE(REPLACE(REPLACE(equality,'[', ''), ']', ''),   ', ', '_'), '')
			   + ISNULL(REPLACE(REPLACE(REPLACE(inequality,'[', ''), ']', ''), ', ', '_'), '')
			   + CASE WHEN [include] IS NOT NULL THEN + N'Includes' ELSE N'' END
			   + CHAR(10)
			   + N' ON '
			   + schema_name
			   + N'.'
			   + table_name
			   + N' (' + 
			   + CASE WHEN equality IS NOT NULL 
					  THEN equality
						+ CASE WHEN inequality IS NOT NULL
							   THEN N', ' + inequality
							   ELSE N''
						  END
					 ELSE inequality
				 END			   
			   + N')' 
			   + CHAR(10)
			   + CASE WHEN include IS NOT NULL
					  THEN N'INCLUDE (' + include + N') WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);'
					  ELSE N' WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);'
				 END
			   + CHAR(10)
			   + N'GO'
			   + CHAR(10)
			   + N'*/'
);

/*Sets up WHERE clause that gets used quite a bit*/

--Date stuff
--If they're both NULL, we'll just look at the last 7 days
IF (@StartDate IS NULL AND @EndDate IS NULL)
	BEGIN
	RAISERROR(N'@StartDate and @EndDate are NULL, checking last 7 days', 0, 1) WITH NOWAIT;
	SET @sql_where += N' AND qsrs.last_execution_time >= DATEADD(DAY, -7, DATEDIFF(DAY, 0, SYSDATETIME() ))
					  ';
	END;

--Hey, that's nice of me
IF @StartDate IS NOT NULL
	BEGIN 
	RAISERROR(N'Setting start date filter', 0, 1) WITH NOWAIT;
	SET @sql_where += N' AND qsrs.last_execution_time >= @sp_StartDate 
					   ';
	END; 

--Alright, sensible
IF @EndDate IS NOT NULL 
	BEGIN 
	RAISERROR(N'Setting end date filter', 0, 1) WITH NOWAIT;
	SET @sql_where += N' AND qsrs.last_execution_time < @sp_EndDate 
					   ';
    END;

--C'mon, why would you do that?
IF (@StartDate IS NULL AND @EndDate IS NOT NULL)
	BEGIN 
	RAISERROR(N'Setting reasonable start date filter', 0, 1) WITH NOWAIT;
	SET @sql_where += N' AND qsrs.last_execution_time >= DATEADD(DAY, -7, @sp_EndDate) 
					   ';
    END;

--Jeez, abusive
IF (@StartDate IS NOT NULL AND @EndDate IS NULL)
	BEGIN 
	RAISERROR(N'Setting reasonable end date filter', 0, 1) WITH NOWAIT;
	SET @sql_where += N' AND qsrs.last_execution_time < DATEADD(DAY, 7, @sp_StartDate) 
					   ';
    END;

--I care about minimum execution counts
IF @MinimumExecutionCount IS NOT NULL 
	BEGIN 
	RAISERROR(N'Setting execution filter', 0, 1) WITH NOWAIT;
	SET @sql_where += N' AND qsrs.count_executions >= @sp_MinimumExecutionCount 
					   ';
    END;

--You care about stored proc names
IF @StoredProcName IS NOT NULL 
	BEGIN 
	RAISERROR(N'Setting stored proc filter', 0, 1) WITH NOWAIT;
	SET @sql_where += N' AND object_name(qsq.object_id, DB_ID(' + QUOTENAME(@DatabaseName, '''') + N')) = @sp_StoredProcName 
					   ';
    END;

--I will always love you, but hopefully this query will eventually end
IF @DurationFilter IS NOT NULL
    BEGIN 
	RAISERROR(N'Setting duration filter', 0, 1) WITH NOWAIT;
	SET  @sql_where += N' AND (qsrs.avg_duration / 1000.) >= @sp_MinDuration 
					    '; 
	END; 

--I don't know why you'd go looking for failed queries, but hey
IF (@Failed = 0 OR @Failed IS NULL)
    BEGIN 
	RAISERROR(N'Setting failed query filter to 0', 0, 1) WITH NOWAIT;
	SET  @sql_where += N' AND qsrs.execution_type = 0 
					    '; 
	END; 
IF (@Failed = 1)
    BEGIN 
	RAISERROR(N'Setting failed query filter to 3, 4', 0, 1) WITH NOWAIT;
	SET  @sql_where += N' AND qsrs.execution_type IN (3, 4) 
					    '; 
	END;  

/*Filtering for plan_id or query_id*/
IF (@PlanIdFilter IS NOT NULL)
    BEGIN 
	RAISERROR(N'Setting plan_id filter', 0, 1) WITH NOWAIT;
	SET  @sql_where += N' AND qsp.plan_id = @sp_PlanIdFilter 
					    '; 
	END; 

IF (@QueryIdFilter IS NOT NULL)
    BEGIN 
	RAISERROR(N'Setting query_id filter', 0, 1) WITH NOWAIT;
	SET  @sql_where += N' AND qsq.query_id = @sp_QueryIdFilter 
					    '; 
	END; 

IF @Debug = 1
	RAISERROR(N'Starting WHERE clause:', 0, 1) WITH NOWAIT;
	PRINT @sql_where;

IF @sql_where IS NULL
    BEGIN
        RAISERROR(N'@sql_where is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

IF (@ExportToExcel = 1 OR @SkipXML = 1)	
	BEGIN
	RAISERROR(N'Exporting to Excel or skipping XML, hiding summary', 0, 1) WITH NOWAIT;
	SET @HideSummary = 1;
	END;

IF @StoredProcName IS NOT NULL
	BEGIN 
	
	DECLARE @sql NVARCHAR(MAX);
	DECLARE @out INT;
	DECLARE @proc_params NVARCHAR(MAX) = N'@sp_StartDate DATETIME2, @sp_EndDate DATETIME2, @sp_MinimumExecutionCount INT, @sp_MinDuration INT, @sp_StoredProcName NVARCHAR(128), @sp_PlanIdFilter INT, @sp_QueryIdFilter INT, @i_out INT OUTPUT';
	
	
	SET @sql = N'SELECT @i_out = COUNT(*) 
				 FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
				 JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
				 ON qsp.plan_id = qsrs.plan_id
				 JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
				 ON qsq.query_id = qsp.query_id
				 WHERE    1 = 1
				        AND qsq.is_internal_query = 0
				 	    AND qsp.query_plan IS NOT NULL 
				 ';
	
	SET @sql += @sql_where;

	EXEC sys.sp_executesql @sql, 
						   @proc_params, 
						   @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter, @i_out = @out OUTPUT;
	
	IF @out = 0
		BEGIN	

		SET @msg = N'We couldn''t find the Stored Procedure ' + QUOTENAME(@StoredProcName) + N' in the Query Store views for ' + QUOTENAME(@DatabaseName) + N' between ' + CONVERT(NVARCHAR(30), ISNULL(@StartDate, DATEADD(DAY, -7, DATEDIFF(DAY, 0, SYSDATETIME() ))) ) + N' and ' + CONVERT(NVARCHAR(30), ISNULL(@EndDate, SYSDATETIME())) +
					 '. Try removing schema prefixes or adjusting dates. If it was executed from a different database context, try searching there instead.';
		RAISERROR(@msg, 0, 1) WITH NOWAIT;

		SELECT @msg AS [Blue Flowers, Blue Flowers, Blue Flowers];
	
		RETURN;
	
		END; 
	
	END;




/*
This is our grouped interval query.

By default, it looks at queries: 
	In the last 7 days
	That aren't system queries
	That have a query plan (some won't, if nested level is > 128, along with other reasons)
	And haven't failed
	This stuff, along with some other options, will be configurable in the stored proc

*/

IF @sql_where IS NOT NULL
BEGIN TRY
	BEGIN

	RAISERROR(N'Populating temp tables', 0, 1) WITH NOWAIT;

RAISERROR(N'Gathering intervals', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
SELECT   CONVERT(DATE, qsrs.last_execution_time) AS flat_date,
         MIN(DATEADD(HOUR, DATEDIFF(HOUR, 0, qsrs.last_execution_time), 0)) AS start_range,
         MAX(DATEADD(HOUR, DATEDIFF(HOUR, 0, qsrs.last_execution_time) + 1, 0)) AS end_range,
         SUM(qsrs.avg_duration / 1000.) / SUM(qsrs.count_executions) AS total_avg_duration_ms,
         SUM(qsrs.avg_cpu_time / 1000.) / SUM(qsrs.count_executions) AS total_avg_cpu_time_ms,
         SUM((qsrs.avg_logical_io_reads * 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_logical_io_reads_mb,
         SUM((qsrs.avg_physical_io_reads* 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_physical_io_reads_mb,
         SUM((qsrs.avg_logical_io_writes* 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_logical_io_writes_mb,
         SUM(( qsrs.avg_query_max_used_memory * 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_query_max_used_memory_mb,
         SUM(qsrs.avg_rowcount) AS total_rowcount,
         SUM(qsrs.count_executions) AS total_count_executions';
		 IF @new_columns = 1
			BEGIN
				SET @sql_select += N',
									 SUM((qsrs.avg_log_bytes_used) / 1048576.) / SUM(qsrs.count_executions) AS total_avg_log_bytes_mb,
									 SUM(avg_tempdb_space_used) /  SUM(qsrs.count_executions) AS total_avg_tempdb_space 
									 ';
			END;
		IF @new_columns = 0
			BEGIN
				SET @sql_select += N',
									NULL AS total_avg_log_bytes_mb, 
									NULL AS total_avg_tempdb_space
									';
			END;


SET @sql_select += N'FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
					 JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
					 ON qsp.plan_id = qsrs.plan_id
					 JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
					 ON qsq.query_id = qsp.query_id
					 WHERE  1 = 1
					        AND qsq.is_internal_query = 0
					 	    AND qsp.query_plan IS NOT NULL
					 	  ';


SET @sql_select += @sql_where;

SET @sql_select += 
			N'GROUP BY CONVERT(DATE, qsrs.last_execution_time)
					OPTION (RECOMPILE);
			';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

INSERT #grouped_interval WITH (TABLOCK)
		( flat_date, start_range, end_range, total_avg_duration_ms, 
		  total_avg_cpu_time_ms, total_avg_logical_io_reads_mb, total_avg_physical_io_reads_mb, 
		  total_avg_logical_io_writes_mb, total_avg_query_max_used_memory_mb, total_rowcount, 
		  total_count_executions, total_avg_log_bytes_mb, total_avg_tempdb_space )

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;


/*
The next group of queries looks at plans in the ranges we found in the grouped interval query

We take the highest value from each metric (duration, cpu, etc) and find the top plans by that metric in the range

They insert into the #working_plans table
*/



/*Get longest duration plans*/

RAISERROR(N'Gathering longest duration plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH duration_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_duration_ms DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
         qsp.plan_id, qsp.query_id, ''duration''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     duration_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
	AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_duration DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*Get longest cpu plans*/

RAISERROR(N'Gathering highest cpu plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH cpu_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_cpu_time_ms DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top )
		 qsp.plan_id, qsp.query_id, ''cpu''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     cpu_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_cpu_time DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*Get highest logical read plans*/

RAISERROR(N'Gathering highest logical read plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH logical_reads_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_logical_io_reads_mb DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
		 qsp.plan_id, qsp.query_id, ''logical reads''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     logical_reads_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_logical_io_reads DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*Get highest physical read plans*/

RAISERROR(N'Gathering highest physical read plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH physical_read_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_physical_io_reads_mb DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
		 qsp.plan_id, qsp.query_id, ''physical reads''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     physical_read_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_physical_io_reads DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*Get highest logical write plans*/

RAISERROR(N'Gathering highest write plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH logical_writes_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_logical_io_writes_mb DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
		 qsp.plan_id, qsp.query_id, ''writes''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     logical_writes_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_logical_io_writes DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*Get highest memory use plans*/

RAISERROR(N'Gathering highest memory use plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH memory_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_query_max_used_memory_mb DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
		 qsp.plan_id, qsp.query_id, ''memory''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     memory_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_query_max_used_memory DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*Get highest row count plans*/

RAISERROR(N'Gathering highest row count plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH rowcount_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_rowcount DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
		 qsp.plan_id, qsp.query_id, ''rows''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     rowcount_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_rowcount DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;


IF @new_columns = 1
BEGIN

RAISERROR(N'Gathering new 2017 new column info...', 0, 1) WITH NOWAIT;

/*Get highest log byte count plans*/

RAISERROR(N'Gathering highest log byte use plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH rowcount_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_log_bytes_mb DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
		 qsp.plan_id, qsp.query_id, ''log bytes''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     rowcount_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_log_bytes_used DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;

/*Get highest row count plans*/

RAISERROR(N'Gathering highest row count plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH rowcount_max
AS ( SELECT   TOP 1 
              gi.start_range,
              gi.end_range
     FROM     #grouped_interval AS gi
     ORDER BY gi.total_avg_tempdb_space DESC )
INSERT #working_plans WITH (TABLOCK) 
		( plan_id, query_id, pattern )
SELECT   TOP ( @sp_Top ) 
		 qsp.plan_id, qsp.query_id, ''tempdb space''
FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN     rowcount_max AS dm
ON qsp.last_execution_time >= dm.start_range
   AND qsp.last_execution_time < dm.end_range
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'ORDER BY qsrs.avg_tempdb_space_used DESC
					OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;

END;


/*
This rolls up the different patterns we find before deduplicating.

The point of this is so we know if a query was gathered by one or more of the search queries

*/

RAISERROR(N'Updating patterns', 0, 1) WITH NOWAIT;

WITH patterns AS (
SELECT wp.plan_id, wp.query_id,
	   pattern_path = STUFF((SELECT DISTINCT N', ' + wp2.pattern
									FROM #working_plans AS wp2
									WHERE wp.plan_id = wp2.plan_id
									AND wp.query_id = wp2.query_id
									FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'')									
FROM #working_plans AS wp
)
UPDATE wp
SET wp.pattern = patterns.pattern_path
FROM #working_plans AS wp
JOIN patterns
ON  wp.plan_id = patterns.plan_id
AND wp.query_id = patterns.query_id
OPTION (RECOMPILE);


/*
This dedupes our results so we hopefully don't double-work the same plan
*/

RAISERROR(N'Deduplicating gathered plans', 0, 1) WITH NOWAIT;

WITH dedupe AS (
SELECT * , ROW_NUMBER() OVER (PARTITION BY wp.plan_id ORDER BY wp.plan_id) AS dupes
FROM #working_plans AS wp
)
DELETE dedupe
WHERE dedupe.dupes > 1
OPTION (RECOMPILE);

SET @msg = N'Removed ' + CONVERT(NVARCHAR(10), @@ROWCOUNT) + N' duplicate plan_ids.';
RAISERROR(@msg, 0, 1) WITH NOWAIT;


/*
This gathers data for the #working_metrics table
*/


RAISERROR(N'Collecting worker metrics', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
SELECT ' + QUOTENAME(@DatabaseName, '''') + N' AS database_name, wp.plan_id, wp.query_id,
       QUOTENAME(object_schema_name(qsq.object_id, DB_ID(' + QUOTENAME(@DatabaseName, '''') + N'))) + ''.'' +
	   QUOTENAME(object_name(qsq.object_id, DB_ID(' + QUOTENAME(@DatabaseName, '''') + N'))) AS proc_or_function_name,
	   qsq.batch_sql_handle, qsq.query_hash, qsq.query_parameterization_type_desc, qsq.count_compiles, 
	   (qsq.avg_compile_duration / 1000.), 
	   (qsq.last_compile_duration / 1000.), 
	   (qsq.avg_bind_duration / 1000.), 
	   (qsq.last_bind_duration / 1000.), 
	   (qsq.avg_bind_cpu_time / 1000.), 
	   (qsq.last_bind_cpu_time / 1000.), 
	   (qsq.avg_optimize_duration / 1000.), 
	   (qsq.last_optimize_duration / 1000.), 
	   (qsq.avg_optimize_cpu_time / 1000.), 
	   (qsq.last_optimize_cpu_time / 1000.), 
	   (qsq.avg_compile_memory_kb / 1024.), 
	   (qsq.last_compile_memory_kb / 1024.), 
	   qsrs.execution_type_desc, qsrs.first_execution_time, qsrs.last_execution_time, qsrs.count_executions, 
	   (qsrs.avg_duration / 1000.), 
	   (qsrs.last_duration / 1000.),
	   (qsrs.min_duration / 1000.), 
	   (qsrs.max_duration / 1000.), 
	   (qsrs.avg_cpu_time / 1000.), 
	   (qsrs.last_cpu_time / 1000.), 
	   (qsrs.min_cpu_time / 1000.), 
	   (qsrs.max_cpu_time / 1000.), 
	   ((qsrs.avg_logical_io_reads * 8 ) / 1024.), 
	   ((qsrs.last_logical_io_reads * 8 ) / 1024.), 
	   ((qsrs.min_logical_io_reads * 8 ) / 1024.), 
	   ((qsrs.max_logical_io_reads * 8 ) / 1024.), 
	   ((qsrs.avg_logical_io_writes * 8 ) / 1024.), 
	   ((qsrs.last_logical_io_writes * 8 ) / 1024.), 
	   ((qsrs.min_logical_io_writes * 8 ) / 1024.), 
	   ((qsrs.max_logical_io_writes * 8 ) / 1024.), 
	   ((qsrs.avg_physical_io_reads * 8 ) / 1024.), 
	   ((qsrs.last_physical_io_reads * 8 ) / 1024.), 
	   ((qsrs.min_physical_io_reads * 8 ) / 1024.), 
	   ((qsrs.max_physical_io_reads * 8 ) / 1024.), 
	   (qsrs.avg_clr_time / 1000.), 
	   (qsrs.last_clr_time / 1000.), 
	   (qsrs.min_clr_time / 1000.), 
	   (qsrs.max_clr_time / 1000.), 
	   qsrs.avg_dop, qsrs.last_dop, qsrs.min_dop, qsrs.max_dop, 
	   ((qsrs.avg_query_max_used_memory * 8 ) / 1024.), 
	   ((qsrs.last_query_max_used_memory * 8 ) / 1024.), 
	   ((qsrs.min_query_max_used_memory * 8 ) / 1024.), 
	   ((qsrs.max_query_max_used_memory * 8 ) / 1024.), 
	   qsrs.avg_rowcount, qsrs.last_rowcount, qsrs.min_rowcount, qsrs.max_rowcount,';
		
		IF @new_columns = 1
			BEGIN
			SET @sql_select += N'
			qsrs.avg_num_physical_io_reads, qsrs.last_num_physical_io_reads, qsrs.min_num_physical_io_reads, qsrs.max_num_physical_io_reads,
			(qsrs.avg_log_bytes_used / 100000000),
			(qsrs.last_log_bytes_used / 100000000),
			(qsrs.min_log_bytes_used / 100000000),
			(qsrs.max_log_bytes_used / 100000000),
			((qsrs.avg_tempdb_space_used * 8 ) / 1024.),
			((qsrs.last_tempdb_space_used * 8 ) / 1024.),
			((qsrs.min_tempdb_space_used * 8 ) / 1024.),
			((qsrs.max_tempdb_space_used * 8 ) / 1024.)
			';
			END;	
		IF @new_columns = 0
			BEGIN
			SET @sql_select += N'
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL
			';
			END;
SET @sql_select +=
N'FROM   #working_plans AS wp
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON wp.query_id = qsq.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = wp.plan_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
ON qsp.plan_id = wp.plan_id
AND qsp.query_id = wp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

INSERT #working_metrics WITH (TABLOCK)
		( database_name, plan_id, query_id, 
		  proc_or_function_name, 
		  batch_sql_handle, query_hash, query_parameterization_type_desc, count_compiles, 
		  avg_compile_duration, last_compile_duration, avg_bind_duration, last_bind_duration, avg_bind_cpu_time, last_bind_cpu_time, avg_optimize_duration, 
		  last_optimize_duration, avg_optimize_cpu_time, last_optimize_cpu_time, avg_compile_memory_kb, last_compile_memory_kb, execution_type_desc, 
		  first_execution_time, last_execution_time, count_executions, avg_duration, last_duration, min_duration, max_duration, avg_cpu_time, last_cpu_time, 
		  min_cpu_time, max_cpu_time, avg_logical_io_reads, last_logical_io_reads, min_logical_io_reads, max_logical_io_reads, avg_logical_io_writes, 
		  last_logical_io_writes, min_logical_io_writes, max_logical_io_writes, avg_physical_io_reads, last_physical_io_reads, min_physical_io_reads, 
		  max_physical_io_reads, avg_clr_time, last_clr_time, min_clr_time, max_clr_time, avg_dop, last_dop, min_dop, max_dop, avg_query_max_used_memory, 
		  last_query_max_used_memory, min_query_max_used_memory, max_query_max_used_memory, avg_rowcount, last_rowcount, min_rowcount, max_rowcount,
		  /* 2017 only columns */
		  avg_num_physical_io_reads, last_num_physical_io_reads, min_num_physical_io_reads, max_num_physical_io_reads,
		  avg_log_bytes_used, last_log_bytes_used, min_log_bytes_used, max_log_bytes_used,
		  avg_tempdb_space_used, last_tempdb_space_used, min_tempdb_space_used, max_tempdb_space_used )

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*This just helps us classify our queries*/
UPDATE #working_metrics
SET proc_or_function_name = N'Statement'
WHERE proc_or_function_name IS NULL
OPTION(RECOMPILE);


/*
This gathers data for the #working_plan_text table
*/


RAISERROR(N'Gathering working plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
SELECT ' + QUOTENAME(@DatabaseName, '''') + N' AS database_name,  wp.plan_id, wp.query_id,
	   qsp.plan_group_id, qsp.engine_version, qsp.compatibility_level, qsp.query_plan_hash, TRY_CONVERT(XML, qsp.query_plan), qsp.is_online_index_plan, qsp.is_trivial_plan, 
	   qsp.is_parallel_plan, qsp.is_forced_plan, qsp.is_natively_compiled, qsp.force_failure_count, qsp.last_force_failure_reason_desc, qsp.count_compiles, 
	   qsp.initial_compile_start_time, qsp.last_compile_start_time, qsp.last_execution_time, 
	   (qsp.avg_compile_duration / 1000.), 
	   (qsp.last_compile_duration / 1000.), 
	   qsqt.query_sql_text, qsqt.statement_sql_handle, qsqt.is_part_of_encrypted_module, qsqt.has_restricted_text
FROM   #working_plans AS wp
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
ON qsp.plan_id = wp.plan_id
   AND qsp.query_id = wp.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON wp.query_id = qsq.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = wp.plan_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

INSERT #working_plan_text WITH (TABLOCK)
		( database_name, plan_id, query_id, 
		  plan_group_id, engine_version, compatibility_level, query_plan_hash, query_plan_xml, is_online_index_plan, is_trivial_plan, 
		  is_parallel_plan, is_forced_plan, is_natively_compiled, force_failure_count, last_force_failure_reason_desc, count_compiles, 
		  initial_compile_start_time, last_compile_start_time, last_execution_time, avg_compile_duration, last_compile_duration, 
		  query_sql_text, statement_sql_handle, is_part_of_encrypted_module, has_restricted_text )

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;



/*

Some memory grant information isn't available in query store

We have to go back to other DMVs to find it, when possible

It may not be there for various reaons

*/
RAISERROR(N'Checking dm_exec_query_stats for memory grant info', 0, 1) WITH NOWAIT;
WITH max_mem
AS ( SELECT   deqs.sql_handle, MAX(deqs.min_grant_kb) AS min_grant_kb, MAX(deqs.max_used_grant_kb) AS max_used_grant_kb
     FROM     sys.dm_exec_query_stats AS deqs
     GROUP BY deqs.sql_handle )
UPDATE wpt
SET    wpt.min_grant_kb = deqs.min_grant_kb,
       wpt.max_used_grant_kb = deqs.max_used_grant_kb
FROM   #working_plan_text AS wpt
JOIN   max_mem AS deqs
ON wpt.statement_sql_handle = deqs.sql_handle
OPTION (RECOMPILE);


/*
This gets us context settings for our queries and adds it to the #working_plan_text table
*/

RAISERROR(N'Gathering context settings', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
UPDATE wp
SET wp.context_settings = SUBSTRING(
					    CASE WHEN (CAST(qcs.set_options AS INT) & 1 = 1) THEN '', ANSI_PADDING'' ELSE '''' END +
					    CASE WHEN (CAST(qcs.set_options AS INT) & 8 = 8) THEN '', CONCAT_NULL_YIELDS_NULL'' ELSE '''' END +
					    CASE WHEN (CAST(qcs.set_options AS INT) & 16 = 16) THEN '', ANSI_WARNINGS'' ELSE '''' END +
					    CASE WHEN (CAST(qcs.set_options AS INT) & 32 = 32) THEN '', ANSI_NULLS'' ELSE '''' END +
					    CASE WHEN (CAST(qcs.set_options AS INT) & 64 = 64) THEN '', QUOTED_IDENTIFIER'' ELSE '''' END +
					    CASE WHEN (CAST(qcs.set_options AS INT) & 4096 = 4096) THEN '', ARITH_ABORT'' ELSE '''' END +
					    CASE WHEN (CAST(qcs.set_options AS INT) & 8192 = 8192) THEN '', NUMERIC_ROUNDABORT'' ELSE '''' END 
					    , 2, 200000)
FROM #working_plan_text wp
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON wp.query_id = qsq.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_context_settings AS qcs
ON qcs.context_settings_id = qsq.context_settings_id
OPTION (RECOMPILE);
';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select;


/*This adds the patterns we found from each interval to the #working_plan_text table*/

RAISERROR(N'Add patterns to working plans', 0, 1) WITH NOWAIT;

UPDATE wpt
SET wpt.pattern = wp.pattern
FROM #working_plans AS wp
JOIN #working_plan_text AS wpt
ON wpt.plan_id = wp.plan_id
AND wpt.query_id = wp.query_id
OPTION (RECOMPILE);

/*This cleans up query text a bit*/

RAISERROR(N'Clean awkward characters from query text', 0, 1) WITH NOWAIT;

UPDATE b
SET b.query_sql_text = REPLACE(REPLACE(REPLACE(b.query_sql_text, @cr, ' '), @lf, ' '), @tab, '  ')
FROM #working_plan_text AS b
OPTION (RECOMPILE);


/*This populates #working_wait_stats when available*/

IF @waitstats = 1

	BEGIN
	
	RAISERROR(N'Collecting wait stats info', 0, 1) WITH NOWAIT;
	
	
		SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
		SET @sql_select += N'
		SELECT   qws.plan_id,
		         qws.wait_category,
		         qws.wait_category_desc,
		         SUM(qws.total_query_wait_time_ms) AS total_query_wait_time_ms,
		         SUM(qws.avg_query_wait_time_ms) AS avg_query_wait_time_ms,
		         SUM(qws.last_query_wait_time_ms) AS last_query_wait_time_ms,
		         SUM(qws.min_query_wait_time_ms) AS min_query_wait_time_ms,
		         SUM(qws.max_query_wait_time_ms) AS max_query_wait_time_ms
		FROM     ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_wait_stats qws
		JOIN #working_plans AS wp
		ON qws.plan_id = wp.plan_id
		GROUP BY qws.plan_id, qws.wait_category, qws.wait_category_desc
		HAVING SUM(qws.min_query_wait_time_ms) >= 5
		OPTION (RECOMPILE);
		';
		
		IF @Debug = 1
			PRINT @sql_select;
		
		IF @sql_select IS NULL
		    BEGIN
		        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
		        RETURN;
		    END;
		
		INSERT #working_wait_stats WITH (TABLOCK)
				( plan_id, wait_category, wait_category_desc, total_query_wait_time_ms, avg_query_wait_time_ms, last_query_wait_time_ms, min_query_wait_time_ms, max_query_wait_time_ms )
		
		EXEC sys.sp_executesql  @stmt = @sql_select;
	
	
	/*This updates #working_plan_text with the top three waits from the wait stats DMV*/
	
	RAISERROR(N'Update working_plan_text with top three waits', 0, 1) WITH NOWAIT;
	
	
		UPDATE wpt
		SET wpt.top_three_waits = x.top_three_waits 
		FROM #working_plan_text AS wpt
		JOIN (
			SELECT wws.plan_id,
				   top_three_waits = STUFF((SELECT TOP 3 N', ' + wws2.wait_category_desc + N' (' + CONVERT(NVARCHAR(20), SUM(CONVERT(BIGINT, wws2.avg_query_wait_time_ms))) + N' ms) '
												FROM #working_wait_stats AS wws2
												WHERE wws.plan_id = wws2.plan_id
												GROUP BY wws2.wait_category_desc
												ORDER BY SUM(wws2.avg_query_wait_time_ms) DESC
												FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'')							
			FROM #working_wait_stats AS wws
			GROUP BY wws.plan_id
		) AS x 
		ON x.plan_id = wpt.plan_id
		OPTION (RECOMPILE);

END;

/*End wait stats population*/

UPDATE #working_plan_text
SET top_three_waits = CASE 
						WHEN @waitstats = 0 THEN N'The query store waits stats DMV is not available'
						ELSE N'No Significant waits detected!'
						END
WHERE top_three_waits IS NULL
OPTION(RECOMPILE);

END;
END TRY
BEGIN CATCH
        RAISERROR (N'Failure populating temp tables.', 0,1) WITH NOWAIT;

        IF @sql_select IS NOT NULL
        BEGIN
            SET @msg = N'Last @sql_select: ' + @sql_select;
            RAISERROR(@msg, 0, 1) WITH NOWAIT;
        END;

        SELECT    @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
        RAISERROR (@msg, @error_severity, @error_state) WITH NOWAIT;
        
        
        WHILE @@TRANCOUNT > 0 
            ROLLBACK;

        RETURN;
END CATCH;

IF (@SkipXML = 0)
BEGIN TRY 
BEGIN

/*
This sets up the #working_warnings table with the IDs we're interested in so we can tie warnings back to them 
*/

RAISERROR(N'Populate working warnings table with gathered plans', 0, 1) WITH NOWAIT;


SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
SELECT wp.plan_id, wp.query_id, qsq.query_hash, qsqt.statement_sql_handle
FROM   #working_plans AS wp
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
ON qsp.plan_id = wp.plan_id
   AND qsp.query_id = wp.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON wp.query_id = qsq.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = wp.plan_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select +=  N'OPTION (RECOMPILE);
					';

IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

INSERT #working_warnings  WITH (TABLOCK)
	( plan_id, query_id, query_hash, sql_handle )
EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;

/*
This looks for queries in the query stores that we picked up from an internal that have multiple plans in cache

This and several of the following queries all replaced XML parsing to find plan attributes. Sweet.

Thanks, Query Store
*/

RAISERROR(N'Populating object name in #working_warnings', 0, 1) WITH NOWAIT;
UPDATE w
SET    w.proc_or_function_name = ISNULL(wm.proc_or_function_name, N'Statement')
FROM   #working_warnings AS w
JOIN   #working_metrics AS wm
ON w.plan_id = wm.plan_id
   AND w.query_id = wm.query_id
OPTION (RECOMPILE);


RAISERROR(N'Checking for multiple plans', 0, 1) WITH NOWAIT;

SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
UPDATE ww
SET ww.plan_multiple_plans = 1
FROM #working_warnings AS ww
JOIN 
(
SELECT wp.query_id, COUNT(qsp.plan_id) AS  plans
FROM   #working_plans AS wp
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
ON qsp.plan_id = wp.plan_id
   AND qsp.query_id = wp.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON wp.query_id = qsq.query_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
JOIN   ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = wp.plan_id
WHERE    1 = 1
    AND qsq.is_internal_query = 0
	AND qsp.query_plan IS NOT NULL
	';

SET @sql_select += @sql_where;

SET @sql_select += N'GROUP BY wp.query_id
					HAVING COUNT(qsp.plan_id) > 1
					) AS x
					ON ww.query_id = x.query_id
					OPTION (RECOMPILE);
					';
IF @Debug = 1
	PRINT @sql_select;

IF @sql_select IS NULL
    BEGIN
        RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
        RETURN;
    END;

EXEC sys.sp_executesql  @stmt = @sql_select, 
						@params = @sp_params,
						@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;

/*
This looks for forced plans
*/

RAISERROR(N'Checking for forced plans', 0, 1) WITH NOWAIT;

UPDATE ww
SET    ww.is_forced_plan = 1
FROM   #working_warnings AS ww
JOIN   #working_plan_text AS wp
ON ww.plan_id = wp.plan_id
   AND ww.query_id = wp.query_id
   AND wp.is_forced_plan = 1
OPTION (RECOMPILE);


/*
This looks for forced parameterization
*/

RAISERROR(N'Checking for forced parameterization', 0, 1) WITH NOWAIT;

UPDATE ww
SET    ww.is_forced_parameterized = 1
FROM   #working_warnings AS ww
JOIN   #working_metrics AS wm
ON ww.plan_id = wm.plan_id
   AND ww.query_id = wm.query_id
   AND wm.query_parameterization_type_desc = 'Forced'
OPTION (RECOMPILE);


/*
This looks for unparameterized queries
*/

RAISERROR(N'Checking for unparameterized plans', 0, 1) WITH NOWAIT;

UPDATE ww
SET    ww.unparameterized_query = 1
FROM   #working_warnings AS ww
JOIN   #working_metrics AS wm
ON ww.plan_id = wm.plan_id
   AND ww.query_id = wm.query_id
   AND wm.query_parameterization_type_desc = 'None'
   AND ww.proc_or_function_name = 'Statement'
OPTION (RECOMPILE);


/*
This looks for cursors
*/

RAISERROR(N'Checking for cursors', 0, 1) WITH NOWAIT;
UPDATE ww
SET    ww.is_cursor = 1
FROM   #working_warnings AS ww
JOIN   #working_plan_text AS wp
ON ww.plan_id = wp.plan_id
   AND ww.query_id = wp.query_id
   AND wp.plan_group_id > 0
OPTION (RECOMPILE);


UPDATE ww
SET    ww.is_cursor = 1
FROM   #working_warnings AS ww
JOIN   #working_plan_text AS wp
ON ww.plan_id = wp.plan_id
   AND ww.query_id = wp.query_id
WHERE ww.query_hash = 0x0000000000000000
OR wp.query_plan_hash = 0x0000000000000000
OPTION (RECOMPILE);

/*
This looks for parallel plans
*/
UPDATE ww
SET    ww.is_parallel = 1
FROM   #working_warnings AS ww
JOIN   #working_plan_text AS wp
ON ww.plan_id = wp.plan_id
   AND ww.query_id = wp.query_id
   AND wp.is_parallel_plan = 1
OPTION (RECOMPILE);

/*This looks for old CE*/

RAISERROR(N'Checking for legacy CE', 0, 1) WITH NOWAIT;

UPDATE w
SET w.downlevel_estimator = 1
FROM #working_warnings AS w
JOIN #working_plan_text AS wpt
ON w.plan_id = wpt.plan_id
AND w.query_id = wpt.query_id
/*PLEASE DON'T TELL ANYONE I DID THIS*/
WHERE PARSENAME(wpt.engine_version, 4) < PARSENAME(CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4)
OPTION (RECOMPILE);
/*NO SERIOUSLY THIS IS A HORRIBLE IDEA*/


/*Plans that compile 2x more than they execute*/

RAISERROR(N'Checking for plans that compile 2x more than they execute', 0, 1) WITH NOWAIT;

UPDATE ww
SET    ww.is_compile_more = 1
FROM   #working_warnings AS ww
JOIN   #working_metrics AS wm
ON ww.plan_id = wm.plan_id
   AND ww.query_id = wm.query_id
   AND wm.count_compiles > (wm.count_executions * 2)
OPTION (RECOMPILE);

/*Plans that compile 2x more than they execute*/

RAISERROR(N'Checking for plans that take more than 5 seconds to bind, compile, or optimize', 0, 1) WITH NOWAIT;

UPDATE ww
SET    ww.is_slow_plan = 1
FROM   #working_warnings AS ww
JOIN   #working_metrics AS wm
ON ww.plan_id = wm.plan_id
   AND ww.query_id = wm.query_id
   AND (wm.avg_bind_duration > 5000
		OR 
		wm.avg_compile_duration > 5000
		OR
		wm.avg_optimize_duration > 5000
		OR 
		wm.avg_optimize_cpu_time > 5000)
OPTION (RECOMPILE);



/*
This parses the XML from our top plans into smaller chunks for easier consumption
*/

RAISERROR(N'Begin XML nodes parsing', 0, 1) WITH NOWAIT;

RAISERROR(N'Inserting #statements', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
INSERT #statements WITH (TABLOCK) ( plan_id, query_id, query_hash, sql_handle, statement )	
	SELECT ww.plan_id, ww.query_id, ww.query_hash, ww.sql_handle, q.n.query('.') AS statement
	FROM #working_warnings AS ww
	JOIN #working_plan_text AS wp
	ON ww.plan_id = wp.plan_id
	AND ww.query_id = wp.query_id
    CROSS APPLY wp.query_plan_xml.nodes('//p:StmtSimple') AS q(n) 
OPTION (RECOMPILE);

RAISERROR(N'Inserting parsed cursor XML to #statements', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
INSERT #statements WITH (TABLOCK) ( plan_id, query_id, query_hash, sql_handle, statement )
	SELECT ww.plan_id, ww.query_id, ww.query_hash, ww.sql_handle, q.n.query('.') AS statement
	FROM #working_warnings AS ww
	JOIN #working_plan_text AS wp
	ON ww.plan_id = wp.plan_id
	AND ww.query_id = wp.query_id
    CROSS APPLY wp.query_plan_xml.nodes('//p:StmtCursor') AS q(n) 
OPTION (RECOMPILE);

RAISERROR(N'Inserting to #query_plan', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
INSERT #query_plan WITH (TABLOCK) ( plan_id, query_id, query_hash, sql_handle, query_plan )
SELECT  s.plan_id, s.query_id, s.query_hash, s.sql_handle, q.n.query('.') AS query_plan
FROM    #statements AS s
        CROSS APPLY s.statement.nodes('//p:QueryPlan') AS q(n) 
OPTION (RECOMPILE);

RAISERROR(N'Inserting to #relop', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
INSERT #relop WITH (TABLOCK) ( plan_id, query_id, query_hash, sql_handle, relop)
SELECT  qp.plan_id, qp.query_id, qp.query_hash, qp.sql_handle, q.n.query('.') AS relop
FROM    #query_plan qp
        CROSS APPLY qp.query_plan.nodes('//p:RelOp') AS q(n) 
OPTION (RECOMPILE);


-- statement level checks

RAISERROR(N'Performing compile timeout checks', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET     b.compile_timeout = 1 
FROM    #statements s
JOIN #working_warnings AS b
ON  s.query_hash = b.query_hash
WHERE s.statement.exist('/p:StmtSimple/@StatementOptmEarlyAbortReason[.="TimeOut"]') = 1
OPTION (RECOMPILE);


RAISERROR(N'Performing compile memory limit exceeded checks', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET     b.compile_memory_limit_exceeded = 1 
FROM    #statements s
JOIN #working_warnings AS b
ON  s.query_hash = b.query_hash
WHERE s.statement.exist('/p:StmtSimple/@StatementOptmEarlyAbortReason[.="MemoryLimitExceeded"]') = 1
OPTION (RECOMPILE);

IF @ExpertMode > 0
BEGIN
RAISERROR(N'Performing index DML checks', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
index_dml AS (
	SELECT	s.query_hash,	
			index_dml = CASE WHEN s.statement.exist('//p:StmtSimple/@StatementType[.="CREATE INDEX"]') = 1 THEN 1
							 WHEN s.statement.exist('//p:StmtSimple/@StatementType[.="DROP INDEX"]') = 1 THEN 1
							 END
	FROM    #statements s
			)
	UPDATE b
		SET b.index_dml = i.index_dml
	FROM #working_warnings AS b
	JOIN index_dml i
	ON i.query_hash = b.query_hash
	WHERE i.index_dml = 1
OPTION (RECOMPILE);


RAISERROR(N'Performing table DML checks', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
table_dml AS (
	SELECT s.query_hash,			
		   table_dml = CASE WHEN s.statement.exist('//p:StmtSimple/@StatementType[.="CREATE TABLE"]') = 1 THEN 1
							WHEN s.statement.exist('//p:StmtSimple/@StatementType[.="DROP OBJECT"]') = 1 THEN 1
							END
		 FROM #statements AS s
		 )
	UPDATE b
		SET b.table_dml = t.table_dml
	FROM #working_warnings AS b
	JOIN table_dml t
	ON t.query_hash = b.query_hash
	WHERE t.table_dml = 1
OPTION (RECOMPILE);
END;


RAISERROR(N'Gathering trivial plans', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
UPDATE b
SET b.is_trivial = 1
FROM #working_warnings AS b
JOIN (
SELECT  s.sql_handle
FROM    #statements AS s
JOIN    (   SELECT  r.sql_handle
            FROM    #relop AS r
            WHERE   r.relop.exist('//p:RelOp[contains(@LogicalOp, "Scan")]') = 1 ) AS r
    ON r.sql_handle = s.sql_handle
WHERE   s.statement.exist('//p:StmtSimple[@StatementOptmLevel[.="TRIVIAL"]]/p:QueryPlan/p:ParameterList') = 1
) AS s
ON b.sql_handle = s.sql_handle
OPTION (RECOMPILE);

IF @ExpertMode > 0
BEGIN
RAISERROR(N'Gathering row estimates', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
INSERT #est_rows (query_hash, estimated_rows)
SELECT DISTINCT 
		CONVERT(BINARY(8), RIGHT('0000000000000000' + SUBSTRING(c.n.value('@QueryHash', 'VARCHAR(18)'), 3, 18), 16), 2) AS query_hash,
		c.n.value('(/p:StmtSimple/@StatementEstRows)[1]', 'FLOAT') AS estimated_rows
FROM   #statements AS s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS c(n)
WHERE  c.n.exist('/p:StmtSimple[@StatementEstRows > 0]') = 1;

	UPDATE b
		SET b.estimated_rows = er.estimated_rows
	FROM #working_warnings AS b
	JOIN #est_rows er
	ON er.query_hash = b.query_hash
	OPTION (RECOMPILE);
END;


/*Begin plan cost calculations*/
RAISERROR(N'Gathering statement costs', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
INSERT #plan_cost WITH (TABLOCK)
	( query_plan_cost, sql_handle, plan_id )
SELECT  DISTINCT
		s.statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float') query_plan_cost,
		s.sql_handle,
		s.plan_id
FROM    #statements s
OUTER APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
WHERE s.statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float') > 0
OPTION (RECOMPILE);


RAISERROR(N'Updating statement costs', 0, 1) WITH NOWAIT;
WITH pc AS (
	SELECT SUM(DISTINCT pc.query_plan_cost) AS queryplancostsum, pc.sql_handle, pc.plan_id
	FROM #plan_cost AS pc
	GROUP BY pc.sql_handle, pc.plan_id
	)
	UPDATE b
		SET b.query_cost = ISNULL(pc.queryplancostsum, 0)
		FROM  #working_warnings AS b
		JOIN pc
		ON pc.sql_handle = b.sql_handle
		AND pc.plan_id = b.plan_id
OPTION (RECOMPILE);


/*End plan cost calculations*/


RAISERROR(N'Checking for plan warnings', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE  b
SET b.plan_warnings = 1
FROM    #query_plan qp
JOIN #working_warnings b
ON  qp.sql_handle = b.sql_handle
AND qp.query_plan.exist('/p:QueryPlan/p:Warnings') = 1
OPTION (RECOMPILE);


RAISERROR(N'Checking for implicit conversion', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE  b
SET b.implicit_conversions = 1
FROM    #query_plan qp
JOIN #working_warnings b
ON  qp.sql_handle = b.sql_handle
AND qp.query_plan.exist('/p:QueryPlan/p:Warnings/p:PlanAffectingConvert/@Expression[contains(., "CONVERT_IMPLICIT")]') = 1
OPTION (RECOMPILE);

IF @ExpertMode > 0
BEGIN
RAISERROR(N'Performing busy loops checks', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE p
SET    busy_loops = CASE WHEN (x.estimated_executions / 100.0) > x.estimated_rows THEN 1 END 
FROM   #working_warnings p
       JOIN (
            SELECT qs.sql_handle,
                   relop.value('sum(/p:RelOp/@EstimateRows)', 'float') AS estimated_rows ,
                   relop.value('sum(/p:RelOp/@EstimateRewinds)', 'float') + relop.value('sum(/p:RelOp/@EstimateRebinds)', 'float') + 1.0 AS estimated_executions 
            FROM   #relop qs
       ) AS x ON p.sql_handle = x.sql_handle
OPTION (RECOMPILE);
END; 


RAISERROR(N'Performing TVF join check', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE p
SET    p.tvf_join = CASE WHEN x.tvf_join = 1 THEN 1 END
FROM   #working_warnings p
       JOIN (
			SELECT r.sql_handle,
				   1 AS tvf_join
			FROM #relop AS r
			WHERE r.relop.exist('//p:RelOp[(@LogicalOp[.="Table-valued function"])]') = 1
			AND   r.relop.exist('//p:RelOp[contains(@LogicalOp, "Join")]') = 1
       ) AS x ON p.sql_handle = x.sql_handle
OPTION (RECOMPILE);

IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for operator warnings', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
, x AS (
SELECT r.sql_handle,
	   c.n.exist('//p:Warnings[(@NoJoinPredicate[.="1"])]') AS warning_no_join_predicate,
	   c.n.exist('//p:ColumnsWithNoStatistics') AS no_stats_warning ,
	   c.n.exist('//p:Warnings') AS relop_warnings
FROM #relop AS r
CROSS APPLY r.relop.nodes('/p:RelOp/p:Warnings') AS c(n)
)
UPDATE b
SET	   b.warning_no_join_predicate = x.warning_no_join_predicate,
	   b.no_stats_warning = x.no_stats_warning,
	   b.relop_warnings = x.relop_warnings
FROM #working_warnings b
JOIN x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);
END; 


RAISERROR(N'Checking for table variables', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
, x AS (
SELECT r.sql_handle,
	   c.n.value('substring(@Table, 2, 1)','VARCHAR(100)') AS first_char
FROM   #relop r
CROSS APPLY r.relop.nodes('//p:Object') AS c(n)
)
UPDATE b
SET	   b.is_table_variable = 1
FROM #working_warnings b
JOIN x ON x.sql_handle = b.sql_handle
JOIN #working_metrics AS wm
ON b.plan_id = wm.plan_id
AND b.query_id = wm.query_id
AND wm.batch_sql_handle IS NOT NULL
WHERE x.first_char = '@'
OPTION (RECOMPILE);


IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for functions', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
, x AS (
SELECT r.sql_handle,
	   n.fn.value('count(distinct-values(//p:UserDefinedFunction[not(@IsClrFunction)]))', 'INT') AS function_count,
	   n.fn.value('count(distinct-values(//p:UserDefinedFunction[@IsClrFunction = "1"]))', 'INT') AS clr_function_count
FROM   #relop r
CROSS APPLY r.relop.nodes('/p:RelOp/p:ComputeScalar/p:DefinedValues/p:DefinedValue/p:ScalarOperator') n(fn)
)
UPDATE b
SET	   b.function_count = x.function_count,
	   b.clr_function_count = x.clr_function_count
FROM #working_warnings b
JOIN x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);
END;


RAISERROR(N'Checking for expensive key lookups', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.key_lookup_cost = x.key_lookup_cost
FROM #working_warnings b
JOIN (
SELECT 
       r.sql_handle,
	   MAX(r.relop.value('sum(/p:RelOp/@EstimatedTotalSubtreeCost)', 'float')) AS key_lookup_cost
FROM   #relop r
WHERE r.relop.exist('/p:RelOp/p:IndexScan[(@Lookup[.="1"])]') = 1
GROUP BY r.sql_handle
) AS x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);


RAISERROR(N'Checking for expensive remote queries', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.remote_query_cost = x.remote_query_cost
FROM #working_warnings b
JOIN (
SELECT 
       r.sql_handle,
	   MAX(r.relop.value('sum(/p:RelOp/@EstimatedTotalSubtreeCost)', 'float')) AS remote_query_cost
FROM   #relop r
WHERE r.relop.exist('/p:RelOp[(@PhysicalOp[contains(., "Remote")])]') = 1
GROUP BY r.sql_handle
) AS x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);


RAISERROR(N'Checking for expensive sorts', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET sort_cost = y.max_sort_cost 
FROM #working_warnings b
JOIN (
	SELECT x.sql_handle, MAX((x.sort_io + x.sort_cpu)) AS max_sort_cost
	FROM (
		SELECT 
		       qs.sql_handle,
			   relop.value('sum(/p:RelOp/@EstimateIO)', 'float') AS sort_io,
			   relop.value('sum(/p:RelOp/@EstimateCPU)', 'float') AS sort_cpu
		FROM   #relop qs
		WHERE [relop].exist('/p:RelOp[(@PhysicalOp[.="Sort"])]') = 1
		) AS x
	GROUP BY x.sql_handle
	) AS y
ON  b.sql_handle = y.sql_handle
OPTION (RECOMPILE);


RAISERROR(N'Checking for Optimistic cursors', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_optimistic_cursor =  1
FROM #working_warnings b
JOIN #statements AS s
ON b.sql_handle = s.sql_handle
CROSS APPLY s.statement.nodes('/p:StmtCursor') AS n1(fn)
WHERE n1.fn.exist('//p:CursorPlan/@CursorConcurrency[.="Optimistic"]') = 1
OPTION (RECOMPILE);


RAISERROR(N'Checking if cursor is Forward Only', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_forward_only_cursor = 1
FROM #working_warnings b
JOIN #statements AS s
ON b.sql_handle = s.sql_handle
CROSS APPLY s.statement.nodes('/p:StmtCursor') AS n1(fn)
WHERE n1.fn.exist('//p:CursorPlan/@ForwardOnly[.="true"]') = 1
OPTION (RECOMPILE);


RAISERROR(N'Checking if cursor is Fast Forward', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_fast_forward_cursor = 1
FROM #working_warnings b
JOIN #statements AS qs
ON b.sql_handle = qs.sql_handle
CROSS APPLY qs.statement.nodes('/p:StmtCursor') AS n1(fn)
WHERE n1.fn.exist('//p:CursorPlan/@CursorActualType[.="FastForward"]') = 1
OPTION (RECOMPILE);


RAISERROR(N'Checking for Dynamic cursors', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_cursor_dynamic =  1
FROM #working_warnings b
JOIN #statements AS s
ON b.sql_handle = s.sql_handle
CROSS APPLY s.statement.nodes('/p:StmtCursor') AS n1(fn)
WHERE n1.fn.exist('//p:CursorPlan/@CursorActualType[.="Dynamic"]') = 1
OPTION (RECOMPILE);


IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for bad scans and plan forcing', 0, 1) WITH NOWAIT;
;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET 
b.is_table_scan = x.is_table_scan,
b.backwards_scan = x.backwards_scan,
b.forced_index = x.forced_index,
b.forced_seek = x.forced_seek,
b.forced_scan = x.forced_scan
FROM #working_warnings b
JOIN (
SELECT 
       r.sql_handle,
	   0 AS is_table_scan,
	   q.n.exist('@ScanDirection[.="BACKWARD"]') AS backwards_scan,
	   q.n.value('@ForcedIndex', 'bit') AS forced_index,
	   q.n.value('@ForceSeek', 'bit') AS forced_seek,
	   q.n.value('@ForceScan', 'bit') AS forced_scan
FROM   #relop r
CROSS APPLY r.relop.nodes('//p:IndexScan') AS q(n)
UNION ALL
SELECT 
       r.sql_handle,
	   1 AS is_table_scan,
	   q.n.exist('@ScanDirection[.="BACKWARD"]') AS backwards_scan,
	   q.n.value('@ForcedIndex', 'bit') AS forced_index,
	   q.n.value('@ForceSeek', 'bit') AS forced_seek,
	   q.n.value('@ForceScan', 'bit') AS forced_scan
FROM   #relop r
CROSS APPLY r.relop.nodes('//p:TableScan') AS q(n)
) AS x ON b.sql_handle = x.sql_handle
OPTION (RECOMPILE);
END;


IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for computed columns that reference scalar UDFs', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_computed_scalar = x.computed_column_function
FROM #working_warnings b
JOIN (
SELECT r.sql_handle,
	   n.fn.value('count(distinct-values(//p:UserDefinedFunction[not(@IsClrFunction)]))', 'INT') AS computed_column_function
FROM   #relop r
CROSS APPLY r.relop.nodes('/p:RelOp/p:ComputeScalar/p:DefinedValues/p:DefinedValue/p:ScalarOperator') n(fn)
WHERE n.fn.exist('/p:RelOp/p:ComputeScalar/p:DefinedValues/p:DefinedValue/p:ColumnReference[(@ComputedColumn[.="1"])]') = 1
) AS x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);
END;


RAISERROR(N'Checking for filters that reference scalar UDFs', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_computed_filter = x.filter_function
FROM #working_warnings b
JOIN (
SELECT 
r.sql_handle, 
c.n.value('count(distinct-values(//p:UserDefinedFunction[not(@IsClrFunction)]))', 'INT') AS filter_function
FROM #relop AS r
CROSS APPLY r.relop.nodes('/p:RelOp/p:Filter/p:Predicate/p:ScalarOperator/p:Compare/p:ScalarOperator/p:UserDefinedFunction') c(n) 
) x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);


IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking modification queries that hit lots of indexes', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),	
IndexOps AS 
(
	SELECT 
	r.query_hash,
	c.n.value('@PhysicalOp', 'VARCHAR(100)') AS op_name,
	c.n.exist('@PhysicalOp[.="Index Insert"]') AS ii,
	c.n.exist('@PhysicalOp[.="Index Update"]') AS iu,
	c.n.exist('@PhysicalOp[.="Index Delete"]') AS id,
	c.n.exist('@PhysicalOp[.="Clustered Index Insert"]') AS cii,
	c.n.exist('@PhysicalOp[.="Clustered Index Update"]') AS ciu,
	c.n.exist('@PhysicalOp[.="Clustered Index Delete"]') AS cid,
	c.n.exist('@PhysicalOp[.="Table Insert"]') AS ti,
	c.n.exist('@PhysicalOp[.="Table Update"]') AS tu,
	c.n.exist('@PhysicalOp[.="Table Delete"]') AS td
	FROM #relop AS r
	CROSS APPLY r.relop.nodes('/p:RelOp') c(n)
	OUTER APPLY r.relop.nodes('/p:RelOp/p:ScalarInsert/p:Object') q(n)
	OUTER APPLY r.relop.nodes('/p:RelOp/p:Update/p:Object') o2(n)
	OUTER APPLY r.relop.nodes('/p:RelOp/p:SimpleUpdate/p:Object') o3(n)
), iops AS 
(
		SELECT	ios.query_hash,
		SUM(CONVERT(TINYINT, ios.ii)) AS index_insert_count,
		SUM(CONVERT(TINYINT, ios.iu)) AS index_update_count,
		SUM(CONVERT(TINYINT, ios.id)) AS index_delete_count,
		SUM(CONVERT(TINYINT, ios.cii)) AS cx_insert_count,
		SUM(CONVERT(TINYINT, ios.ciu)) AS cx_update_count,
		SUM(CONVERT(TINYINT, ios.cid)) AS cx_delete_count,
		SUM(CONVERT(TINYINT, ios.ti)) AS table_insert_count,
		SUM(CONVERT(TINYINT, ios.tu)) AS table_update_count,
		SUM(CONVERT(TINYINT, ios.td)) AS table_delete_count
		FROM IndexOps AS ios
		WHERE ios.op_name IN ('Index Insert', 'Index Delete', 'Index Update', 
							  'Clustered Index Insert', 'Clustered Index Delete', 'Clustered Index Update', 
							  'Table Insert', 'Table Delete', 'Table Update')
		GROUP BY ios.query_hash) 
UPDATE b
SET b.index_insert_count = iops.index_insert_count,
	b.index_update_count = iops.index_update_count,
	b.index_delete_count = iops.index_delete_count,
	b.cx_insert_count = iops.cx_insert_count,
	b.cx_update_count = iops.cx_update_count,
	b.cx_delete_count = iops.cx_delete_count,
	b.table_insert_count = iops.table_insert_count,
	b.table_update_count = iops.table_update_count,
	b.table_delete_count = iops.table_delete_count
FROM #working_warnings AS b
JOIN iops ON  iops.query_hash = b.query_hash
OPTION (RECOMPILE);
END;


IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for Spatial index use', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_spatial = x.is_spatial
FROM #working_warnings AS b
JOIN (
SELECT r.sql_handle,
	   1 AS is_spatial
FROM   #relop r
CROSS APPLY r.relop.nodes('/p:RelOp//p:Object') n(fn)
WHERE n.fn.exist('(@IndexKind[.="Spatial"])') = 1
) AS x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);
END;

RAISERROR(N'Checking for forced serialization', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE  b
SET b.is_forced_serial = 1
FROM #query_plan qp
JOIN #working_warnings AS b
ON    qp.sql_handle = b.sql_handle
AND b.is_parallel IS NULL
AND qp.query_plan.exist('/p:QueryPlan/@NonParallelPlanReason') = 1
OPTION (RECOMPILE);


IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for ColumnStore queries operating in Row Mode instead of Batch Mode', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.columnstore_row_mode = x.is_row_mode
FROM #working_warnings AS b
JOIN (
SELECT 
       r.sql_handle,
	   r.relop.exist('/p:RelOp[(@EstimatedExecutionMode[.="Row"])]') AS is_row_mode
FROM   #relop r
WHERE r.relop.exist('/p:RelOp/p:IndexScan[(@Storage[.="ColumnStore"])]') = 1
) AS x ON x.sql_handle = b.sql_handle
OPTION (RECOMPILE);
END;


IF @ExpertMode > 0
BEGIN
RAISERROR('Checking for row level security only', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE  b
SET  b.is_row_level = 1
FROM #working_warnings b
JOIN #statements s 
ON s.query_hash = b.query_hash 
WHERE s.statement.exist('/p:StmtSimple/@SecurityPolicyApplied[.="true"]') = 1
OPTION (RECOMPILE);
END;


IF @ExpertMode > 0
BEGIN
RAISERROR('Checking for wonky Index Spools', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES (
    'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
, selects
AS ( SELECT s.plan_id, s.query_id
     FROM   #statements AS s
     WHERE  s.statement.exist('/p:StmtSimple/@StatementType[.="SELECT"]') = 1 )
, spools
AS ( SELECT DISTINCT r.plan_id,
       r.query_id,
	   c.n.value('@EstimateRows', 'FLOAT') AS estimated_rows,
       c.n.value('@EstimateIO', 'FLOAT') AS estimated_io,
       c.n.value('@EstimateCPU', 'FLOAT') AS estimated_cpu,
       c.n.value('@EstimateRewinds', 'FLOAT') AS estimated_rewinds
FROM   #relop AS r
JOIN   selects AS s
ON s.plan_id = r.plan_id
   AND s.query_id = r.query_id
CROSS APPLY r.relop.nodes('/p:RelOp') AS c(n)
WHERE  r.relop.exist('/p:RelOp[@PhysicalOp="Index Spool" and @LogicalOp="Eager Spool"]') = 1
)
UPDATE ww
		SET ww.index_spool_rows = sp.estimated_rows,
			ww.index_spool_cost = ((sp.estimated_io * sp.estimated_cpu) * CASE WHEN sp.estimated_rewinds < 1 THEN 1 ELSE sp.estimated_rewinds END)

FROM #working_warnings ww
JOIN spools sp
ON ww.plan_id = sp.plan_id
AND ww.query_id = sp.query_id
OPTION (RECOMPILE);
END;


IF (PARSENAME(CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4)) >= 14
OR ((PARSENAME(CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4)) = 13
	AND PARSENAME(CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 2) >= 5026)

BEGIN

RAISERROR(N'Beginning 2017 and 2016 SP2 specfic checks', 0, 1) WITH NOWAIT;


IF @ExpertMode > 0
BEGIN
RAISERROR('Gathering stats information', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
INSERT #stats_agg WITH (TABLOCK)
	(sql_handle, last_update, modification_count, sampling_percent, [statistics], [table], [schema], [database])
SELECT qp.sql_handle,
	   x.c.value('@LastUpdate', 'DATETIME2(7)') AS LastUpdate,
	   x.c.value('@ModificationCount', 'INT') AS ModificationCount,
	   x.c.value('@SamplingPercent', 'FLOAT') AS SamplingPercent,
	   x.c.value('@Statistics', 'NVARCHAR(258)') AS [Statistics], 
	   x.c.value('@Table', 'NVARCHAR(258)') AS [Table], 
	   x.c.value('@Schema', 'NVARCHAR(258)') AS [Schema], 
	   x.c.value('@Database', 'NVARCHAR(258)') AS [Database]
FROM #query_plan AS qp
CROSS APPLY qp.query_plan.nodes('//p:OptimizerStatsUsage/p:StatisticsInfo') x (c)
OPTION (RECOMPILE);


RAISERROR('Checking for stale stats', 0, 1) WITH NOWAIT;
WITH  stale_stats AS (
	SELECT sa.sql_handle
	FROM #stats_agg AS sa
	GROUP BY sa.sql_handle
	HAVING MAX(sa.last_update) <= DATEADD(DAY, -7, SYSDATETIME())
	AND AVG(sa.modification_count) >= 100000
)
UPDATE b
SET b.stale_stats = 1
FROM #working_warnings AS b
JOIN stale_stats os
ON b.sql_handle = os.sql_handle
OPTION (RECOMPILE);
END;


IF (PARSENAME(CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4)) >= 14
	AND @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for Adaptive Joins', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
aj AS (
	SELECT r.sql_handle
	FROM #relop AS r
	CROSS APPLY r.relop.nodes('//p:RelOp') x(c)
	WHERE x.c.exist('@IsAdaptive[.=1]') = 1
)
UPDATE b
SET b.is_adaptive = 1
FROM #working_warnings AS b
JOIN aj
ON b.sql_handle = aj.sql_handle
OPTION (RECOMPILE);
END; 


IF @ExpertMode > 0
BEGIN;
RAISERROR(N'Checking for Row Goals', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
row_goals AS(
SELECT qs.query_hash
FROM   #relop qs
WHERE relop.value('sum(/p:RelOp/@EstimateRowsWithoutRowGoal)', 'float') > 0
)
UPDATE b
SET b.is_row_goal = 1
FROM #working_warnings b
JOIN row_goals
ON b.query_hash = row_goals.query_hash
OPTION (RECOMPILE);
END;

END; 


RAISERROR(N'Performing query level checks', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE  b
SET     b.missing_index_count = query_plan.value('count(//p:QueryPlan/p:MissingIndexes/p:MissingIndexGroup)', 'int') ,
		b.unmatched_index_count = CASE WHEN is_trivial <> 1 THEN query_plan.value('count(//p:QueryPlan/p:UnmatchedIndexes/p:Parameterization/p:Object)', 'int') END
FROM    #query_plan qp
JOIN #working_warnings AS b
ON b.query_hash = qp.query_hash
OPTION (RECOMPILE);


RAISERROR(N'Trace flag checks', 0, 1) WITH NOWAIT;
;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
, tf_pretty AS (
SELECT  qp.sql_handle,
		q.n.value('@Value', 'INT') AS trace_flag,
		q.n.value('@Scope', 'VARCHAR(10)') AS scope
FROM    #query_plan qp
CROSS APPLY qp.query_plan.nodes('/p:QueryPlan/p:TraceFlags/p:TraceFlag') AS q(n)
)
INSERT #trace_flags WITH (TABLOCK)
		(sql_handle, global_trace_flags, session_trace_flags )
SELECT DISTINCT tf1.sql_handle ,
    STUFF((
          SELECT DISTINCT N', ' + CONVERT(NVARCHAR(5), tf2.trace_flag)
          FROM  tf_pretty AS tf2 
          WHERE tf1.sql_handle = tf2.sql_handle 
		  AND tf2.scope = 'Global'
        FOR XML PATH(N'')), 1, 2, N''
      ) AS global_trace_flags,
    STUFF((
          SELECT DISTINCT N', ' + CONVERT(NVARCHAR(5), tf2.trace_flag)
          FROM  tf_pretty AS tf2 
          WHERE tf1.sql_handle = tf2.sql_handle 
		  AND tf2.scope = 'Session'
        FOR XML PATH(N'')), 1, 2, N''
      ) AS session_trace_flags
FROM tf_pretty AS tf1
OPTION (RECOMPILE);

UPDATE b
SET    b.trace_flags_session = tf.session_trace_flags
FROM   #working_warnings AS b
JOIN #trace_flags tf 
ON tf.sql_handle = b.sql_handle 
OPTION (RECOMPILE);


RAISERROR(N'Checking for MSTVFs', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_mstvf = 1
FROM #relop AS r
JOIN #working_warnings AS b
ON b.sql_handle = r.sql_handle
WHERE  r.relop.exist('/p:RelOp[(@EstimateRows="100" or @EstimateRows="1") and @LogicalOp="Table-valued function"]') = 1
OPTION (RECOMPILE);

IF @ExpertMode > 0
BEGIN
RAISERROR(N'Checking for many to many merge joins', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET b.is_mm_join = 1
FROM #relop AS r
JOIN #working_warnings AS b
ON b.sql_handle = r.sql_handle
WHERE  r.relop.exist('/p:RelOp/p:Merge/@ManyToMany[.="1"]') = 1
OPTION (RECOMPILE);
END;


IF @ExpertMode > 0
BEGIN
RAISERROR(N'Is Paul White Electric?', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
is_paul_white_electric AS (
SELECT 1 AS [is_paul_white_electric], 
r.sql_handle
FROM #relop AS r
CROSS APPLY r.relop.nodes('//p:RelOp') c(n)
WHERE c.n.exist('@PhysicalOp[.="Switch"]') = 1
)
UPDATE b
SET    b.is_paul_white_electric = ipwe.is_paul_white_electric
FROM   #working_warnings AS b
JOIN is_paul_white_electric ipwe 
ON ipwe.sql_handle = b.sql_handle 
OPTION (RECOMPILE);
END;



RAISERROR(N'Checking for non-sargable predicates', 0, 1) WITH NOWAIT;
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
, nsarg
    AS (   SELECT       r.query_hash, 1 AS fn, 0 AS jo, 0 AS lk
           FROM         #relop AS r
           CROSS APPLY  r.relop.nodes('/p:RelOp/p:IndexScan/p:Predicate/p:ScalarOperator/p:Compare/p:ScalarOperator') AS ca(x)
           WHERE        (   ca.x.exist('//p:ScalarOperator/p:Intrinsic/@FunctionName') = 1
                            OR     ca.x.exist('//p:ScalarOperator/p:IF') = 1 )
           UNION ALL
           SELECT       r.query_hash, 0 AS fn, 1 AS jo, 0 AS lk
           FROM         #relop AS r
           CROSS APPLY  r.relop.nodes('/p:RelOp//p:ScalarOperator') AS ca(x)
           WHERE        r.relop.exist('/p:RelOp[contains(@LogicalOp, "Join")]') = 1
                        AND ca.x.exist('//p:ScalarOperator[contains(@ScalarString, "Expr")]') = 1
           UNION ALL
           SELECT       r.query_hash, 0 AS fn, 0 AS jo, 1 AS lk
           FROM         #relop AS r
           CROSS APPLY  r.relop.nodes('/p:RelOp/p:IndexScan/p:Predicate/p:ScalarOperator') AS ca(x)
           CROSS APPLY  ca.x.nodes('//p:Const') AS co(x)
           WHERE        ca.x.exist('//p:ScalarOperator/p:Intrinsic/@FunctionName[.="like"]') = 1
                        AND (   (   co.x.value('substring(@ConstValue, 1, 1)', 'VARCHAR(100)') <> 'N'
                                    AND co.x.value('substring(@ConstValue, 2, 1)', 'VARCHAR(100)') = '%' )
                                OR (   co.x.value('substring(@ConstValue, 1, 1)', 'VARCHAR(100)') = 'N'
                                       AND co.x.value('substring(@ConstValue, 3, 1)', 'VARCHAR(100)') = '%' ))),
  d_nsarg
    AS (   SELECT   DISTINCT
                    nsarg.query_hash
           FROM     nsarg
           WHERE    nsarg.fn = 1
                    OR nsarg.jo = 1
                    OR nsarg.lk = 1 )
UPDATE  b
SET     b.is_nonsargable = 1
FROM    d_nsarg AS d
JOIN    #working_warnings AS b
    ON b.query_hash = d.query_hash
OPTION ( RECOMPILE );


        RAISERROR(N'Getting information about implicit conversions and stored proc parameters', 0, 1) WITH NOWAIT;

		RAISERROR(N'Getting variable info', 0, 1) WITH NOWAIT;
		WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
		INSERT #variable_info ( query_hash, sql_handle, proc_name, variable_name, variable_datatype, compile_time_value )
		SELECT      DISTINCT
		            qp.query_hash,
		            qp.sql_handle,
		            b.proc_or_function_name AS proc_name,
		            q.n.value('@Column', 'NVARCHAR(258)') AS variable_name,
		            q.n.value('@ParameterDataType', 'NVARCHAR(258)') AS variable_datatype,
		            q.n.value('@ParameterCompiledValue', 'NVARCHAR(258)') AS compile_time_value
		FROM        #query_plan AS qp
           JOIN     #working_warnings AS b
           ON (b.query_hash = qp.query_hash AND b.proc_or_function_name = 'adhoc')
		   OR (b.sql_handle = qp.sql_handle AND b.proc_or_function_name <> 'adhoc')
		CROSS APPLY qp.query_plan.nodes('//p:QueryPlan/p:ParameterList/p:ColumnReference') AS q(n)
		OPTION (RECOMPILE);

		RAISERROR(N'Getting conversion info', 0, 1) WITH NOWAIT;
		WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
		INSERT #conversion_info ( query_hash, sql_handle, proc_name, expression )
		SELECT      DISTINCT
		            qp.query_hash,
		            qp.sql_handle,
		            b.proc_or_function_name AS proc_name,
		            qq.c.value('@Expression', 'NVARCHAR(4000)') AS expression
		FROM        #query_plan AS qp
		   JOIN     #working_warnings AS b
           ON (b.query_hash = qp.query_hash AND b.proc_or_function_name = 'adhoc')
		   OR (b.sql_handle = qp.sql_handle AND b.proc_or_function_name <> 'adhoc')
		CROSS APPLY qp.query_plan.nodes('//p:QueryPlan/p:Warnings/p:PlanAffectingConvert') AS qq(c)
		WHERE       qq.c.exist('@ConvertIssue[.="Seek Plan"]') = 1
		            AND b.implicit_conversions = 1
		OPTION (RECOMPILE);

		RAISERROR(N'Parsing conversion info', 0, 1) WITH NOWAIT;
		INSERT #stored_proc_info ( sql_handle, query_hash, proc_name, variable_name, variable_datatype, converted_column_name, column_name, converted_to, compile_time_value )
		SELECT ci.sql_handle,
		       ci.query_hash,
		       ci.proc_name,
		       CASE WHEN ci.at_charindex > 0
		                 AND ci.bracket_charindex > 0 
					THEN SUBSTRING(ci.expression, ci.at_charindex, ci.bracket_charindex)
		            ELSE N'**no_variable**'
		       END AS variable_name,
			   N'**no_variable**' AS variable_datatype,
		       CASE WHEN ci.at_charindex = 0
		                 AND ci.comma_charindex > 0
		                 AND ci.second_comma_charindex > 0 
					THEN SUBSTRING(ci.expression, ci.comma_charindex, ci.second_comma_charindex)
		            ELSE N'**no_column**'
		       END AS converted_column_name,
		       CASE WHEN ci.at_charindex = 0
		                 AND ci.equal_charindex > 0 
						 AND ci.convert_implicit_charindex = 0
					THEN SUBSTRING(ci.expression, ci.equal_charindex, 4000)
					WHEN ci.at_charindex = 0
		                 AND ci.equal_charindex > 0 
						 AND ci.convert_implicit_charindex > 0
					THEN SUBSTRING(ci.expression, 0, ci.equal_charindex -1)
		            WHEN ci.at_charindex > 0
		                 AND ci.comma_charindex > 0
		                 AND ci.second_comma_charindex > 0 
					THEN SUBSTRING(ci.expression, ci.comma_charindex, ci.second_comma_charindex)
		            ELSE N'**no_column **'
		       END AS column_name,
		       CASE WHEN ci.paren_charindex > 0
		                 AND ci.comma_paren_charindex > 0 
					THEN SUBSTRING(ci.expression, ci.paren_charindex, ci.comma_paren_charindex)
		       END AS converted_to,
		       CASE WHEN ci.at_charindex = 0
		                 AND ci.convert_implicit_charindex = 0
		                 AND ci.proc_name = 'Statement' 
					THEN SUBSTRING(ci.expression, ci.equal_charindex, 4000)
		            ELSE '**idk_man**'
		       END AS compile_time_value
		FROM   #conversion_info AS ci
		OPTION (RECOMPILE);

		RAISERROR(N'Updating variables inserted procs', 0, 1) WITH NOWAIT;
		UPDATE sp
		SET sp.variable_datatype = vi.variable_datatype,
			sp.compile_time_value = vi.compile_time_value
		FROM   #stored_proc_info AS sp
		JOIN #variable_info AS vi
		ON (sp.proc_name = 'adhoc' AND sp.query_hash = vi.query_hash)
		OR 	(sp.proc_name <> 'adhoc' AND sp.sql_handle = vi.sql_handle)
		AND sp.variable_name = vi.variable_name
		OPTION (RECOMPILE);
		
		
		RAISERROR(N'Inserting variables for other procs', 0, 1) WITH NOWAIT;
		INSERT #stored_proc_info 
				( sql_handle, query_hash, variable_name, variable_datatype, compile_time_value, proc_name )
		SELECT vi.sql_handle, vi.query_hash, vi.variable_name, vi.variable_datatype, vi.compile_time_value, vi.proc_name
		FROM #variable_info AS vi
		WHERE NOT EXISTS
		(
			SELECT * 
			FROM   #stored_proc_info AS sp
			WHERE (sp.proc_name = 'adhoc' AND sp.query_hash = vi.query_hash)
			OR 	(sp.proc_name <> 'adhoc' AND sp.sql_handle = vi.sql_handle)
		)
		OPTION (RECOMPILE);
		
		RAISERROR(N'Updating procs', 0, 1) WITH NOWAIT;
		UPDATE s
		SET    s.variable_datatype = CASE WHEN s.variable_datatype LIKE '%(%)%' THEN
		                                      LEFT(s.variable_datatype, CHARINDEX('(', s.variable_datatype) - 1)
										  ELSE s.variable_datatype
		                             END,
		       s.converted_to = CASE WHEN s.converted_to LIKE '%(%)%' THEN
		                                 LEFT(s.converted_to, CHARINDEX('(', s.converted_to) - 1)
		                             ELSE s.converted_to
		                        END,
			   s.compile_time_value = CASE WHEN s.compile_time_value LIKE '%(%)%' THEN
												SUBSTRING(s.compile_time_value, 
															CHARINDEX('(', s.compile_time_value) + 1,
															CHARINDEX(')', s.compile_time_value) - 1
															- CHARINDEX('(', s.compile_time_value)
															)
											WHEN variable_datatype NOT IN ('bit', 'tinyint', 'smallint', 'int', 'bigint') 
												AND s.variable_datatype NOT LIKE '%binary%' 
												AND s.compile_time_value NOT LIKE 'N''%'''
												AND s.compile_time_value NOT LIKE '''%''' THEN
												QUOTENAME(compile_time_value, '''')
									ELSE s.compile_time_value 
									  END
		FROM   #stored_proc_info AS s
		OPTION (RECOMPILE);

		
		RAISERROR(N'Updating SET options', 0, 1) WITH NOWAIT;
		WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
		UPDATE s
		SET set_options = set_options.ansi_set_options
		FROM #stored_proc_info AS s
		JOIN (
				SELECT  x.sql_handle,
						N'SET ANSI_NULLS = ' + CASE WHEN [ANSI_NULLS] = 'true' THEN N'ON ' ELSE N'OFF ' END + NCHAR(10) +
						N'SET ANSI_PADDING = ' + CASE WHEN [ANSI_PADDING] = 'true' THEN N'ON ' ELSE N'OFF ' END + NCHAR(10) +
						N'SET ANSI_WARNINGS = ' + CASE WHEN [ANSI_WARNINGS] = 'true' THEN N'ON ' ELSE N'OFF ' END + NCHAR(10) +
						N'SET ARITHABORT = ' + CASE WHEN [ARITHABORT] = 'true' THEN N'ON ' ELSE N' OFF ' END + NCHAR(10) +
						N'SET CONCAT_NULL_YIELDS_NULL = ' + CASE WHEN [CONCAT_NULL_YIELDS_NULL] = 'true' THEN N'ON ' ELSE N'OFF ' END + NCHAR(10) +
						N'SET NUMERIC_ROUNDABORT = ' + CASE WHEN [NUMERIC_ROUNDABORT] = 'true' THEN N'ON ' ELSE N'OFF ' END + NCHAR(10) +
						N'SET QUOTED_IDENTIFIER = ' + CASE WHEN [QUOTED_IDENTIFIER] = 'true' THEN N'ON ' ELSE N'OFF ' + NCHAR(10) END AS [ansi_set_options]
				FROM (
					SELECT
						s.sql_handle,
						so.o.value('@ANSI_NULLS', 'NVARCHAR(20)') AS [ANSI_NULLS],
						so.o.value('@ANSI_PADDING', 'NVARCHAR(20)') AS [ANSI_PADDING],
						so.o.value('@ANSI_WARNINGS', 'NVARCHAR(20)') AS [ANSI_WARNINGS],
						so.o.value('@ARITHABORT', 'NVARCHAR(20)') AS [ARITHABORT],
						so.o.value('@CONCAT_NULL_YIELDS_NULL', 'NVARCHAR(20)') AS [CONCAT_NULL_YIELDS_NULL],
						so.o.value('@NUMERIC_ROUNDABORT', 'NVARCHAR(20)') AS [NUMERIC_ROUNDABORT],
						so.o.value('@QUOTED_IDENTIFIER', 'NVARCHAR(20)') AS [QUOTED_IDENTIFIER]
					FROM #statements AS s
					CROSS APPLY s.statement.nodes('//p:StatementSetOptions') AS so(o)
				   ) AS x
		) AS set_options ON set_options.sql_handle = s.sql_handle
		OPTION(RECOMPILE);


		RAISERROR(N'Updating conversion XML', 0, 1) WITH NOWAIT;
		WITH precheck AS (
		SELECT spi.sql_handle,
			   spi.proc_name,
					(SELECT CASE WHEN spi.proc_name <> 'Statement' 
						   THEN N'The stored procedure ' + spi.proc_name 
						   ELSE N'This ad hoc statement' 
					  END
					+ N' had the following implicit conversions: '
					+ CHAR(10)
					+ STUFF((
						SELECT DISTINCT 
								@cr + @lf
								+ CASE WHEN spi2.variable_name <> N'**no_variable**'
									   THEN N'The variable '
									   WHEN spi2.variable_name = N'**no_variable**' AND (spi2.column_name = spi2.converted_column_name OR spi2.column_name LIKE '%CONVERT_IMPLICIT%')
									   THEN N'The compiled value '
									   WHEN spi2.column_name LIKE '%Expr%'
									   THEN 'The expression '
									   ELSE N'The column '
								  END 
								+ CASE WHEN spi2.variable_name <> N'**no_variable**'
									   THEN spi2.variable_name
									   WHEN spi2.variable_name = N'**no_variable**' AND (spi2.column_name = spi2.converted_column_name OR spi2.column_name LIKE '%CONVERT_IMPLICIT%')
									   THEN spi2.compile_time_value
		
									   ELSE spi2.column_name
								  END 
								+ N' has a data type of '
								+ CASE WHEN spi2.variable_datatype = N'**no_variable**' THEN spi2.converted_to
									   ELSE spi2.variable_datatype 
								  END
								+ N' which caused implicit conversion on the column '
								+ CASE WHEN spi2.column_name LIKE N'%CONVERT_IMPLICIT%'
									   THEN spi2.converted_column_name
									   WHEN spi2.column_name = N'**no_column**'
									   THEN spi2.converted_column_name
									   WHEN spi2.converted_column_name = N'**no_column**'
									   THEN spi2.column_name
									   WHEN spi2.column_name <> spi2.converted_column_name
									   THEN spi2.converted_column_name
									   ELSE spi2.column_name
								  END
								+ CASE WHEN spi2.variable_name = N'**no_variable**' AND (spi2.column_name = spi2.converted_column_name OR spi2.column_name LIKE '%CONVERT_IMPLICIT%')
									   THEN N''
									   WHEN spi2.column_name LIKE '%Expr%'
									   THEN N''
									   WHEN spi2.compile_time_value NOT IN ('**declared in proc**', '**idk_man**')
									   AND spi2.compile_time_value <> spi2.column_name
									   THEN ' with the value ' + RTRIM(spi2.compile_time_value)
									ELSE N''
								 END 
								+ '.'
						FROM #stored_proc_info AS spi2
						WHERE spi.sql_handle = spi2.sql_handle
						FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'')
					AS [processing-instruction(ClickMe)] FOR XML PATH(''), TYPE )
					AS implicit_conversion_info
		FROM #stored_proc_info AS spi
		GROUP BY spi.sql_handle, spi.proc_name
		)
		UPDATE b
        SET    b.implicit_conversion_info = pk.implicit_conversion_info
        FROM   #working_warnings AS b
        JOIN   precheck AS pk
        ON pk.sql_handle = b.sql_handle
        OPTION (RECOMPILE);

		RAISERROR(N'Updating cached parameter XML for procs', 0, 1) WITH NOWAIT;
		WITH precheck AS (
		SELECT spi.sql_handle,
			   spi.proc_name,
			   (SELECT set_options
					+ @cr + @lf
					+ @cr + @lf
					+ N'EXEC ' 
					+ spi.proc_name 
					+ N' '
					+ STUFF((
						SELECT DISTINCT N', ' 
								+ CASE WHEN spi2.variable_name <> N'**no_variable**' AND spi2.compile_time_value <> N'**idk_man**'
										THEN spi2.variable_name + N' = '
										ELSE @cr + @lf + N' We could not find any cached parameter values for this stored proc. ' 
								  END
								+ CASE WHEN spi2.variable_name = N'**no_variable**' OR spi2.compile_time_value = N'**idk_man**'
									   THEN @cr + @lf + N' Possible reasons include declared variables inside the procedure, recompile hints, etc. '
									   WHEN spi2.compile_time_value = N'NULL' 
									   THEN spi2.compile_time_value 
									   ELSE RTRIM(spi2.compile_time_value)
								  END
						FROM #stored_proc_info AS spi2
						WHERE spi.sql_handle = spi2.sql_handle
						AND spi2.proc_name <> N'Statement'
						FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'')
					AS [processing-instruction(ClickMe)] FOR XML PATH(''), TYPE )
					AS cached_execution_parameters
		FROM #stored_proc_info AS spi
		GROUP BY spi.sql_handle, spi.proc_name, set_options
		) 
		UPDATE b
		SET b.cached_execution_parameters = pk.cached_execution_parameters
        FROM   #working_warnings AS b
        JOIN   precheck AS pk
        ON pk.sql_handle = b.sql_handle
		WHERE b.proc_or_function_name <> N'Statement'
        OPTION (RECOMPILE);


	RAISERROR(N'Updating cached parameter XML for statements', 0, 1) WITH NOWAIT;
	WITH precheck AS (
	SELECT spi.sql_handle,
			   spi.proc_name,
		   (SELECT 
				set_options
				+ @cr + @lf
				+ @cr + @lf
				+ N' See QueryText column for full query text'
				+ @cr + @lf
				+ @cr + @lf
				+ STUFF((
					SELECT DISTINCT N', ' 
							+ CASE WHEN spi2.variable_name <> N'**no_variable**' AND spi2.compile_time_value <> N'**idk_man**'
									THEN spi2.variable_name + N' = '
									ELSE + @cr + @lf + N' We could not find any cached parameter values for this stored proc. ' 
							  END
							+ CASE WHEN spi2.variable_name = N'**no_variable**' OR spi2.compile_time_value = N'**idk_man**'
								   THEN + @cr + @lf + N' Possible reasons include declared variables inside the procedure, recompile hints, etc. '
								   WHEN spi2.compile_time_value = N'NULL' 
								   THEN spi2.compile_time_value 
								   ELSE RTRIM(spi2.compile_time_value)
							  END
					FROM #stored_proc_info AS spi2
					WHERE spi.sql_handle = spi2.sql_handle
					AND spi2.proc_name = N'Statement'
					AND spi2.variable_name NOT LIKE N'%msparam%'
					FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'')
				AS [processing-instruction(ClickMe)] FOR XML PATH(''), TYPE )
				AS cached_execution_parameters
	FROM #stored_proc_info AS spi
	GROUP BY spi.sql_handle, spi.proc_name, spi.set_options
	) 
	UPDATE b
	SET b.cached_execution_parameters = pk.cached_execution_parameters
    FROM   #working_warnings AS b
    JOIN   precheck AS pk
    ON pk.sql_handle = b.sql_handle
	WHERE b.proc_or_function_name = N'Statement'
    OPTION (RECOMPILE);


RAISERROR(N'Filling in implicit conversion info', 0, 1) WITH NOWAIT;
UPDATE b
SET    b.implicit_conversion_info = CASE WHEN b.implicit_conversion_info IS NULL 
									OR CONVERT(NVARCHAR(4000), b.implicit_conversion_info) = N''
									THEN N'<?NoNeedToClickMe -- N/A --?>'
                                    ELSE b.implicit_conversion_info
                                    END,
       b.cached_execution_parameters = CASE WHEN b.cached_execution_parameters IS NULL 
									   OR CONVERT(NVARCHAR(4000), b.cached_execution_parameters) = N''
									   THEN N'<?NoNeedToClickMe -- N/A --?>'
                                       ELSE b.cached_execution_parameters
                                       END
FROM   #working_warnings AS b
OPTION (RECOMPILE);

/*Begin Missing Index*/

IF EXISTS 
	(SELECT 1 FROM #working_warnings AS ww WHERE ww.missing_index_count > 0 )
	
	BEGIN
	
		RAISERROR(N'Inserting to #missing_index_xml', 0, 1) WITH NOWAIT;
		WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
		INSERT 	#missing_index_xml
		SELECT qp.query_hash,
		       qp.sql_handle,
		       c.mg.value('@Impact', 'FLOAT') AS Impact,
			   c.mg.query('.') AS cmg
		FROM   #query_plan AS qp
		CROSS APPLY qp.query_plan.nodes('//p:MissingIndexes/p:MissingIndexGroup') AS c(mg)
		WHERE  qp.query_hash IS NOT NULL
		AND c.mg.value('@Impact', 'FLOAT') > 70.0
		OPTION (RECOMPILE);

		RAISERROR(N'Inserting to #missing_index_schema', 0, 1) WITH NOWAIT;		
		WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
		INSERT #missing_index_schema
		SELECT mix.query_hash, mix.sql_handle, mix.impact,
		       c.mi.value('@Database', 'NVARCHAR(128)') ,
		       c.mi.value('@Schema', 'NVARCHAR(128)') ,
		       c.mi.value('@Table', 'NVARCHAR(128)') ,
			   c.mi.query('.')
		FROM #missing_index_xml AS mix
		CROSS APPLY mix.index_xml.nodes('//p:MissingIndex') AS c(mi)
		OPTION (RECOMPILE);
		
		RAISERROR(N'Inserting to #missing_index_usage', 0, 1) WITH NOWAIT;
		WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
		INSERT #missing_index_usage
		SELECT ms.query_hash, ms.sql_handle, ms.impact, ms.database_name, ms.schema_name, ms.table_name,
				c.cg.value('@Usage', 'NVARCHAR(128)'),
				c.cg.query('.')
		FROM #missing_index_schema ms
		CROSS APPLY ms.index_xml.nodes('//p:ColumnGroup') AS c(cg)
		OPTION (RECOMPILE);
		
		RAISERROR(N'Inserting to #missing_index_detail', 0, 1) WITH NOWAIT;
		WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
		INSERT #missing_index_detail
		SELECT miu.query_hash,
		       miu.sql_handle,
		       miu.impact,
		       miu.database_name,
		       miu.schema_name,
		       miu.table_name,
		       miu.usage,
		       c.c.value('@Name', 'NVARCHAR(128)')
		FROM #missing_index_usage AS miu
		CROSS APPLY miu.index_xml.nodes('//p:Column') AS c(c)
		OPTION (RECOMPILE);
		
		RAISERROR(N'Inserting to #missing_index_pretty', 0, 1) WITH NOWAIT;
		INSERT #missing_index_pretty
		SELECT m.query_hash, m.sql_handle, m.impact, m.database_name, m.schema_name, m.table_name
		, STUFF((   SELECT DISTINCT N', ' + ISNULL(m2.column_name, '') AS column_name
		                 FROM   #missing_index_detail AS m2
		                 WHERE  m2.usage = 'EQUALITY'
						 AND m.query_hash = m2.query_hash
						 AND m.sql_handle = m2.sql_handle
						 AND m.impact = m2.impact
						 AND m.database_name = m2.database_name
						 AND m.schema_name = m2.schema_name
						 AND m.table_name = m2.table_name
		                 FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') AS equality
		, STUFF((   SELECT DISTINCT N', ' + ISNULL(m2.column_name, '') AS column_name
		                 FROM   #missing_index_detail AS m2
		                 WHERE  m2.usage = 'INEQUALITY'
						 AND m.query_hash = m2.query_hash
						 AND m.sql_handle = m2.sql_handle
						 AND m.impact = m2.impact
						 AND m.database_name = m2.database_name
						 AND m.schema_name = m2.schema_name
						 AND m.table_name = m2.table_name
		                 FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') AS inequality
		, STUFF((   SELECT DISTINCT N', ' + ISNULL(m2.column_name, '') AS column_name
		                 FROM   #missing_index_detail AS m2
		                 WHERE  m2.usage = 'INCLUDE'
						 AND m.query_hash = m2.query_hash
						 AND m.sql_handle = m2.sql_handle
						 AND m.impact = m2.impact
						 AND m.database_name = m2.database_name
						 AND m.schema_name = m2.schema_name
						 AND m.table_name = m2.table_name
		                 FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') AS [include]
		FROM #missing_index_detail AS m
		GROUP BY m.query_hash, m.sql_handle, m.impact, m.database_name, m.schema_name, m.table_name
		OPTION (RECOMPILE);
		
		RAISERROR(N'Updating missing index information', 0, 1) WITH NOWAIT;
		WITH missing AS (
		SELECT mip.query_hash,
		       mip.sql_handle, 
			   CONVERT(XML,
			   N'<MissingIndexes><![CDATA['
			   + CHAR(10) + CHAR(13)
			   + STUFF((   SELECT CHAR(10) + CHAR(13) + ISNULL(mip2.details, '') AS details
		                   FROM   #missing_index_pretty AS mip2
						   WHERE mip.query_hash = mip2.query_hash
						   AND mip.sql_handle = mip2.sql_handle
						   GROUP BY mip2.details
		                   ORDER BY MAX(mip2.impact) DESC
						   FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') 
			   + CHAR(10) + CHAR(13)
			   + N']]></MissingIndexes>'  
			   ) AS full_details
		FROM #missing_index_pretty AS mip
		GROUP BY mip.query_hash, mip.sql_handle, mip.impact
						)
		UPDATE ww
			SET ww.missing_indexes = m.full_details
		FROM #working_warnings AS ww
		JOIN missing AS m
		ON m.sql_handle = ww.sql_handle
		OPTION (RECOMPILE);

	
	END;

	RAISERROR(N'Filling in missing index blanks', 0, 1) WITH NOWAIT;
	UPDATE ww
	SET ww.missing_indexes = 
		CASE WHEN ww.missing_indexes IS NULL 
			 THEN '<?NoNeedToClickMe -- N/A --?>' 
			 ELSE ww.missing_indexes 
		END
	FROM #working_warnings AS ww
	OPTION (RECOMPILE);

/*End Missing Index*/

RAISERROR(N'General query dispositions: frequent executions, long running, etc.', 0, 1) WITH NOWAIT;

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE b
SET    b.frequent_execution = CASE WHEN wm.xpm > @execution_threshold THEN 1 END ,
	   b.near_parallel = CASE WHEN b.query_cost BETWEEN @ctp * (1 - (@ctp_threshold_pct / 100.0)) AND @ctp THEN 1 END,
	   b.long_running = CASE WHEN wm.avg_duration > @long_running_query_warning_seconds THEN 1
						     WHEN wm.max_duration > @long_running_query_warning_seconds THEN 1
                             WHEN wm.avg_cpu_time > @long_running_query_warning_seconds THEN 1
                             WHEN wm.max_cpu_time > @long_running_query_warning_seconds THEN 1 END,
	   b.is_key_lookup_expensive = CASE WHEN b.query_cost >= (@ctp / 2) AND b.key_lookup_cost >= b.query_cost * .5 THEN 1 END,
	   b.is_sort_expensive = CASE WHEN b.query_cost >= (@ctp / 2) AND b.sort_cost >= b.query_cost * .5 THEN 1 END,
	   b.is_remote_query_expensive = CASE WHEN b.remote_query_cost >= b.query_cost * .05 THEN 1 END,
	   b.is_unused_grant = CASE WHEN percent_memory_grant_used <= @memory_grant_warning_percent AND min_grant_kb > @min_memory_per_query THEN 1 END,
	   b.long_running_low_cpu = CASE WHEN wm.avg_duration > wm.avg_cpu_time * 4 AND avg_cpu_time < 500. THEN 1 END,
	   b.low_cost_high_cpu = CASE WHEN b.query_cost < 10 AND wm.avg_cpu_time > 5000. THEN 1 END,
	   b.is_spool_expensive = CASE WHEN b.query_cost > (@ctp / 2) AND b.index_spool_cost >= b.query_cost * .1 THEN 1 END,
	   b.is_spool_more_rows = CASE WHEN b.index_spool_rows >= wm.min_rowcount THEN 1 END,
	   b.is_bad_estimate = CASE WHEN wm.avg_rowcount > 0 AND (b.estimated_rows * 1000 < wm.avg_rowcount OR b.estimated_rows > wm.avg_rowcount * 1000) THEN 1 END,
	   b.is_big_log = CASE WHEN wm.avg_log_bytes_used >= (@log_size_mb / 2.) THEN 1 END,
	   b.is_big_tempdb = CASE WHEN wm.avg_tempdb_space_used >= (@avg_tempdb_data_file / 2.) THEN 1 END
FROM #working_warnings AS b
JOIN #working_metrics AS wm
ON b.plan_id = wm.plan_id
AND b.query_id = wm.query_id
JOIN #working_plan_text AS wpt
ON b.plan_id = wpt.plan_id
AND b.query_id = wpt.query_id
OPTION (RECOMPILE);


RAISERROR('Populating Warnings column', 0, 1) WITH NOWAIT;
/* Populate warnings */
UPDATE b
SET    b.warnings = SUBSTRING(
                  CASE WHEN b.warning_no_join_predicate = 1 THEN ', No Join Predicate' ELSE '' END +
                  CASE WHEN b.compile_timeout = 1 THEN ', Compilation Timeout' ELSE '' END +
                  CASE WHEN b.compile_memory_limit_exceeded = 1 THEN ', Compile Memory Limit Exceeded' ELSE '' END +
                  CASE WHEN b.is_forced_plan = 1 THEN ', Forced Plan' ELSE '' END +
                  CASE WHEN b.is_forced_parameterized = 1 THEN ', Forced Parameterization' ELSE '' END +
                  CASE WHEN b.unparameterized_query = 1 THEN ', Unparameterized Query' ELSE '' END +
                  CASE WHEN b.missing_index_count > 0 THEN ', Missing Indexes (' + CAST(b.missing_index_count AS NVARCHAR(3)) + ')' ELSE '' END +
                  CASE WHEN b.unmatched_index_count > 0 THEN ', Unmatched Indexes (' + CAST(b.unmatched_index_count AS NVARCHAR(3)) + ')' ELSE '' END +                  
                  CASE WHEN b.is_cursor = 1 THEN ', Cursor' 
							+ CASE WHEN b.is_optimistic_cursor = 1 THEN '; optimistic' ELSE '' END
							+ CASE WHEN b.is_forward_only_cursor = 0 THEN '; not forward only' ELSE '' END
							+ CASE WHEN b.is_cursor_dynamic = 1 THEN '; dynamic' ELSE '' END
                            + CASE WHEN b.is_fast_forward_cursor = 1 THEN '; fast forward' ELSE '' END			
				  ELSE '' END +
                  CASE WHEN b.is_parallel = 1 THEN ', Parallel' ELSE '' END +
                  CASE WHEN b.near_parallel = 1 THEN ', Nearly Parallel' ELSE '' END +
                  CASE WHEN b.frequent_execution = 1 THEN ', Frequent Execution' ELSE '' END +
                  CASE WHEN b.plan_warnings = 1 THEN ', Plan Warnings' ELSE '' END +
                  CASE WHEN b.parameter_sniffing = 1 THEN ', Parameter Sniffing' ELSE '' END +
                  CASE WHEN b.long_running = 1 THEN ', Long Running Query' ELSE '' END +
                  CASE WHEN b.downlevel_estimator = 1 THEN ', Downlevel CE' ELSE '' END +
                  CASE WHEN b.implicit_conversions = 1 THEN ', Implicit Conversions' ELSE '' END +
                  CASE WHEN b.plan_multiple_plans = 1 THEN ', Multiple Plans' ELSE '' END +
                  CASE WHEN b.is_trivial = 1 THEN ', Trivial Plans' ELSE '' END +
				  CASE WHEN b.is_forced_serial = 1 THEN ', Forced Serialization' ELSE '' END +
				  CASE WHEN b.is_key_lookup_expensive = 1 THEN ', Expensive Key Lookup' ELSE '' END +
				  CASE WHEN b.is_remote_query_expensive = 1 THEN ', Expensive Remote Query' ELSE '' END + 
				  CASE WHEN b.trace_flags_session IS NOT NULL THEN ', Session Level Trace Flag(s) Enabled: ' + b.trace_flags_session ELSE '' END +
				  CASE WHEN b.is_unused_grant = 1 THEN ', Unused Memory Grant' ELSE '' END +
				  CASE WHEN b.function_count > 0 THEN ', Calls ' + CONVERT(VARCHAR(10), b.function_count) + ' function(s)' ELSE '' END + 
				  CASE WHEN b.clr_function_count > 0 THEN ', Calls ' + CONVERT(VARCHAR(10), b.clr_function_count) + ' CLR function(s)' ELSE '' END + 
				  CASE WHEN b.is_table_variable = 1 THEN ', Table Variables' ELSE '' END +
				  CASE WHEN b.no_stats_warning = 1 THEN ', Columns With No Statistics' ELSE '' END +
				  CASE WHEN b.relop_warnings = 1 THEN ', Operator Warnings' ELSE '' END  + 
				  CASE WHEN b.is_table_scan = 1 THEN ', Table Scans' ELSE '' END  + 
				  CASE WHEN b.backwards_scan = 1 THEN ', Backwards Scans' ELSE '' END  + 
				  CASE WHEN b.forced_index = 1 THEN ', Forced Indexes' ELSE '' END  + 
				  CASE WHEN b.forced_seek = 1 THEN ', Forced Seeks' ELSE '' END  + 
				  CASE WHEN b.forced_scan = 1 THEN ', Forced Scans' ELSE '' END  +
				  CASE WHEN b.columnstore_row_mode = 1 THEN ', ColumnStore Row Mode ' ELSE '' END +
				  CASE WHEN b.is_computed_scalar = 1 THEN ', Computed Column UDF ' ELSE '' END  +
				  CASE WHEN b.is_sort_expensive = 1 THEN ', Expensive Sort' ELSE '' END +
				  CASE WHEN b.is_computed_filter = 1 THEN ', Filter UDF' ELSE '' END +
				  CASE WHEN b.index_ops >= 5 THEN ', >= 5 Indexes Modified' ELSE '' END +
				  CASE WHEN b.is_row_level = 1 THEN ', Row Level Security' ELSE '' END + 
				  CASE WHEN b.is_spatial = 1 THEN ', Spatial Index' ELSE '' END + 
				  CASE WHEN b.index_dml = 1 THEN ', Index DML' ELSE '' END +
				  CASE WHEN b.table_dml = 1 THEN ', Table DML' ELSE '' END +
				  CASE WHEN b.low_cost_high_cpu = 1 THEN ', Low Cost High CPU' ELSE '' END + 
				  CASE WHEN b.long_running_low_cpu = 1 THEN + ', Long Running With Low CPU' ELSE '' END +
				  CASE WHEN b.stale_stats = 1 THEN + ', Statistics used have > 100k modifications in the last 7 days' ELSE '' END +
				  CASE WHEN b.is_adaptive = 1 THEN + ', Adaptive Joins' ELSE '' END	+
				  CASE WHEN b.is_spool_expensive = 1 THEN + ', Expensive Index Spool' ELSE '' END +
				  CASE WHEN b.is_spool_more_rows = 1 THEN + ', Large Index Row Spool' ELSE '' END +
				  CASE WHEN b.is_bad_estimate = 1 THEN + ', Row estimate mismatch' ELSE '' END +
				  CASE WHEN b.is_big_log = 1 THEN + ', High log use' ELSE '' END +
				  CASE WHEN b.is_big_tempdb = 1 THEN ', High tempdb use' ELSE '' END +
				  CASE WHEN b.is_paul_white_electric = 1 THEN ', SWITCH!' ELSE '' END + 
				  CASE WHEN b.is_row_goal = 1 THEN ', Row Goals' ELSE '' END + 
				  CASE WHEN b.is_mstvf = 1 THEN ', MSTVFs' ELSE '' END + 
				  CASE WHEN b.is_mm_join = 1 THEN ', Many to Many Merge' ELSE '' END + 
                  CASE WHEN b.is_nonsargable = 1 THEN ', non-SARGables' ELSE '' END
                  , 2, 200000) 
FROM #working_warnings b
OPTION (RECOMPILE);


END;
END TRY
BEGIN CATCH
        RAISERROR (N'Failure generating warnings.', 0,1) WITH NOWAIT;

        IF @sql_select IS NOT NULL
        BEGIN
            SET @msg = N'Last @sql_select: ' + @sql_select;
            RAISERROR(@msg, 0, 1) WITH NOWAIT;
        END;

        SELECT    @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
        RAISERROR (@msg, @error_severity, @error_state) WITH NOWAIT;
        
        
        WHILE @@TRANCOUNT > 0 
            ROLLBACK;

        RETURN;
END CATCH;


BEGIN TRY 
BEGIN 

RAISERROR(N'Checking for parameter sniffing symptoms', 0, 1) WITH NOWAIT;

UPDATE b
SET b.parameter_sniffing_symptoms = 
CASE WHEN b.count_executions < 4 THEN 'Too few executions to compare (< 4).'
	ELSE													
	SUBSTRING(  
				/*Duration*/
				CASE WHEN (b.min_duration * 100) < (b.avg_duration) THEN ', Fast sometimes' ELSE '' END +
				CASE WHEN (b.max_duration) > (b.avg_duration * 100) THEN ', Slow sometimes' ELSE '' END +
				CASE WHEN (b.last_duration * 100) < (b.avg_duration)  THEN ', Fast last run' ELSE '' END +
				CASE WHEN (b.last_duration) > (b.avg_duration * 100) THEN ', Slow last run' ELSE '' END +
				/*CPU*/
				CASE WHEN (b.min_cpu_time / b.avg_dop) * 100 < (b.avg_cpu_time / b.avg_dop) THEN ', Low CPU sometimes' ELSE '' END +
				CASE WHEN (b.max_cpu_time / b.max_dop) > (b.avg_cpu_time / b.avg_dop) * 100 THEN ', High CPU sometimes' ELSE '' END +
				CASE WHEN (b.last_cpu_time / b.last_dop) * 100 < (b.avg_cpu_time / b.avg_dop)  THEN ', Low CPU last run' ELSE '' END +
				CASE WHEN (b.last_cpu_time / b.last_dop) > (b.avg_cpu_time / b.avg_dop) * 100 THEN ', High CPU last run' ELSE '' END +
				/*Logical Reads*/
				CASE WHEN (b.min_logical_io_reads * 100) < (b.avg_logical_io_reads) THEN ', Low reads sometimes' ELSE '' END +
				CASE WHEN (b.max_logical_io_reads) > (b.avg_logical_io_reads * 100) THEN ', High reads sometimes' ELSE '' END +
				CASE WHEN (b.last_logical_io_reads * 100) < (b.avg_logical_io_reads)  THEN ', Low reads last run' ELSE '' END +
				CASE WHEN (b.last_logical_io_reads) > (b.avg_logical_io_reads * 100) THEN ', High reads last run' ELSE '' END +
				/*Logical Writes*/
				CASE WHEN (b.min_logical_io_writes * 100) < (b.avg_logical_io_writes) THEN ', Low writes sometimes' ELSE '' END +
				CASE WHEN (b.max_logical_io_writes) > (b.avg_logical_io_writes * 100) THEN ', High writes sometimes' ELSE '' END +
				CASE WHEN (b.last_logical_io_writes * 100) < (b.avg_logical_io_writes)  THEN ', Low writes last run' ELSE '' END +
				CASE WHEN (b.last_logical_io_writes) > (b.avg_logical_io_writes * 100) THEN ', High writes last run' ELSE '' END +
				/*Physical Reads*/
				CASE WHEN (b.min_physical_io_reads * 100) < (b.avg_physical_io_reads) THEN ', Low physical reads sometimes' ELSE '' END +
				CASE WHEN (b.max_physical_io_reads) > (b.avg_physical_io_reads * 100) THEN ', High physical reads sometimes' ELSE '' END +
				CASE WHEN (b.last_physical_io_reads * 100) < (b.avg_physical_io_reads)  THEN ', Low physical reads last run' ELSE '' END +
				CASE WHEN (b.last_physical_io_reads) > (b.avg_physical_io_reads * 100) THEN ', High physical reads last run' ELSE '' END +
				/*Memory*/
				CASE WHEN (b.min_query_max_used_memory * 100) < (b.avg_query_max_used_memory) THEN ', Low memory sometimes' ELSE '' END +
				CASE WHEN (b.max_query_max_used_memory) > (b.avg_query_max_used_memory * 100) THEN ', High memory sometimes' ELSE '' END +
				CASE WHEN (b.last_query_max_used_memory * 100) < (b.avg_query_max_used_memory)  THEN ', Low memory last run' ELSE '' END +
				CASE WHEN (b.last_query_max_used_memory) > (b.avg_query_max_used_memory * 100) THEN ', High memory last run' ELSE '' END +
				/*Duration*/
				CASE WHEN b.min_rowcount * 100 < b.avg_rowcount THEN ', Low row count sometimes' ELSE '' END +
				CASE WHEN b.max_rowcount > b.avg_rowcount * 100 THEN ', High row count sometimes' ELSE '' END +
				CASE WHEN b.last_rowcount * 100 < b.avg_rowcount  THEN ', Low row count run' ELSE '' END +
				CASE WHEN b.last_rowcount > b.avg_rowcount * 100 THEN ', High row count last run' ELSE '' END +
				/*DOP*/
				CASE WHEN b.min_dop = 1 THEN ', Serial sometimes' ELSE '' END +
				CASE WHEN b.max_dop > 1 THEN ', Parallel sometimes' ELSE '' END +
				CASE WHEN b.last_dop = 1  THEN ', Serial last run' ELSE '' END +
				CASE WHEN b.last_dop > 1 THEN ', Parallel last run' ELSE '' END +
				/*tempdb*/
				CASE WHEN b.min_tempdb_space_used * 100 < b.avg_tempdb_space_used THEN ', Low tempdb sometimes' ELSE '' END +
				CASE WHEN b.max_tempdb_space_used > b.avg_tempdb_space_used * 100 THEN ', High tempdb sometimes' ELSE '' END +
				CASE WHEN b.last_tempdb_space_used * 100 < b.avg_tempdb_space_used  THEN ', Low tempdb run' ELSE '' END +
				CASE WHEN b.last_tempdb_space_used > b.avg_tempdb_space_used * 100 THEN ', High tempdb last run' ELSE '' END +
				/*tlog*/
				CASE WHEN b.min_log_bytes_used * 100 < b.avg_log_bytes_used THEN ', Low log use sometimes' ELSE '' END +
				CASE WHEN b.max_log_bytes_used > b.avg_log_bytes_used * 100 THEN ', High log use sometimes' ELSE '' END +
				CASE WHEN b.last_log_bytes_used * 100 < b.avg_log_bytes_used  THEN ', Low log use run' ELSE '' END +
				CASE WHEN b.last_log_bytes_used > b.avg_log_bytes_used * 100 THEN ', High log use last run' ELSE '' END 
	, 2, 200000) 
	END
FROM #working_metrics AS b
OPTION (RECOMPILE);

END;
END TRY
BEGIN CATCH
        RAISERROR (N'Failure analyzing parameter sniffing', 0,1) WITH NOWAIT;

        IF @sql_select IS NOT NULL
        BEGIN
            SET @msg = N'Last @sql_select: ' + @sql_select;
            RAISERROR(@msg, 0, 1) WITH NOWAIT;
        END;

        SELECT    @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
        RAISERROR (@msg, @error_severity, @error_state) WITH NOWAIT;
        
        
        WHILE @@TRANCOUNT > 0 
            ROLLBACK;

        RETURN;
END CATCH;

BEGIN TRY 

BEGIN 

IF (@Failed = 0 AND @ExportToExcel = 0 AND @SkipXML = 0)
BEGIN

RAISERROR(N'Returning regular results', 0, 1) WITH NOWAIT;


WITH x AS (
SELECT wpt.database_name, ww.query_cost, wm.plan_id, wm.query_id, wpt.query_sql_text, wm.proc_or_function_name, wpt.query_plan_xml, ww.warnings, wpt.pattern, 
	   wm.parameter_sniffing_symptoms, wpt.top_three_waits, ww.missing_indexes, ww.implicit_conversion_info, ww.cached_execution_parameters, wm.count_executions, wm.count_compiles, wm.total_cpu_time, wm.avg_cpu_time,
	   wm.total_duration, wm.avg_duration, wm.total_logical_io_reads, wm.avg_logical_io_reads,
	   wm.total_physical_io_reads, wm.avg_physical_io_reads, wm.total_logical_io_writes, wm.avg_logical_io_writes, wm.total_rowcount, wm.avg_rowcount,
	   wm.total_query_max_used_memory, wm.avg_query_max_used_memory, wm.total_tempdb_space_used, wm.avg_tempdb_space_used,
	   wm.total_log_bytes_used, wm.avg_log_bytes_used, wm.total_num_physical_io_reads, wm.avg_num_physical_io_reads,
	   wm.first_execution_time, wm.last_execution_time, wpt.last_force_failure_reason_desc, wpt.context_settings, ROW_NUMBER() OVER (PARTITION BY wm.plan_id, wm.query_id, wm.last_execution_time ORDER BY wm.plan_id) AS rn
FROM #working_plan_text AS wpt
JOIN #working_warnings AS ww
	ON wpt.plan_id = ww.plan_id
	AND wpt.query_id = ww.query_id
JOIN #working_metrics AS wm
	ON wpt.plan_id = wm.plan_id
	AND wpt.query_id = wm.query_id
)
SELECT *
FROM x
WHERE x.rn = 1
ORDER BY x.last_execution_time
OPTION (RECOMPILE);

END;

IF (@Failed = 1 AND @ExportToExcel = 0 AND @SkipXML = 0)
BEGIN

RAISERROR(N'Returning results for failed queries', 0, 1) WITH NOWAIT;

WITH x AS (
SELECT wpt.database_name, ww.query_cost, wm.plan_id, wm.query_id, wpt.query_sql_text, wm.proc_or_function_name, wpt.query_plan_xml, ww.warnings, wpt.pattern, 
	   wm.parameter_sniffing_symptoms, wpt.last_force_failure_reason_desc, wpt.top_three_waits, ww.missing_indexes, ww.implicit_conversion_info, ww.cached_execution_parameters, 
	   wm.count_executions, wm.count_compiles, wm.total_cpu_time, wm.avg_cpu_time,
	   wm.total_duration, wm.avg_duration, wm.total_logical_io_reads, wm.avg_logical_io_reads,
	   wm.total_physical_io_reads, wm.avg_physical_io_reads, wm.total_logical_io_writes, wm.avg_logical_io_writes, wm.total_rowcount, wm.avg_rowcount,
	   wm.total_query_max_used_memory, wm.avg_query_max_used_memory, wm.total_tempdb_space_used, wm.avg_tempdb_space_used,
	   wm.total_log_bytes_used, wm.avg_log_bytes_used, wm.total_num_physical_io_reads, wm.avg_num_physical_io_reads,
	   wm.first_execution_time, wm.last_execution_time, wpt.context_settings, ROW_NUMBER() OVER (PARTITION BY wm.plan_id, wm.query_id, wm.last_execution_time ORDER BY wm.plan_id) AS rn
FROM #working_plan_text AS wpt
JOIN #working_warnings AS ww
	ON wpt.plan_id = ww.plan_id
	AND wpt.query_id = ww.query_id
JOIN #working_metrics AS wm
	ON wpt.plan_id = wm.plan_id
	AND wpt.query_id = wm.query_id
)
SELECT *
FROM x
WHERE x.rn = 1
ORDER BY x.last_execution_time
OPTION (RECOMPILE);

END;

IF (@ExportToExcel = 1 AND @SkipXML = 0)
BEGIN

RAISERROR(N'Returning results for Excel export', 0, 1) WITH NOWAIT;

UPDATE #working_plan_text
SET query_sql_text = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(query_sql_text)),' ','<>'),'><',''),'<>',' '), 1, 31000)
OPTION (RECOMPILE);

WITH x AS (
SELECT wpt.database_name, ww.query_cost, wm.plan_id, wm.query_id, wpt.query_sql_text, wm.proc_or_function_name, ww.warnings, wpt.pattern, 
	   wm.parameter_sniffing_symptoms, wpt.last_force_failure_reason_desc, wpt.top_three_waits, wm.count_executions, wm.count_compiles, wm.total_cpu_time, wm.avg_cpu_time,
	   wm.total_duration, wm.avg_duration, wm.total_logical_io_reads, wm.avg_logical_io_reads,
	   wm.total_physical_io_reads, wm.avg_physical_io_reads, wm.total_logical_io_writes, wm.avg_logical_io_writes, wm.total_rowcount, wm.avg_rowcount,
	   wm.total_query_max_used_memory, wm.avg_query_max_used_memory, wm.total_tempdb_space_used, wm.avg_tempdb_space_used,
	   wm.total_log_bytes_used, wm.avg_log_bytes_used, wm.total_num_physical_io_reads, wm.avg_num_physical_io_reads,
	   wm.first_execution_time, wm.last_execution_time, wpt.context_settings, ROW_NUMBER() OVER (PARTITION BY wm.plan_id, wm.query_id, wm.last_execution_time ORDER BY wm.plan_id) AS rn
FROM #working_plan_text AS wpt
JOIN #working_warnings AS ww
	ON wpt.plan_id = ww.plan_id
	AND wpt.query_id = ww.query_id
JOIN #working_metrics AS wm
	ON wpt.plan_id = wm.plan_id
	AND wpt.query_id = wm.query_id
)
SELECT *
FROM x
WHERE x.rn = 1
ORDER BY x.last_execution_time
OPTION (RECOMPILE);

END;

IF (@ExportToExcel = 0 AND @SkipXML = 1)
BEGIN

RAISERROR(N'Returning results for skipped XML', 0, 1) WITH NOWAIT;

WITH x AS (
SELECT wpt.database_name, wm.plan_id, wm.query_id, wpt.query_sql_text, wpt.query_plan_xml, wpt.pattern, 
	   wm.parameter_sniffing_symptoms, wpt.top_three_waits, wm.count_executions, wm.count_compiles, wm.total_cpu_time, wm.avg_cpu_time,
	   wm.total_duration, wm.avg_duration, wm.total_logical_io_reads, wm.avg_logical_io_reads,
	   wm.total_physical_io_reads, wm.avg_physical_io_reads, wm.total_logical_io_writes, wm.avg_logical_io_writes, wm.total_rowcount, wm.avg_rowcount,
	   wm.total_query_max_used_memory, wm.avg_query_max_used_memory, wm.total_tempdb_space_used, wm.avg_tempdb_space_used,
	   wm.total_log_bytes_used, wm.avg_log_bytes_used, wm.total_num_physical_io_reads, wm.avg_num_physical_io_reads,
	   wm.first_execution_time, wm.last_execution_time, wpt.last_force_failure_reason_desc, wpt.context_settings, ROW_NUMBER() OVER (PARTITION BY wm.plan_id, wm.query_id, wm.last_execution_time ORDER BY wm.plan_id) AS rn
FROM #working_plan_text AS wpt
JOIN #working_metrics AS wm
	ON wpt.plan_id = wm.plan_id
	AND wpt.query_id = wm.query_id
)
SELECT *
FROM x
WHERE x.rn = 1
ORDER BY x.last_execution_time
OPTION (RECOMPILE);

END;

END;
END TRY
BEGIN CATCH
        RAISERROR (N'Failure returning results', 0,1) WITH NOWAIT;

        IF @sql_select IS NOT NULL
        BEGIN
            SET @msg = N'Last @sql_select: ' + @sql_select;
            RAISERROR(@msg, 0, 1) WITH NOWAIT;
        END;

        SELECT    @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
        RAISERROR (@msg, @error_severity, @error_state) WITH NOWAIT;
        
        
        WHILE @@TRANCOUNT > 0 
            ROLLBACK;

        RETURN;
END CATCH;

BEGIN TRY 
BEGIN 

IF (@ExportToExcel = 0 AND @HideSummary = 0 AND @SkipXML = 0)
BEGIN
        RAISERROR('Building query plan summary data.', 0, 1) WITH NOWAIT;

        /* Build summary data */
        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE frequent_execution = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    1,
                    100,
                    'Execution Pattern',
                    'Frequently Executed Queries',
                    'http://brentozar.com/blitzcache/frequently-executed-queries/',
                    'Queries are being executed more than '
                    + CAST (@execution_threshold AS VARCHAR(5))
                    + ' times per minute. This can put additional load on the server, even when queries are lightweight.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  parameter_sniffing = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    2,
                    50,
                    'Parameterization',
                    'Parameter Sniffing',
                    'http://brentozar.com/blitzcache/parameter-sniffing/',
                    'There are signs of parameter sniffing (wide variance in rows return or time to execute). Investigate query patterns and tune code appropriately.') ;

        /* Forced execution plans */
        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_forced_plan = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    3,
                    5,
                    'Parameterization',
                    'Forced Plans',
                    'http://brentozar.com/blitzcache/forced-plans/',
                    'Execution plans have been compiled with forced plans, either through FORCEPLAN, plan guides, or forced parameterization. This will make general tuning efforts less effective.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_cursor = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    4,
                    200,
                    'Cursors',
                    'Cursors',
                    'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
                    'There are cursors in the plan cache. This is neither good nor bad, but it is a thing. Cursors are weird in SQL Server.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_cursor = 1
				   AND is_optimistic_cursor = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    4,
                    200,
                    'Cursors',
                    'Optimistic Cursors',
                    'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
                    'There are optimistic cursors in the plan cache, which can harm performance.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_cursor = 1
				   AND is_forward_only_cursor = 0
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    4,
                    200,
                    'Cursors',
                    'Non-forward Only Cursors',
                    'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
                    'There are non-forward only cursors in the plan cache, which can harm performance.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_cursor = 1
				   AND is_cursor_dynamic = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (4,
                    200,
                    'Cursors',
                    'Dynamic Cursors',
                    'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
                    'Dynamic Cursors inhibit parallelism!.');

		IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_cursor = 1
				   AND is_fast_forward_cursor = 1
                    )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (4,
                    200,
                    'Cursors',
                    'Fast Forward Cursors',
                    'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
                    'Fast forward cursors inhibit parallelism!.');
					
        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_forced_parameterized = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    5,
                    50,
                    'Parameterization',
                    'Forced Parameterization',
                    'http://brentozar.com/blitzcache/forced-parameterization/',
                    'Execution plans have been compiled with forced parameterization.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_parallel = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    6,
                    200,
                    'Execution Plans',
                    'Parallelism',
                    'http://brentozar.com/blitzcache/parallel-plans-detected/',
                    'Parallel plans detected. These warrant investigation, but are neither good nor bad.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.near_parallel = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    7,
                    200,
                    'Execution Plans',
                    'Nearly Parallel',
                    'http://brentozar.com/blitzcache/query-cost-near-cost-threshold-parallelism/',
                    'Queries near the cost threshold for parallelism. These may go parallel when you least expect it.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.plan_warnings = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    8,
                    50,
                    'Execution Plans',
                    'Query Plan Warnings',
                    'http://brentozar.com/blitzcache/query-plan-warnings/',
                    'Warnings detected in execution plans. SQL Server is telling you that something bad is going on that requires your attention.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.long_running = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    9,
                    50,
                    'Performance',
                    'Long Running Queries',
                    'http://brentozar.com/blitzcache/long-running-queries/',
                    'Long running queries have been found. These are queries with an average duration longer than '
                    + CAST(@long_running_query_warning_seconds / 1000 / 1000 AS VARCHAR(5))
                    + ' second(s). These queries should be investigated for additional tuning options.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.missing_index_count > 0
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    10,
                    50,
                    'Performance',
                    'Missing Index Request',
                    'http://brentozar.com/blitzcache/missing-index-request/',
                    'Queries found with missing indexes.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.downlevel_estimator = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    13,
                    200,
                    'Cardinality',
                    'Legacy Cardinality Estimator in Use',
                    'http://brentozar.com/blitzcache/legacy-cardinality-estimator/',
                    'A legacy cardinality estimator is being used by one or more queries. Investigate whether you need to be using this cardinality estimator. This may be caused by compatibility levels, global trace flags, or query level trace flags.');

        IF EXISTS (SELECT 1/0
                   FROM #working_warnings p
                   WHERE p.implicit_conversions = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    14,
                    50,
                    'Performance',
                    'Implicit Conversions',
                    'http://brentozar.com/go/implicit',
                    'One or more queries are comparing two fields that are not of the same data type.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  busy_loops = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                16,
                100,
                'Performance',
                'Busy Loops',
                'http://brentozar.com/blitzcache/busy-loops/',
                'Operations have been found that are executed 100 times more often than the number of rows returned by each iteration. This is an indicator that something is off in query execution.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  tvf_join = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                17,
                50,
                'Performance',
                'Joining to table valued functions',
                'http://brentozar.com/blitzcache/tvf-join/',
                'Execution plans have been found that join to table valued functions (TVFs). TVFs produce inaccurate estimates of the number of rows returned and can lead to any number of query plan problems.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  compile_timeout = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                18,
                50,
                'Execution Plans',
                'Compilation timeout',
                'http://brentozar.com/blitzcache/compilation-timeout/',
                'Query compilation timed out for one or more queries. SQL Server did not find a plan that meets acceptable performance criteria in the time allotted so the best guess was returned. There is a very good chance that this plan isn''t even below average - it''s probably terrible.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  compile_memory_limit_exceeded = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                19,
                50,
                'Execution Plans',
                'Compilation memory limit exceeded',
                'http://brentozar.com/blitzcache/compile-memory-limit-exceeded/',
                'The optimizer has a limited amount of memory available. One or more queries are complex enough that SQL Server was unable to allocate enough memory to fully optimize the query. A best fit plan was found, and it''s probably terrible.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  warning_no_join_predicate = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                20,
                10,
                'Execution Plans',
                'No join predicate',
                'http://brentozar.com/blitzcache/no-join-predicate/',
                'Operators in a query have no join predicate. This means that all rows from one table will be matched with all rows from anther table producing a Cartesian product. That''s a whole lot of rows. This may be your goal, but it''s important to investigate why this is happening.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  plan_multiple_plans = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                21,
                200,
                'Execution Plans',
                'Multiple execution plans',
                'http://brentozar.com/blitzcache/multiple-plans/',
                'Queries exist with multiple execution plans (as determined by query_plan_hash). Investigate possible ways to parameterize these queries or otherwise reduce the plan count.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  unmatched_index_count > 0
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                22,
                100,
                'Performance',
                'Unmatched indexes',
                'http://brentozar.com/blitzcache/unmatched-indexes',
                'An index could have been used, but SQL Server chose not to use it - likely due to parameterization and filtered indexes.');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  unparameterized_query = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                23,
                100,
                'Parameterization',
                'Unparameterized queries',
                'http://brentozar.com/blitzcache/unparameterized-queries',
                'Unparameterized queries found. These could be ad hoc queries, data exploration, or queries using "OPTIMIZE FOR UNKNOWN".');

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings
                   WHERE  is_trivial = 1
				   )
        INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
        VALUES (
                24,
                100,
                'Execution Plans',
                'Trivial Plans',
                'http://brentozar.com/blitzcache/trivial-plans',
                'Trivial plans get almost no optimization. If you''re finding these in the top worst queries, something may be going wrong.');
    
        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_forced_serial= 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    25,
                    10,
                    'Execution Plans',
                    'Forced Serialization',
                    'http://www.brentozar.com/blitzcache/forced-serialization/',
                    'Something in your plan is forcing a serial query. Further investigation is needed if this is not by design.') ;	

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_key_lookup_expensive= 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    26,
                    100,
                    'Execution Plans',
                    'Expensive Key Lookups',
                    'http://www.brentozar.com/blitzcache/expensive-key-lookups/',
                    'There''s a key lookup in your plan that costs >=50% of the total plan cost.') ;	

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_remote_query_expensive= 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    28,
                    100,
                    'Execution Plans',
                    'Expensive Remote Query',
                    'http://www.brentozar.com/blitzcache/expensive-remote-query/',
                    'There''s a remote query in your plan that costs >=50% of the total plan cost.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.trace_flags_session IS NOT NULL
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    29,
                    100,
                    'Trace Flags',
                    'Session Level Trace Flags Enabled',
                    'https://www.brentozar.com/blitz/trace-flags-enabled-globally/',
                    'Someone is enabling session level Trace Flags in a query.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_unused_grant IS NOT NULL
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    30,
                    100,
                    'Unused memory grants',
                    'Queries are asking for more memory than they''re using',
                    'https://www.brentozar.com/blitzcache/unused-memory-grants/',
                    'Queries have large unused memory grants. This can cause concurrency issues, if queries are waiting a long time to get memory to run.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.function_count > 0
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    31,
                    100,
                    'Compute Scalar That References A Function',
                    'This could be trouble if you''re using Scalar Functions or MSTVFs',
                    'https://www.brentozar.com/blitzcache/compute-scalar-functions/',
                    'Both of these will force queries to run serially, run at least once per row, and may result in poor cardinality estimates.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.clr_function_count > 0
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    32,
                    100,
                    'Compute Scalar That References A CLR Function',
                    'This could be trouble if your CLR functions perform data access',
                    'https://www.brentozar.com/blitzcache/compute-scalar-functions/',
                    'May force queries to run serially, run at least once per row, and may result in poor cardinlity estimates.') ;


        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_table_variable = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    33,
                    100,
                    'Table Variables detected',
                    'Beware nasty side effects',
                    'https://www.brentozar.com/blitzcache/table-variables/',
                    'All modifications are single threaded, and selects have really low row estimates.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.no_stats_warning = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    35,
                    100,
                    'Columns with no statistics',
                    'Poor cardinality estimates may ensue',
                    'https://www.brentozar.com/blitzcache/columns-no-statistics/',
                    'Sometimes this happens with indexed views, other times because auto create stats is turned off.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.relop_warnings = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    36,
                    100,
                    'Operator Warnings',
                    'SQL is throwing operator level plan warnings',
                    'http://brentozar.com/blitzcache/query-plan-warnings/',
                    'Check the plan for more details.') ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_table_scan = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    37,
                    100,
                    'Table Scans',
                    'Your database has HEAPs',
                    'https://www.brentozar.com/archive/2012/05/video-heaps/',
                    'This may not be a problem. Run sp_BlitzIndex for more information.') ;
        
		IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.backwards_scan = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    38,
                    100,
                    'Backwards Scans',
                    'Indexes are being read backwards',
                    'https://www.brentozar.com/blitzcache/backwards-scans/',
                    'This isn''t always a problem. They can cause serial zones in plans, and may need an index to match sort order.') ;

		IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.forced_index = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    39,
                    100,
                    'Index forcing',
                    'Someone is using hints to force index usage',
                    'https://www.brentozar.com/blitzcache/optimizer-forcing/',
                    'This can cause inefficient plans, and will prevent missing index requests.') ;

		IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.forced_seek = 1
				   OR p.forced_scan = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    40,
                    100,
                    'Seek/Scan forcing',
                    'Someone is using hints to force index seeks/scans',
                    'https://www.brentozar.com/blitzcache/optimizer-forcing/',
                    'This can cause inefficient plans by taking seek vs scan choice away from the optimizer.') ;

		IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.columnstore_row_mode = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    41,
                    100,
                    'ColumnStore indexes operating in Row Mode',
                    'Batch Mode is optimal for ColumnStore indexes',
                    'https://www.brentozar.com/blitzcache/columnstore-indexes-operating-row-mode/',
                    'ColumnStore indexes operating in Row Mode indicate really poor query choices.') ;

		IF EXISTS (SELECT 1/0
                   FROM   #working_warnings p
                   WHERE  p.is_computed_scalar = 1
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    42,
                    50,
                    'Computed Columns Referencing Scalar UDFs',
                    'This makes a whole lot of stuff run serially',
                    'https://www.brentozar.com/blitzcache/computed-columns-referencing-functions/',
                    'This can cause a whole mess of bad serializartion problems.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_sort_expensive = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     43,
                     100,
                     'Execution Plans',
                     'Expensive Sort',
                     'http://www.brentozar.com/blitzcache/expensive-sorts/',
                     'There''s a sort in your plan that costs >=50% of the total plan cost.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_computed_filter = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     44,
                     50,
                     'Filters Referencing Scalar UDFs',
                     'This forces serialization',
                     'https://www.brentozar.com/blitzcache/compute-scalar-functions/',
                     'Someone put a Scalar UDF in the WHERE clause!') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.index_ops >= 5
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     45,
                     100,
                     'Many Indexes Modified',
                     'Write Queries Are Hitting >= 5 Indexes',
                     'https://www.brentozar.com/blitzcache/many-indexes-modified/',
                     'This can cause lots of hidden I/O -- Run sp_BlitzIndex for more information.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_row_level = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     46,
                     100,
                     'Plan Confusion',
                     'Row Level Security is in use',
                     'https://www.brentozar.com/blitzcache/row-level-security/',
                     'You may see a lot of confusing junk in your query plan.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_spatial = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     47,
                     200,
                     'Spatial Abuse',
                     'You hit a Spatial Index',
                     'https://www.brentozar.com/blitzcache/spatial-indexes/',
                     'Purely informational.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.index_dml = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     48,
                     150,
                     'Index DML',
                     'Indexes were created or dropped',
                     'https://www.brentozar.com/blitzcache/index-dml/',
                     'This can cause recompiles and stuff.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.table_dml = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     49,
                     150,
                     'Table DML',
                     'Tables were created or dropped',
                     'https://www.brentozar.com/blitzcache/table-dml/',
                     'This can cause recompiles and stuff.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.long_running_low_cpu = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     50,
                     150,
                     'Long Running Low CPU',
                     'You have a query that runs for much longer than it uses CPU',
                     'https://www.brentozar.com/blitzcache/long-running-low-cpu/',
                     'This can be a sign of blocking, linked servers, or poor client application code (ASYNC_NETWORK_IO).') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.low_cost_high_cpu = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     51,
                     150,
                     'Low Cost Query With High CPU',
                     'You have a low cost query that uses a lot of CPU',
                     'https://www.brentozar.com/blitzcache/low-cost-high-cpu/',
                     'This can be a sign of functions or Dynamic SQL that calls black-box code.') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.stale_stats = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     52,
                     150,
                     'Biblical Statistics',
                     'Statistics used in queries are >7 days old with >100k modifications',
                     'https://www.brentozar.com/blitzcache/stale-statistics/',
                     'Ever heard of updating statistics?') ;

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_adaptive = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     53,
                     150,
                     'Adaptive joins',
                     'This is pretty cool -- you''re living in the future.',
                     'https://www.brentozar.com/blitzcache/adaptive-joins/',
                     'Joe Sack rules.') ;					 

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_spool_expensive = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     54,
                     150,
                     'Expensive Index Spool',
                     'You have an index spool, this is usually a sign that there''s an index missing somewhere.',
                     'https://www.brentozar.com/blitzcache/eager-index-spools/',
                     'Check operator predicates and output for index definition guidance') ;	

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_spool_more_rows = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     55,
                     150,
                     'Index Spools Many Rows',
                     'You have an index spool that spools more rows than the query returns',
                     'https://www.brentozar.com/blitzcache/eager-index-spools/',
                     'Check operator predicates and output for index definition guidance') ;	

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_bad_estimate = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     56,
                     100,
                     'Potentially bad cardinality estimates',
                     'Estimated rows are different from average rows by a factor of 10000',
                     'https://www.brentozar.com/blitzcache/bad-estimates/',
                     'This may indicate a performance problem if mismatches occur regularly') ;					

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_big_log = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     57,
                     100,
                     'High transaction log use',
                     'This query on average uses more than half of the transaction log',
                     'http://michaeljswart.com/2014/09/take-care-when-scripting-batches/',
                     'This is probably a sign that you need to start batching queries') ;
					 		
        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_big_tempdb = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     58,
                     100,
                     'High tempdb use',
                     'This query uses more than half of a data file on average',
                     'No URL yet',
                     'You should take a look at tempdb waits to see if you''re having problems') ;
					 
        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_row_goal = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (59,
                     200,
                     'Row Goals',
                     'This query had row goals introduced',
                     'https://www.brentozar.com/archive/2018/01/sql-server-2017-cu3-adds-optimizer-row-goal-information-query-plans/',
                     'This can be good or bad, and should be investigated for high read queries') ;						 	

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_mstvf = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     60,
                     100,
                     'MSTVFs',
                     'These have many of the same problems scalar UDFs have',
                     'http://brentozar.com/blitzcache/tvf-join/',
					 'Execution plans have been found that join to table valued functions (TVFs). TVFs produce inaccurate estimates of the number of rows returned and can lead to any number of query plan problems.');

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_mstvf = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (61,
                     100,
                     'Many to Many Merge',
                     'These use secret worktables that could be doing lots of reads',
                     'https://www.brentozar.com/archive/2018/04/many-mysteries-merge-joins/',
					 'Occurs when join inputs aren''t known to be unique. Can be really bad when parallel.');

        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_nonsargable = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (62,
                     50,
                     'Non-SARGable queries',
                     'Queries may be using',
                     'http://brentozar.com/go/sargable',
					 'Occurs when join inputs aren''t known to be unique. Can be really bad when parallel.');
					
        IF EXISTS (SELECT 1/0
                    FROM   #working_warnings p
                    WHERE  p.is_paul_white_electric = 1
  					)
             INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
             VALUES (
                     998,
                     200,
                     'Is Paul White Electric?',
                     'This query has a Switch operator in it!',
                     'http://sqlblog.com/blogs/paul_white/archive/2013/06/11/hello-operator-my-switch-is-bored.aspx',
                     'You should email this query plan to Paul: SQLkiwi at gmail dot com') ;
					 
					 						 					
				
				INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
				SELECT 				
				999,
				200,
				'Database Level Statistics',
				'The database ' + sa.[database] + ' last had a stats update on '  + CONVERT(NVARCHAR(10), CONVERT(DATE, MAX(sa.last_update))) + ' and has ' + CONVERT(NVARCHAR(10), AVG(sa.modification_count)) + ' modifications on average.' AS Finding,
				'https://www.brentozar.com/blitzcache/stale-statistics/' AS URL,
				'Consider updating statistics more frequently,' AS Details
				FROM #stats_agg AS sa
				GROUP BY sa.[database]
				HAVING MAX(sa.last_update) <= DATEADD(DAY, -7, SYSDATETIME())
				AND AVG(sa.modification_count) >= 100000;

		
        IF EXISTS (SELECT 1/0
                   FROM   #trace_flags AS tf 
                   WHERE  tf.global_trace_flags IS NOT NULL
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    1000,
                    255,
                    'Global Trace Flags Enabled',
                    'You have Global Trace Flags enabled on your server',
                    'https://www.brentozar.com/blitz/trace-flags-enabled-globally/',
                    'You have the following Global Trace Flags enabled: ' + (SELECT TOP 1 tf.global_trace_flags FROM #trace_flags AS tf WHERE tf.global_trace_flags IS NOT NULL)) ;

        IF EXISTS (SELECT 1/0
                   FROM   #working_plan_text AS p
                   WHERE  p.min_grant_kb IS NULL
				   )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    1001,
                    255,
                    'Plans not in cache',
                    'We checked sys.dm_exec_query_stats for memory grant info',
                    '',
                    'Plans in Query Store aren''t in other DMVs, which means we can''t get some information about them.') ;

			/*
			Return worsts
			*/
			WITH worsts AS (
			SELECT gi.flat_date,
			       gi.start_range,
			       gi.end_range,
			       gi.total_avg_duration_ms,
			       gi.total_avg_cpu_time_ms,
			       gi.total_avg_logical_io_reads_mb,
			       gi.total_avg_physical_io_reads_mb,
			       gi.total_avg_logical_io_writes_mb,
			       gi.total_avg_query_max_used_memory_mb,
			       gi.total_rowcount,
				   gi.total_avg_log_bytes_mb,
				   gi.total_avg_tempdb_space,
				   CONVERT(NVARCHAR(20), gi.flat_date) AS worst_date,
				   CASE WHEN DATEPART(HOUR, gi.start_range) = 0 THEN ' midnight '
						WHEN DATEPART(HOUR, gi.start_range) <= 12 THEN CONVERT(NVARCHAR(3), DATEPART(HOUR, gi.start_range)) + 'am '
						WHEN DATEPART(HOUR, gi.start_range) > 12 THEN CONVERT(NVARCHAR(3), DATEPART(HOUR, gi.start_range) -12) + 'pm '
						END AS worst_start_time,
				   CASE WHEN DATEPART(HOUR, gi.end_range) = 0 THEN ' midnight '
						WHEN DATEPART(HOUR, gi.end_range) <= 12 THEN CONVERT(NVARCHAR(3), DATEPART(HOUR, gi.end_range)) + 'am '
						WHEN DATEPART(HOUR, gi.end_range) > 12 THEN CONVERT(NVARCHAR(3),  DATEPART(HOUR, gi.end_range) -12) + 'pm '
						END AS worst_end_time
			FROM   #grouped_interval AS gi
			), 
				duration_worst AS (
			SELECT TOP 1 'Your worst duration range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_duration_ms DESC
				), 
				cpu_worst AS (
			SELECT TOP 1 'Your worst cpu range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_cpu_time_ms DESC
				), 
				logical_reads_worst AS (
			SELECT TOP 1 'Your worst logical read range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_logical_io_reads_mb DESC
				), 
				physical_reads_worst AS (
			SELECT TOP 1 'Your worst physical read range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_physical_io_reads_mb DESC
				), 
				logical_writes_worst AS (
			SELECT TOP 1 'Your worst logical write range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_logical_io_writes_mb DESC
				), 
				memory_worst AS (
			SELECT TOP 1 'Your worst memory range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_query_max_used_memory_mb DESC
				), 
				rowcount_worst AS (
			SELECT TOP 1 'Your worst row count range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_rowcount DESC
				), 
				logbytes_worst AS (
			SELECT TOP 1 'Your worst log bytes range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_log_bytes_mb DESC
				), 
				tempdb_worst AS (
			SELECT TOP 1 'Your worst tempdb range was on ' + worsts.worst_date + ' between ' + worsts.worst_start_time + ' and ' + worsts.worst_end_time + '.' AS msg
			FROM worsts
			ORDER BY worsts.total_avg_tempdb_space DESC
				)
			INSERT #warning_results ( CheckID, Priority, FindingsGroup, Finding, URL, Details )
			SELECT 1002, 255, 'Worsts', 'Worst Duration', 'N/A', duration_worst.msg
			FROM duration_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst CPU', 'N/A', cpu_worst.msg
			FROM cpu_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst Logical Reads', 'N/A', logical_reads_worst.msg
			FROM logical_reads_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst Physical Reads', 'N/A', physical_reads_worst.msg
			FROM physical_reads_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst Logical Writes', 'N/A', logical_writes_worst.msg
			FROM logical_writes_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst Memory', 'N/A', memory_worst.msg
			FROM memory_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst Row Counts', 'N/A', rowcount_worst.msg
			FROM rowcount_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst Log Bytes', 'N/A', logbytes_worst.msg
			FROM logbytes_worst
			UNION ALL
			SELECT 1002, 255, 'Worsts', 'Worst tempdb', 'N/A', tempdb_worst.msg
			FROM tempdb_worst
			OPTION (RECOMPILE);


        IF NOT EXISTS (SELECT 1/0
					   FROM   #warning_results AS bcr
                       WHERE  bcr.Priority = 2147483646
				      )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (2147483646,
                    255,
                    'Need more help?' ,
                    'Paste your plan on the internet!',
                    'http://pastetheplan.com',
                    'This makes it easy to share plans and post them to Q&A sites like https://dba.stackexchange.com/!') ;


        IF NOT EXISTS (SELECT 1/0
					   FROM   #warning_results AS bcr
                       WHERE  bcr.Priority = 2147483647
				      )
            INSERT INTO #warning_results (CheckID, Priority, FindingsGroup, Finding, URL, Details)
            VALUES (
                    2147483647,
                    255,
                    'Thanks for using sp_BlitzQueryStore!' ,
                    'From Your Community Volunteers',
                    'http://FirstResponderKit.org',
                    'We hope you found this tool useful. Current version: ' + @Version + ' released on ' + CONVERT(NVARCHAR(30), @VersionDate) + '.') ;
	

	
    SELECT  Priority,
            FindingsGroup,
            Finding,
            URL,
            Details,
            CheckID
    FROM    #warning_results 
    GROUP BY Priority,
            FindingsGroup,
            Finding,
            URL,
            Details,
            CheckID
    ORDER BY Priority ASC, FindingsGroup, Finding, CheckID ASC
    OPTION (RECOMPILE);



END;	

END;
END TRY
BEGIN CATCH
        RAISERROR (N'Failure returning warnings', 0,1) WITH NOWAIT;

        IF @sql_select IS NOT NULL
        BEGIN
            SET @msg = N'Last @sql_select: ' + @sql_select;
            RAISERROR(@msg, 0, 1) WITH NOWAIT;
        END;

        SELECT    @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
        RAISERROR (@msg, @error_severity, @error_state) WITH NOWAIT;
        
        
        WHILE @@TRANCOUNT > 0 
            ROLLBACK;

        RETURN;
END CATCH;

IF @Debug = 1	

BEGIN TRY 

BEGIN

RAISERROR(N'Returning debugging data from temp tables', 0, 1) WITH NOWAIT;

--Table content debugging

SELECT '#working_metrics' AS table_name, *
FROM #working_metrics AS wm
OPTION (RECOMPILE);

SELECT '#working_plan_text' AS table_name, *
FROM #working_plan_text AS wpt
OPTION (RECOMPILE);

SELECT '#working_warnings' AS table_name, *
FROM #working_warnings AS ww
OPTION (RECOMPILE);

SELECT '#working_wait_stats' AS table_name, *
FROM #working_wait_stats wws
OPTION (RECOMPILE);

SELECT '#grouped_interval' AS table_name, *
FROM #grouped_interval
OPTION (RECOMPILE);

SELECT '#working_plans' AS table_name, *
FROM #working_plans
OPTION (RECOMPILE);

SELECT '#stats_agg' AS table_name, *
FROM #stats_agg
OPTION (RECOMPILE);

SELECT '#trace_flags' AS table_name, *
FROM #trace_flags
OPTION (RECOMPILE);

SELECT '#statements' AS table_name, *
FROM #statements AS s
OPTION (RECOMPILE);

SELECT '#query_plan' AS table_name, *
FROM #query_plan AS qp
OPTION (RECOMPILE);

SELECT '#relop' AS table_name, *
FROM #relop AS r
OPTION (RECOMPILE);

SELECT '#plan_cost' AS table_name,  * 
FROM #plan_cost AS pc
OPTION (RECOMPILE);

SELECT '#est_rows' AS table_name,  * 
FROM #est_rows AS er
OPTION (RECOMPILE);

SELECT '#stored_proc_info' AS table_name, *
FROM #stored_proc_info AS spi
OPTION(RECOMPILE);

SELECT '#conversion_info' AS table_name, *
FROM #conversion_info AS ci
OPTION ( RECOMPILE );

SELECT '#variable_info' AS table_name, *
FROM #variable_info AS vi
OPTION ( RECOMPILE );

SELECT '#missing_index_xml' AS table_name, *
FROM   #missing_index_xml
OPTION ( RECOMPILE );

SELECT '#missing_index_schema' AS table_name, *
FROM   #missing_index_schema
OPTION ( RECOMPILE );

SELECT '#missing_index_usage' AS table_name, *
FROM   #missing_index_usage
OPTION ( RECOMPILE );

SELECT '#missing_index_detail' AS table_name, *
FROM   #missing_index_detail
OPTION ( RECOMPILE );

SELECT '#missing_index_pretty' AS table_name, *
FROM   #missing_index_pretty
OPTION ( RECOMPILE );

END; 

END TRY
BEGIN CATCH
        RAISERROR (N'Failure returning debug temp tables', 0,1) WITH NOWAIT;

        IF @sql_select IS NOT NULL
        BEGIN
            SET @msg = N'Last @sql_select: ' + @sql_select;
            RAISERROR(@msg, 0, 1) WITH NOWAIT;
        END;

        SELECT    @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
        RAISERROR (@msg, @error_severity, @error_state) WITH NOWAIT;
        
        
        WHILE @@TRANCOUNT > 0 
            ROLLBACK;

        RETURN;
END CATCH;

/*
Ways to run this thing

--Debug
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Debug = 1

--Get the top 1
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @Debug = 1

--Use a StartDate												 
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @StartDate = '20170527'
				
--Use an EndDate												 
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @EndDate = '20170527'
				
--Use Both												 
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @StartDate = '20170526', @EndDate = '20170527'

--Set a minimum execution count												 
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @MinimumExecutionCount = 10

--Set a duration minimum
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @DurationFilter = 5

--Look for a stored procedure name (that doesn't exist!)
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @StoredProcName = 'blah'

--Look for a stored procedure name that does (at least On My Computer®)
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @StoredProcName = 'UserReportExtended'

--Look for failed queries
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @Top = 1, @Failed = 1

--Filter by plan_id
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @PlanIdFilter = 3356

--Filter by query_id
EXEC sp_BlitzQueryStore @DatabaseName = 'StackOverflow', @QueryIdFilter = 2958

*/

END;

GO