sp_QuickieStore - Erik Darling.sql

-- sp_QuickieStore - Erik Darling
-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox
-- This script creates Erik Darling's sp_QuickieStore stored procedure that "digs into Query Store data to find queries to tune".
-- From https://github.com/erikdarlingdata/DarlingData/blob/main/sp_QuickieStore/sp_QuickieStore.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 IMPLICIT_TRANSACTIONS OFF;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

/*

 ██████╗ ██╗   ██╗██╗ ██████╗██╗  ██╗██╗███████╗
██╔═══██╗██║   ██║██║██╔════╝██║ ██╔╝██║██╔════╝
██║   ██║██║   ██║██║██║     █████╔╝ ██║█████╗
██║▄▄ ██║██║   ██║██║██║     ██╔═██╗ ██║██╔══╝
╚██████╔╝╚██████╔╝██║╚██████╗██║  ██╗██║███████╗
 ╚══▀▀═╝  ╚═════╝ ╚═╝ ╚═════╝╚═╝  ╚═╝╚═╝╚══════╝
                      
███████╗████████╗ ██████╗ ██████╗ ███████╗██╗
██╔════╝╚══██╔══╝██╔═══██╗██╔══██╗██╔════╝██║
███████╗   ██║   ██║   ██║██████╔╝█████╗  ██║
╚════██║   ██║   ██║   ██║██╔══██╗██╔══╝  ╚═╝
███████║   ██║   ╚██████╔╝██║  ██║███████╗██╗
╚══════╝   ╚═╝    ╚═════╝ ╚═╝  ╚═╝╚══════╝╚═╝

Copyright 2021 Darling Data, LLC
https://www.erikdarlingdata.com/

For usage and licensing details, run:
EXEC sp_QuickieStore
    @help = 1;

For working through errors:
EXEC sp_QuickieStore
    @debug = 1;

For performance issues:
EXEC sp_QuickieStore
    @troubleshoot_performance = 1;

For support, head over to GitHub:
https://github.com/erikdarlingdata/DarlingData

*/

CREATE OR ALTER PROCEDURE dbo.sp_QuickieStore
(
    @database_name sysname = NULL,
    @sort_order VARCHAR(20) = 'cpu',
    @top BIGINT = 10,
    @start_date DATETIME = NULL,
    @end_date DATETIME = NULL,
    @execution_count BIGINT = NULL,
    @duration_ms BIGINT = NULL,
    @procedure_schema sysname = NULL,
    @procedure_name sysname = NULL,
    @include_plan_ids NVARCHAR(4000) = NULL,
    @include_query_ids NVARCHAR(4000) = NULL,
    @ignore_plan_ids NVARCHAR(4000) = NULL,
    @ignore_query_ids NVARCHAR(4000) = NULL,
    @query_text_search NVARCHAR(4000) = NULL,
    @wait_filter VARCHAR(20) = NULL,
    @expert_mode BIT = 0,
    @format_output BIT = 0,
    @version VARCHAR(30) = NULL OUTPUT,
    @version_date DATETIME = NULL OUTPUT,
    @help BIT = 0,
    @debug BIT = 0,
    @troubleshoot_performance BIT = 0
)
WITH RECOMPILE
AS
BEGIN

    SET STATISTICS XML OFF;
    SET NOCOUNT, XACT_ABORT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    BEGIN TRY

        /*
If this column doesn't exist, you're not on a good version of SQL Server
*/
        IF NOT EXISTS
        (
            SELECT 1 / 0
            FROM sys.all_columns AS ac
            WHERE ac.object_id = OBJECT_ID(N'sys.dm_exec_query_stats', N'V')
                  AND ac.name = N'total_spills'
        )
        BEGIN
            RAISERROR(
                         'This procedure only runs on supported versions of SQL Server:
* 2016 SP2+
* 2017 CU3+
* 2019+
* Probably Azure?',
                         11,
                         1
                     ) WITH NOWAIT;

            RETURN;
        END;

        /*
These are for your outputs.
*/
        SELECT @version = '1.92',
               @version_date = '20211101';

        /*
Helpful section! For help.
*/
        IF @help = 1
        BEGIN

            /*
    Introduction
    */
            SELECT introduction = 'hi, i''m sp_QuickieStore!'
            UNION ALL
            SELECT 'you got me from https://www.erikdarlingdata.com/sp_quickiestore/'
            UNION ALL
            SELECT 'i can be used to quickly grab misbehaving queries from query store'
            UNION ALL
            SELECT 'the plan analysis is up to you; there will not be any XML shredding here'
            UNION ALL
            SELECT 'so what can you do, and how do you do it? read below!';

            /*
    Parameters
    */
            SELECT parameter_name = ap.name,
                   data_type = t.name,
                   description = CASE ap.name
                                     WHEN '@database_name' THEN
                                         'the name of the database you want to look at query store in'
                                     WHEN '@sort_order' THEN
                                         'the runtime metric you want to prioritize results by'
                                     WHEN '@top' THEN
                                         'the number of queries you want to pull back'
                                     WHEN '@start_date' THEN
                                         'the begin date of your search'
                                     WHEN '@end_date' THEN
                                         'the end date of your search'
                                     WHEN '@execution_count' THEN
                                         'the minimum number of executions a query must have'
                                     WHEN '@duration_ms' THEN
                                         'the minimum duration a query must have'
                                     WHEN '@procedure_schema' THEN
                                         'the schema of the procedure you''re searching for'
                                     WHEN '@procedure_name' THEN
                                         'the name of the programmable object you''re searching for'
                                     WHEN '@include_plan_ids' THEN
                                         'a list of plan ids to search for'
                                     WHEN '@include_query_ids' THEN
                                         'a list of query ids to search for'
                                     WHEN '@ignore_plan_ids' THEN
                                         'a list of plan ids to ignore'
                                     WHEN '@ignore_query_ids' THEN
                                         'a list of query ids to ignore'
                                     WHEN '@query_text_search' THEN
                                         'query text to search for'
                                     WHEN '@wait_filter' THEN
                                         'wait category to search for; category details are below'
                                     WHEN '@expert_mode' THEN
                                         'returns additional columns and results'
                                     WHEN '@format_output' THEN
                                         'returns numbers formatted with commas'
                                     WHEN '@version' THEN
                                         'OUTPUT; for support'
                                     WHEN '@version_date' THEN
                                         'OUTPUT; for support'
                                     WHEN '@help' THEN
                                         'how you got here'
                                     WHEN '@debug' THEN
                                         'prints dynamic sql (including statement length), parameter and variable values, and raw temp table contents'
                                     WHEN '@troubleshoot_performance' THEN
                                         'set statistics xml on for queries against views'
                                 END,
                   valid_inputs = CASE ap.name
                                      WHEN '@database_name' THEN
                                          'a database name with query store enabled'
                                      WHEN '@sort_order' THEN
                                          'cpu, logical reads, physical reads, writes, duration, memory, tempdb, executions'
                                      WHEN '@top' THEN
                                          'a positive integer between 1 and 9,223,372,036,854,775,807'
                                      WHEN '@start_date' THEN
                                          'January 1, 1753, through December 31, 9999'
                                      WHEN '@end_date' THEN
                                          'January 1, 1753, through December 31, 9999'
                                      WHEN '@execution_count' THEN
                                          'a positive integer between 1 and 9,223,372,036,854,775,807'
                                      WHEN '@duration_ms' THEN
                                          'a positive integer between 1 and 9,223,372,036,854,775,807'
                                      WHEN '@procedure_schema' THEN
                                          'a valid schema in your database'
                                      WHEN '@procedure_name' THEN
                                          'a valid programmable object in your database'
                                      WHEN '@include_plan_ids' THEN
                                          'a string; comma separated for multiple ids'
                                      WHEN '@include_query_ids' THEN
                                          'a string; comma separated for multiple ids'
                                      WHEN '@ignore_plan_ids' THEN
                                          'a string; comma separated for multiple ids'
                                      WHEN '@ignore_query_ids' THEN
                                          'a string; comma separated for multiple ids'
                                      WHEN '@query_text_search' THEN
                                          'a string; leading and trailing wildcards will be added if missing'
                                      WHEN '@wait_filter' THEN
                                          'cpu, lock, latch, buffer latch, buffer io, log io, network io, parallelism, memory'
                                      WHEN '@expert_mode' THEN
                                          '0 or 1'
                                      WHEN '@format_output' THEN
                                          '0 or 1'
                                      WHEN '@version' THEN
                                          'none'
                                      WHEN '@version_date' THEN
                                          'none'
                                      WHEN '@help' THEN
                                          '0 or 1'
                                      WHEN '@debug' THEN
                                          '0 or 1'
                                      WHEN '@troubleshoot_performance' THEN
                                          '0 or 1'
                                  END,
                   defaults = CASE ap.name
                                  WHEN '@database_name' THEN
                                      'NULL'
                                  WHEN '@sort_order' THEN
                                      'cpu'
                                  WHEN '@top' THEN
                                      '10'
                                  WHEN '@start_date' THEN
                                      'the last 24 hours'
                                  WHEN '@end_date' THEN
                                      'NULL'
                                  WHEN '@execution_count' THEN
                                      'NULL'
                                  WHEN '@duration_ms' THEN
                                      'NULL'
                                  WHEN '@procedure_schema' THEN
                                      'NULL; dbo if NULL and procedure name is not NULL'
                                  WHEN '@procedure_name' THEN
                                      'NULL'
                                  WHEN '@include_plan_ids' THEN
                                      'NULL'
                                  WHEN '@include_query_ids' THEN
                                      'NULL'
                                  WHEN '@ignore_plan_ids' THEN
                                      'NULL'
                                  WHEN '@ignore_query_ids' THEN
                                      'NULL'
                                  WHEN '@query_text_search' THEN
                                      'NULL'
                                  WHEN '@wait_filter' THEN
                                      'NULL'
                                  WHEN '@expert_mode' THEN
                                      '0'
                                  WHEN '@format_output' THEN
                                      '0'
                                  WHEN '@version' THEN
                                      'none'
                                  WHEN '@version_date' THEN
                                      'none'
                                  WHEN '@help' THEN
                                      '0'
                                  WHEN '@debug' THEN
                                      '0'
                                  WHEN '@troubleshoot_performance' THEN
                                      '0'
                              END
            FROM sys.all_parameters AS ap
                INNER JOIN sys.all_objects AS o
                    ON ap.object_id = o.object_id
                INNER JOIN sys.types AS t
                    ON ap.system_type_id = t.system_type_id
                       AND ap.user_type_id = t.user_type_id
            WHERE o.name = N'sp_QuickieStore'
            OPTION (RECOMPILE);

            /*
    Wait categories
    */

            SELECT wait_categories = 'cpu (1): SOS_SCHEDULER_YIELD'
            UNION ALL
            SELECT 'lock (3): LCK_M_%'
            UNION ALL
            SELECT 'latch (4): LATCH_%'
            UNION ALL
            SELECT 'buffer latch (5): PAGELATCH_%'
            UNION ALL
            SELECT 'buffer io (6): PAGEIOLATCH_%'
            UNION ALL
            SELECT 'log io (14): LOGMGR, LOGBUFFER, LOGMGR_RESERVE_APPEND, LOGMGR_FLUSH, LOGMGR_PMM_LOG, CHKPT, WRITELOG'
            UNION ALL
            SELECT 'network io (15): ASYNC_NETWORK_IO, NET_WAITFOR_PACKET, PROXY_NETWORK_IO, EXTERNAL_SCRIPT_NETWORK_IOF'
            UNION ALL
            SELECT 'parallelism (16): CXPACKET, EXCHANGE, HT%, BMP%, BP%'
            UNION ALL
            SELECT 'memory (17): RESOURCE_SEMAPHORE, CMEMTHREAD, CMEMPARTITIONED, EE_PMOLOCK, MEMORY_ALLOCATION_EXT, RESERVED_MEMORY_ALLOCATION_EXT, MEMORY_GRANT_UPDATE';

            /*
    Results
    */
            SELECT results = 'results returned at the end of the procedure:'
            UNION ALL
            SELECT REPLICATE('-', 100)
            UNION ALL
            SELECT 'Runtime Stats: data from query_store_runtime_stats, along with query plan, query text, wait stats (2017+, when enabled), and parent object'
            UNION ALL
            SELECT REPLICATE('-', 100)
            UNION ALL
            SELECT 'Compilation Stats (expert mode only): data from query_store_query about compilation metrics'
            UNION ALL
            SELECT REPLICATE('-', 100)
            UNION ALL
            SELECT 'Resource Stats (expert mode only): data from dm_exec_query_stats, when available'
            UNION ALL
            SELECT 'query store does not currently track some details about memory grants and thread usage'
            UNION ALL
            SELECT 'so i go back to a plan cache view to try to track it down'
            UNION ALL
            SELECT REPLICATE('-', 100)
            UNION ALL
            SELECT 'Query Store Waits By Query(expert mode only): information about query duration and logged wait stats'
            UNION ALL
            SELECT 'it can sometimes be useful to compare query duration to query wait times'
            UNION ALL
            SELECT REPLICATE('-', 100)
            UNION ALL
            SELECT 'Query Store Waits Total(expert mode only): total wait stats for the chosen date range only'
            UNION ALL
            SELECT REPLICATE('-', 100)
            UNION ALL
            SELECT 'Query Store Options (expert mode only): details about current query store configuration';

            /*
    Limitations
    */
            SELECT limitations = 'frigid shortcomings:'
            UNION ALL
            SELECT 'you need to be on at least SQL Server 2016 SP2, 2017 CU3, or any higher version to run this'
            UNION ALL
            SELECT 'if you''re on azure sql db then you''ll need to be in compat level 130'
            UNION ALL
            SELECT 'i do not currently support synapse or edge or other memes';

            /*
    License to F5
    */
            SELECT mit_license_yo = 'i am MIT licensed, so like, do whatever'
            UNION ALL
            SELECT 'see printed messages for full license';

            RAISERROR(
                         '
MIT License

Copyright 2021 Darling Data, LLC

https://www.erikdarlingdata.com/

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.
',
                         0,
                         1
                     )    WITH NOWAIT;

            RETURN;

        END;

        /*
These are the tables that we'll use to grab data from query store
It will be fun
You'll love it
*/

        /*
Plans we'll be working on
*/
        CREATE TABLE #distinct_plans
        (
            plan_id BIGINT PRIMARY KEY
        );

        /*
Hold plan_ids for procedures we're searching
*/
        CREATE TABLE #procedure_plans
        (
            plan_id BIGINT PRIMARY KEY
        );

        /*
Hold plan_ids for plans we want
*/
        CREATE TABLE #include_plan_ids
        (
            plan_id BIGINT PRIMARY KEY
        );

        /*
Hold query_ids for plans we want
*/
        CREATE TABLE #include_query_ids
        (
            query_id BIGINT PRIMARY KEY
        );

        /*
Hold plan_ids for ignored plans
*/
        CREATE TABLE #ignore_plan_ids
        (
            plan_id BIGINT PRIMARY KEY
        );

        /*
Hold query_ids for ignored plans
*/
        CREATE TABLE #ignore_query_ids
        (
            query_id BIGINT PRIMARY KEY
        );

        /*
Hold plan_ids for matching query text
*/
        CREATE TABLE #query_text_search
        (
            plan_id BIGINT PRIMARY KEY
        );

        /*
Hold plan_ids for matching wait filter
*/
        CREATE TABLE #wait_filter
        (
            plan_id BIGINT PRIMARY KEY
        );

        /*
Index and statistics entries to avoid
*/
        CREATE TABLE #maintenance_plans
        (
            plan_id BIGINT PRIMARY KEY
        );

        /*
Query Store Setup
*/
        CREATE TABLE #database_query_store_options
        (
            desired_state_desc NVARCHAR(60) NULL,
            actual_state_desc NVARCHAR(60) NULL,
            readonly_reason NVARCHAR(100) NULL,
            current_storage_size_mb BIGINT NULL,
            flush_interval_seconds BIGINT NULL,
            interval_length_minutes BIGINT NULL,
            max_storage_size_mb BIGINT NULL,
            stale_query_threshold_days BIGINT NULL,
            max_plans_per_query BIGINT NULL,
            query_capture_mode_desc NVARCHAR(60) NULL,
            capture_policy_execution_count INT NULL,
            capture_policy_total_compile_cpu_time_ms BIGINT NULL,
            capture_policy_total_execution_cpu_time_ms BIGINT NULL,
            capture_policy_stale_threshold_hours INT NULL,
            size_based_cleanup_mode_desc NVARCHAR(60) NULL,
            wait_stats_capture_mode_desc NVARCHAR(60) NULL
        );

        /*
Plans and Plan information
*/
        CREATE TABLE #query_store_plan
        (
            plan_id BIGINT NOT NULL,
            query_id BIGINT NOT NULL,
            all_plan_ids VARCHAR(MAX),
            plan_group_id BIGINT NULL,
            engine_version NVARCHAR(32) NULL,
            compatibility_level SMALLINT NOT NULL,
            query_plan_hash BINARY(8) NOT NULL,
            query_plan NVARCHAR(MAX) NULL,
            is_online_index_plan BIT NOT NULL,
            is_trivial_plan BIT NOT NULL,
            is_parallel_plan BIT NOT NULL,
            is_forced_plan BIT NOT NULL,
            is_natively_compiled BIT NOT NULL,
            force_failure_count BIGINT NOT NULL,
            last_force_failure_reason INT NOT NULL,
            last_force_failure_reason_desc NVARCHAR(128) NULL,
            count_compiles BIGINT NULL,
            initial_compile_start_time DATETIMEOFFSET(7) NOT NULL,
            last_compile_start_time DATETIMEOFFSET(7) NULL,
            last_execution_time DATETIMEOFFSET(7) NULL,
            avg_compile_duration_ms FLOAT NULL,
            last_compile_duration_ms BIGINT NULL,
            plan_forcing_type INT NULL,
            plan_forcing_type_desc NVARCHAR(60) NULL
        );

        /*
Queries and Compile Information
*/
        CREATE TABLE #query_store_query
        (
            query_id BIGINT NOT NULL,
            query_text_id BIGINT NOT NULL,
            context_settings_id BIGINT NOT NULL,
            object_id BIGINT NULL,
            object_name AS
                ISNULL(
                          QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id)) + N'.'
                          + QUOTENAME(OBJECT_NAME(object_id, database_id)),
                          N'Adhoc'
                      ),
            batch_sql_handle VARBINARY(64) NULL,
            query_hash BINARY(8) NOT NULL,
            is_internal_query BIT NOT NULL,
            query_parameterization_type TINYINT NOT NULL,
            query_parameterization_type_desc NVARCHAR(60) NULL,
            initial_compile_start_time DATETIMEOFFSET(7) NOT NULL,
            last_compile_start_time DATETIMEOFFSET(7) NULL,
            last_execution_time DATETIMEOFFSET(7) NULL,
            last_compile_batch_sql_handle VARBINARY(64) NULL,
            last_compile_batch_offset_start BIGINT NULL,
            last_compile_batch_offset_end BIGINT NULL,
            count_compiles BIGINT NULL,
            avg_compile_duration_ms FLOAT NULL,
            total_compile_duration_ms AS (count_compiles * avg_compile_duration_ms),
            last_compile_duration_ms BIGINT NULL,
            avg_bind_duration_ms FLOAT NULL,
            total_bind_duration_ms AS (count_compiles * avg_bind_duration_ms),
            last_bind_duration_ms BIGINT NULL,
            avg_bind_cpu_time_ms FLOAT NULL,
            total_bind_cpu_time_ms AS (count_compiles * avg_bind_cpu_time_ms),
            last_bind_cpu_time_ms BIGINT NULL,
            avg_optimize_duration_ms FLOAT NULL,
            total_optimize_duration_ms AS (count_compiles * avg_optimize_duration_ms),
            last_optimize_duration_ms BIGINT NULL,
            avg_optimize_cpu_time_ms FLOAT NULL,
            total_optimize_cpu_time_ms AS (count_compiles * avg_optimize_cpu_time_ms),
            last_optimize_cpu_time_ms BIGINT NULL,
            avg_compile_memory_mb FLOAT NULL,
            total_compile_memory_mb AS (count_compiles * avg_compile_memory_mb),
            last_compile_memory_mb BIGINT NULL,
            max_compile_memory_mb BIGINT NULL,
            is_clouddb_internal_query BIT NULL,
            database_id INT NULL
        );

        /*
Query Text And Columns From sys.dm_exec_query_stats
*/
        CREATE TABLE #query_store_query_text
        (
            query_text_id BIGINT NOT NULL,
            query_sql_text XML NULL,
            statement_sql_handle VARBINARY(64) NULL,
            is_part_of_encrypted_module BIT NOT NULL,
            has_restricted_text BIT NOT NULL,
            total_grant_mb BIGINT NULL,
            last_grant_mb BIGINT NULL,
            min_grant_mb BIGINT NULL,
            max_grant_mb BIGINT NULL,
            total_used_grant_mb BIGINT NULL,
            last_used_grant_mb BIGINT NULL,
            min_used_grant_mb BIGINT NULL,
            max_used_grant_mb BIGINT NULL,
            total_ideal_grant_mb BIGINT NULL,
            last_ideal_grant_mb BIGINT NULL,
            min_ideal_grant_mb BIGINT NULL,
            max_ideal_grant_mb BIGINT NULL,
            total_reserved_threads BIGINT NULL,
            last_reserved_threads BIGINT NULL,
            min_reserved_threads BIGINT NULL,
            max_reserved_threads BIGINT NULL,
            total_used_threads BIGINT NULL,
            last_used_threads BIGINT NULL,
            min_used_threads BIGINT NULL,
            max_used_threads BIGINT NULL
        );

        /*
Figure it out.
*/
        CREATE TABLE #dm_exec_query_stats
        (
            statement_sql_handle VARBINARY(64) NOT NULL,
            total_grant_mb BIGINT NULL,
            last_grant_mb BIGINT NULL,
            min_grant_mb BIGINT NULL,
            max_grant_mb BIGINT NULL,
            total_used_grant_mb BIGINT NULL,
            last_used_grant_mb BIGINT NULL,
            min_used_grant_mb BIGINT NULL,
            max_used_grant_mb BIGINT NULL,
            total_ideal_grant_mb BIGINT NULL,
            last_ideal_grant_mb BIGINT NULL,
            min_ideal_grant_mb BIGINT NULL,
            max_ideal_grant_mb BIGINT NULL,
            total_reserved_threads BIGINT NULL,
            last_reserved_threads BIGINT NULL,
            min_reserved_threads BIGINT NULL,
            max_reserved_threads BIGINT NULL,
            total_used_threads BIGINT NULL,
            last_used_threads BIGINT NULL,
            min_used_threads BIGINT NULL,
            max_used_threads BIGINT NULL
        );

        /*
Runtime stats information
*/
        CREATE TABLE #query_store_runtime_stats
        (
            runtime_stats_id BIGINT NOT NULL,
            plan_id BIGINT NOT NULL,
            runtime_stats_interval_id BIGINT NOT NULL,
            execution_type_desc NVARCHAR(60) NULL,
            first_execution_time DATETIMEOFFSET(7) NOT NULL,
            last_execution_time DATETIMEOFFSET(7) NOT NULL,
            count_executions BIGINT NOT NULL,
            executions_per_second AS
                ISNULL(count_executions / NULLIF(DATEDIFF(SECOND, first_execution_time, last_execution_time), 0), 0),
            avg_duration_ms FLOAT NULL,
            last_duration_ms BIGINT NOT NULL,
            min_duration_ms BIGINT NOT NULL,
            max_duration_ms BIGINT NOT NULL,
            total_duration_ms AS (avg_duration_ms * count_executions),
            avg_cpu_time_ms FLOAT NULL,
            last_cpu_time_ms BIGINT NOT NULL,
            min_cpu_time_ms BIGINT NOT NULL,
            max_cpu_time_ms BIGINT NOT NULL,
            total_cpu_time_ms AS (avg_cpu_time_ms * count_executions),
            avg_logical_io_reads_mb FLOAT NULL,
            last_logical_io_reads_mb BIGINT NOT NULL,
            min_logical_io_reads_mb BIGINT NOT NULL,
            max_logical_io_reads_mb BIGINT NOT NULL,
            total_logical_io_reads_mb AS (avg_logical_io_reads_mb * count_executions),
            avg_logical_io_writes_mb FLOAT NULL,
            last_logical_io_writes_mb BIGINT NOT NULL,
            min_logical_io_writes_mb BIGINT NOT NULL,
            max_logical_io_writes_mb BIGINT NOT NULL,
            total_logical_io_writes_mb AS (avg_logical_io_writes_mb * count_executions),
            avg_physical_io_reads_mb FLOAT NULL,
            last_physical_io_reads_mb BIGINT NOT NULL,
            min_physical_io_reads_mb BIGINT NOT NULL,
            max_physical_io_reads_mb BIGINT NOT NULL,
            total_physical_io_reads_mb AS (avg_physical_io_reads_mb * count_executions),
            avg_clr_time_ms FLOAT NULL,
            last_clr_time_ms BIGINT NOT NULL,
            min_clr_time_ms BIGINT NOT NULL,
            max_clr_time_ms BIGINT NOT NULL,
            total_clr_time_ms AS (avg_clr_time_ms * count_executions),
            last_dop BIGINT NOT NULL,
            min_dop BIGINT NOT NULL,
            max_dop BIGINT NOT NULL,
            avg_query_max_used_memory_mb FLOAT NULL,
            last_query_max_used_memory_mb BIGINT NOT NULL,
            min_query_max_used_memory_mb BIGINT NOT NULL,
            max_query_max_used_memory_mb BIGINT NOT NULL,
            total_query_max_used_memory_mb AS (avg_query_max_used_memory_mb * count_executions),
            avg_rowcount FLOAT NULL,
            last_rowcount BIGINT NOT NULL,
            min_rowcount BIGINT NOT NULL,
            max_rowcount BIGINT NOT NULL,
            total_rowcount AS (avg_rowcount * count_executions),
            avg_num_physical_io_reads_mb FLOAT NULL,
            last_num_physical_io_reads_mb BIGINT NULL,
            min_num_physical_io_reads_mb BIGINT NULL,
            max_num_physical_io_reads_mb BIGINT NULL,
            total_num_physical_io_reads_mb AS (avg_num_physical_io_reads_mb * count_executions),
            avg_log_bytes_used_mb FLOAT NULL,
            last_log_bytes_used_mb BIGINT NULL,
            min_log_bytes_used_mb BIGINT NULL,
            max_log_bytes_used_mb BIGINT NULL,
            total_log_bytes_used_mb AS (avg_log_bytes_used_mb * count_executions),
            avg_tempdb_space_used_mb FLOAT NULL,
            last_tempdb_space_used_mb BIGINT NULL,
            min_tempdb_space_used_mb BIGINT NULL,
            max_tempdb_space_used_mb BIGINT NULL,
            total_tempdb_space_used_mb AS (avg_tempdb_space_used_mb * count_executions),
            context_settings NVARCHAR(256) NULL
        );

        /*
Wait Stats, When Available
*/
        CREATE TABLE #query_store_wait_stats
        (
            plan_id BIGINT NOT NULL,
            wait_category_desc NVARCHAR(60) NOT NULL,
            total_query_wait_time_ms BIGINT NOT NULL,
            avg_query_wait_time_ms FLOAT NULL,
            last_query_wait_time_ms BIGINT NOT NULL,
            min_query_wait_time_ms BIGINT NOT NULL,
            max_query_wait_time_ms BIGINT NOT NULL
        );

        /*
Context is everything
*/
        CREATE TABLE #query_context_settings
        (
            context_settings_id BIGINT NOT NULL,
            set_options VARBINARY(8) NULL,
            language_id SMALLINT NOT NULL,
            date_format SMALLINT NOT NULL,
            date_first TINYINT NOT NULL,
            status VARBINARY(2) NULL,
            required_cursor_options INT NOT NULL,
            acceptable_cursor_options INT NOT NULL,
            merge_action_type SMALLINT NOT NULL,
            default_schema_id INT NOT NULL,
            is_replication_specific BIT NOT NULL,
            is_contained VARBINARY(1) NULL
        );

        /*
Trouble Loves Me
*/
        CREATE TABLE #troubleshoot_performance
        (
            id BIGINT IDENTITY,
            current_table NVARCHAR(100),
            start_time DATETIME,
            end_time DATETIME,
            runtime_ms AS FORMAT(DATEDIFF(MILLISECOND, start_time, end_time), 'N0')
        );

        /*
Try to be helpful by subbing in a database name if null
*/
        IF (
               @database_name IS NULL
               AND LOWER(DB_NAME()) NOT IN ( N'master', N'model', N'msdb', N'tempdb', N'dbatools', N'dbadmin',
                                             N'dbmaintenance', N'rdsadmin', N'other_memes'
                                           )
           )
        BEGIN
            SELECT @database_name = DB_NAME();
        END;

        /*
Variables for the variable gods
*/
        DECLARE @azure BIT,
                @engine INT,
                @product_version INT,
                @database_id INT,
                @database_name_quoted sysname,
                @procedure_name_quoted sysname,
                @collation sysname,
                @new BIT,
                @sql NVARCHAR(MAX),
                @isolation_level NVARCHAR(MAX),
                @parameters NVARCHAR(200),
                @plans_top BIGINT,
                @nc10 NVARCHAR(2),
                @where_clause NVARCHAR(MAX),
                @procedure_exists BIT,
                @query_store_exists BIT,
                @query_store_waits_enabled BIT,
                @string_split NVARCHAR(1500),
                @current_table NVARCHAR(100),
                @troubleshoot_insert NVARCHAR(MAX),
                @troubleshoot_update NVARCHAR(MAX),
                @troubleshoot_info NVARCHAR(MAX),
                @rc BIGINT;

        /*
Some variable assignment, because why not?
*/
        SELECT @azure = CASE
                            WHEN CONVERT(sysname, SERVERPROPERTY('EDITION')) = N'SQL Azure' THEN
                                1
                            ELSE
                                0
                        END,
               @engine = CONVERT(INT, SERVERPROPERTY('ENGINEEDITION')),
               @product_version = CONVERT(INT, PARSENAME(CONVERT(sysname, SERVERPROPERTY('PRODUCTVERSION')), 4)),
               @database_id = DB_ID(@database_name),
               @database_name_quoted = QUOTENAME(@database_name),
               @procedure_name_quoted
                   = QUOTENAME(@database_name) + N'.' + QUOTENAME(ISNULL(@procedure_schema, N'dbo')) + N'.'
                     + QUOTENAME(@procedure_name),
               @collation = CONVERT(sysname, DATABASEPROPERTYEX(@database_name, 'Collation')),
               @new = 0,
               @sql = N'',
               @isolation_level = N'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;',
               @parameters
                   = N'@top bigint,
          @start_date datetime,
          @end_date datetime,
          @execution_count bigint,
          @duration_ms bigint',
               @plans_top = CASE
                                WHEN @include_plan_ids IS NULL THEN
                                    1
                                ELSE
                                    10
                            END,
               @nc10 = NCHAR(10),
               @where_clause = N'',
               @procedure_exists = 0,
               @query_store_exists = 0,
               @query_store_waits_enabled = 0,
               @current_table = N'',
               @string_split
                   = N'
    SELECT DISTINCT
        LTRIM
        (
            RTRIM
            (
                ids.ids
            )
        ) AS ids
    FROM
    (
        SELECT
            ids =
                x.x.value
                    (
                        ''(./text())[1]'',
                        ''bigint''
                    )
        FROM
        (
            SELECT
                ids =
                    CONVERT
                    (
                        xml,
                        ''<x>'' +
                        REPLACE
                        (
                            REPLACE
                            (
                                @ids,
                                '','',
                                ''</x><x>''
                            ),
                            '' '',
                            ''''
                        ) +
                        ''</x>''
                    ).query(''.'')
        ) AS ids
            CROSS APPLY ids.nodes(''x'') AS x (x)
    ) AS ids
    OPTION(RECOMPILE);',
               @troubleshoot_insert
                   = N'
        INSERT
            #troubleshoot_performance WITH(TABLOCK)
        (
            current_table,
            start_time
        )
        VALUES
        (
            @current_table,
            GETDATE()
        )
        OPTION(RECOMPILE);',
               @troubleshoot_update
                   = N'
        UPDATE tp
            SET tp.end_time = GETDATE()
        FROM #troubleshoot_performance AS tp
        WHERE tp.current_table = @current_table
        OPTION(RECOMPILE);',
               @troubleshoot_info
                   = N'
        SELECT
            (
                SELECT
                    runtime_ms =
                        tp.runtime_ms,
                    current_table =
                        tp.current_table,
                    query_length =
                        FORMAT(LEN(@sql), ''N0''),
                    ''processing-instruction(statement_text)'' =
                        @sql
                FROM #troubleshoot_performance AS tp
                WHERE tp.current_table = @current_table
                FOR XML PATH(N''''), TYPE
            ).query(''.[1]'') AS current_query
        OPTION(RECOMPILE);',
               @rc = 0;

        /*
Some parameters can't be NULL,
and some shouldn't be empty strings
*/
        SELECT @sort_order = ISNULL(@sort_order, 'cpu'),
               @top = ISNULL(@top, 10),
               @expert_mode = ISNULL(@expert_mode, 0),
               @procedure_schema = NULLIF(@procedure_schema, ''),
               @procedure_name = NULLIF(@procedure_name, ''),
               @include_plan_ids = NULLIF(@include_plan_ids, ''),
               @include_query_ids = NULLIF(@include_query_ids, ''),
               @ignore_plan_ids = NULLIF(@ignore_plan_ids, ''),
               @ignore_query_ids = NULLIF(@ignore_query_ids, ''),
               @wait_filter = NULLIF(@wait_filter, ''),
               @format_output = ISNULL(@format_output, 0),
               @help = ISNULL(@help, 0),
               @debug = ISNULL(@debug, 0),
               @troubleshoot_performance = ISNULL(@troubleshoot_performance, 0);

        /*
Let's make sure things will work
*/

        /*
Database are you there?
*/
        IF (@database_id IS NULL OR @collation IS NULL)
        BEGIN
            RAISERROR('Database %s does not exist', 11, 1, @database_name) WITH NOWAIT;
            RETURN;
        END;

        /*
Database what are you?
*/
        IF (@azure = 1 AND @engine NOT IN ( 5, 8 ))
        BEGIN
            RAISERROR('Not all Azure offerings are supported, please try avoiding memes', 11, 1) WITH NOWAIT;
            RETURN;
        END;

        /*
Database are you compatible?
*/
        IF (
               @azure = 1
               AND EXISTS
        (
            SELECT 1 / 0
            FROM sys.databases AS d
            WHERE d.database_id = @database_id
                  AND d.compatibility_level < 130
        )
           )
        BEGIN
            RAISERROR('Azure databases in compatiblity levels under 130 are not supported', 11, 1) WITH NOWAIT;
            RETURN;
        END;

        /*
Sometimes sys.databases will report Query Store being on, but it's really not
*/
        SELECT @current_table = N'checking query store existence',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT
    @query_store_exists =
        CASE
            WHEN EXISTS
                 (
                     SELECT
                         1/0
                     FROM ' + @database_name_quoted
                       + N'.sys.database_query_store_options AS dqso
                     WHERE ( dqso.actual_state = 0
                               OR dqso.actual_state IS NULL )
                 )
            OR   NOT EXISTS
                     (
                         SELECT
                             1/0
                         FROM ' + @database_name_quoted
                       + N'.sys.database_query_store_options AS dqso
                     )
            THEN 0
            ELSE 1
        END
OPTION(RECOMPILE);' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        EXEC sys.sp_executesql @sql,
                               N'@query_store_exists bit OUTPUT',
                               @query_store_exists OUTPUT;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        IF @query_store_exists = 0
        BEGIN
            RAISERROR('Query Store doesn''t seem to be enabled for database: %s', 11, 1, @database_name) WITH NOWAIT;
            RETURN;
        END;

        /*
If you specified a procedure name, we need to figure out if it's there
*/
        IF @procedure_name IS NOT NULL
        BEGIN

            SELECT @current_table = N'checking procedure existence',
                   @sql = @isolation_level;

            IF @troubleshoot_performance = 1
            BEGIN

                EXEC sys.sp_executesql @troubleshoot_insert,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                SET STATISTICS XML ON;

            END;

            SELECT @sql += N'
SELECT
    @procedure_exists =
        CASE
            WHEN EXISTS
                 (
                     SELECT
                         1/0
                     FROM ' + @database_name_quoted
                           + N'.sys.query_store_query AS qsq
                     WHERE qsq.object_id = OBJECT_ID(@procedure_name_quoted)
                 )
            THEN 1
            ELSE 0
        END
OPTION(RECOMPILE);' + @nc10;

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            EXEC sys.sp_executesql @sql,
                                   N'@procedure_exists bit OUTPUT,
        @procedure_name_quoted sysname',
                                   @procedure_exists OUTPUT,
                                   @procedure_name_quoted;

            IF @troubleshoot_performance = 1
            BEGIN

                SET STATISTICS XML OFF;

                EXEC sys.sp_executesql @troubleshoot_update,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                EXEC sys.sp_executesql @troubleshoot_info,
                                       N'@sql nvarchar(max),
            @current_table nvarchar(100)',
                                       @sql,
                                       @current_table;

            END;

            IF @procedure_exists = 0
            BEGIN
                RAISERROR(
                             'The stored procedure %s does not appear to have any entries in Query Store for database %s
Check that you spelled everything correctly and you''re in the right database',
                             11,
                             1,
                             @procedure_name,
                             @database_name
                         ) WITH NOWAIT;
                RETURN;
            END;
        END;

        /*
Some things are version dependent.
Normally, I'd check for object existence, but the documentation
leads me to believe that certain things won't be back-ported,
like the wait stats DMV, and tempdb spills columns
*/
        IF (@product_version > 13 OR @azure = 1)
        BEGIN
            SELECT @new = 1;
        END;

        /*
Validate Sort Order
*/
        IF @sort_order NOT IN ( 'cpu', 'logical reads', 'physical reads', 'writes', 'duration', 'memory', 'tempdb',
                                'executions'
                              )
        BEGIN
            RAISERROR(
                         'The sort order (%s) you chose is so out of this world that I''m using cpu instead',
                         10,
                         1,
                         @sort_order
                     ) WITH NOWAIT;
            SELECT @sort_order = 'cpu';
        END;

        /*
These columns are only available in 2017+
*/
        IF (@sort_order = 'tempdb' AND @new = 0)
        BEGIN
            RAISERROR(
                         'The sort order (%s) you chose is invalid in product version %i, reverting to cpu',
                         10,
                         1,
                         @sort_order,
                         @product_version
                     ) WITH NOWAIT;
            SELECT @sort_order = N'cpu';
        END;

        IF (@wait_filter IS NOT NULL AND @new = 0)
        BEGIN
            RAISERROR('Query Store wait stats are not available prior to SQL Server 2017', 11, 1) WITH NOWAIT;
            RETURN;
        END;

        IF (
               @new = 1
               AND @wait_filter NOT IN ( 'cpu', 'lock', 'locks', 'latch', 'latches', 'buffer latch', 'buffer latches',
                                         'buffer io', 'log', 'log io', 'network', 'network io', 'parallel',
                                         'parallelism', 'memory'
                                       )
           )
        BEGIN
            RAISERROR('The wait category (%s) you chose is invalid', 11, 1, @wait_filter) WITH NOWAIT;
            RETURN;
        END;

        IF (@wait_filter IS NOT NULL AND @new = 1)
        BEGIN

            SELECT @current_table = N'checking query store waits are enabled',
                   @sql = @isolation_level;

            IF @troubleshoot_performance = 1
            BEGIN

                EXEC sys.sp_executesql @troubleshoot_insert,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                SET STATISTICS XML ON;

            END;

            SELECT @sql += N'
SELECT
    @query_store_waits_enabled =
        CASE
            WHEN EXISTS
                 (
                     SELECT
                         1/0
                     FROM ' + @database_name_quoted
                           + N'.sys.database_query_store_options AS dqso
                     WHERE dqso.wait_stats_capture_mode = 1
                 )
            THEN 1
            ELSE 0
        END
OPTION(RECOMPILE);' + @nc10;

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            EXEC sys.sp_executesql @sql,
                                   N'@query_store_waits_enabled bit OUTPUT',
                                   @query_store_waits_enabled OUTPUT;

            IF @troubleshoot_performance = 1
            BEGIN

                SET STATISTICS XML OFF;

                EXEC sys.sp_executesql @troubleshoot_update,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                EXEC sys.sp_executesql @troubleshoot_info,
                                       N'@sql nvarchar(max),
            @current_table nvarchar(100)',
                                       @sql,
                                       @current_table;

            END;

            IF @query_store_waits_enabled = 0
            BEGIN
                RAISERROR('Query Store wait stats are not enabled for database %s', 11, 1, @database_name_quoted) WITH NOWAIT;
                RETURN;
            END;

        END;


        /*
Get filters ready, or whatever
We're only going to pull some stuff from runtime stats and plans
*/

        IF @start_date IS NULL
        BEGIN
            SELECT @where_clause += N'AND   qsrs.last_execution_time >= DATEADD(DAY, -1, DATEDIFF(DAY, 0, SYSDATETIME()))'
                                    + @nc10;
        END;

        IF @start_date IS NOT NULL
        BEGIN
            SELECT @where_clause += N'AND   qsrs.last_execution_time >= @start_date' + @nc10;
        END;

        IF @end_date IS NOT NULL
        BEGIN
            SELECT @where_clause += N'AND   qsrs.last_execution_time < @end_date' + @nc10;
        END;

        IF @execution_count IS NOT NULL
        BEGIN
            SELECT @where_clause += N'AND   qsrs.count_executions >= @execution_count' + @nc10;
        END;

        IF @duration_ms IS NOT NULL
        BEGIN
            SELECT @where_clause += N'AND   qsrs.avg_duration >= (@duration_ms * 1000.)' + @nc10;
        END;

        IF (@procedure_name IS NOT NULL AND @procedure_exists = 1)
        BEGIN

            SELECT @current_table = N'inserting #procedure_plans',
                   @sql = @isolation_level;

            IF @troubleshoot_performance = 1
            BEGIN

                EXEC sys.sp_executesql @troubleshoot_insert,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                SET STATISTICS XML ON;

            END;

            SELECT @sql += N'
SELECT DISTINCT
    qsp.plan_id
FROM ' +        @database_name_quoted + N'.sys.query_store_query AS qsq
JOIN ' +        @database_name_quoted
                           + N'.sys.query_store_plan AS qsp
   ON qsq.query_id = qsp.query_id
WHERE qsq.object_id = OBJECT_ID(@procedure_name_quoted)
OPTION(RECOMPILE);' + @nc10;

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            INSERT #procedure_plans WITH (TABLOCK)
            (
                plan_id
            )
            EXEC sys.sp_executesql @sql,
                                   N'@procedure_name_quoted sysname',
                                   @procedure_name_quoted;

            IF @troubleshoot_performance = 1
            BEGIN

                SET STATISTICS XML OFF;

                EXEC sys.sp_executesql @troubleshoot_update,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                EXEC sys.sp_executesql @troubleshoot_info,
                                       N'@sql nvarchar(max),
            @current_table nvarchar(100)',
                                       @sql,
                                       @current_table;

            END;

            SELECT @where_clause += N'AND   EXISTS
        (
            SELECT
                1/0
            FROM #procedure_plans AS pp
            WHERE pp.plan_id = qsrs.plan_id
        )' +    @nc10;

        END;

        /*
This section filters query or plan ids
*/
        IF (
               @include_plan_ids IS NOT NULL
               OR @include_query_ids IS NOT NULL
               OR @ignore_plan_ids IS NOT NULL
               OR @ignore_query_ids IS NOT NULL
           )
        BEGIN

            IF @include_plan_ids IS NOT NULL
            BEGIN

                SELECT @current_table = N'inserting #include_plan_ids';

                INSERT #include_plan_ids WITH (TABLOCK)
                (
                    plan_id
                )
                EXEC sys.sp_executesql @string_split,
                                       N'@ids nvarchar(4000)',
                                       @include_plan_ids;

                SELECT @where_clause += N'AND   EXISTS
      (
         SELECT
            1/0
         FROM #include_plan_ids AS idi
         WHERE idi.plan_id = qsrs.plan_id
      )' +          @nc10;

            END;

            IF @ignore_plan_ids IS NOT NULL
            BEGIN

                SELECT @current_table = N'inserting #ignore_plan_ids';

                INSERT #ignore_plan_ids WITH (TABLOCK)
                (
                    plan_id
                )
                EXEC sys.sp_executesql @string_split,
                                       N'@ids nvarchar(4000)',
                                       @ignore_plan_ids;

                SELECT @where_clause += N'AND   NOT EXISTS
          (
             SELECT
                1/0
             FROM #ignore_plan_ids AS idi
             WHERE idi.plan_id = qsrs.plan_id
          )' +      @nc10;

            END;

            IF @include_query_ids IS NOT NULL
            BEGIN

                SELECT @current_table = N'inserting #include_query_ids',
                       @sql = @isolation_level;

                INSERT #include_query_ids WITH (TABLOCK)
                (
                    query_id
                )
                EXEC sys.sp_executesql @string_split,
                                       N'@ids nvarchar(4000)',
                                       @include_query_ids;

                SELECT @current_table = N'inserting #include_plan_ids for included query ids';

                IF @troubleshoot_performance = 1
                BEGIN

                    EXEC sys.sp_executesql @troubleshoot_insert,
                                           N'@current_table nvarchar(100)',
                                           @current_table;

                    SET STATISTICS XML ON;

                END;

                SELECT @sql += N'
SELECT DISTINCT
    qsp.plan_id
FROM ' +            @database_name_quoted
                               + N'.sys.query_store_plan AS qsp
WHERE EXISTS
      (
          SELECT
              1/0
          FROM #include_query_ids AS iqi
          WHERE iqi.query_id = qsp.query_id
      )
OPTION(RECOMPILE);' + @nc10;

                IF @debug = 1
                BEGIN
                    PRINT LEN(@sql);
                    PRINT @sql;
                END;

                INSERT #include_plan_ids
                (
                    plan_id
                )
                EXEC sys.sp_executesql @sql;

                IF @troubleshoot_performance = 1
                BEGIN

                    SET STATISTICS XML OFF;

                    EXEC sys.sp_executesql @troubleshoot_update,
                                           N'@current_table nvarchar(100)',
                                           @current_table;

                    EXEC sys.sp_executesql @troubleshoot_info,
                                           N'@sql nvarchar(max),
                @current_table nvarchar(100)',
                                           @sql,
                                           @current_table;

                END;

                IF @include_plan_ids IS NULL
                BEGIN
                    SELECT @where_clause += N'AND   EXISTS
          (
             SELECT
                1/0
             FROM #include_plan_ids AS idi
             WHERE idi.plan_id = qsrs.plan_id
          )' +          @nc10;
                END;

            END;

            IF @ignore_query_ids IS NOT NULL
            BEGIN

                SELECT @current_table = N'inserting #ignore_query_ids',
                       @sql = @isolation_level;

                INSERT #ignore_query_ids WITH (TABLOCK)
                (
                    query_id
                )
                EXEC sys.sp_executesql @string_split,
                                       N'@ids nvarchar(4000)',
                                       @ignore_query_ids;

                SELECT @current_table = N'inserting #ignore_plan_ids for ignored query ids';

                IF @troubleshoot_performance = 1
                BEGIN

                    EXEC sys.sp_executesql @troubleshoot_insert,
                                           N'@current_table nvarchar(100)',
                                           @current_table;

                    SET STATISTICS XML ON;

                END;

                SELECT @sql += N'
SELECT DISTINCT
    qsp.plan_id
FROM ' +            @database_name_quoted
                               + N'.sys.query_store_plan AS qsp
WHERE EXISTS
      (
          SELECT
              1/0
          FROM #ignore_query_ids AS iqi
          WHERE iqi.query_id = qsp.query_id
      )
OPTION(RECOMPILE);' + @nc10;

                IF @debug = 1
                BEGIN
                    PRINT LEN(@sql);
                    PRINT @sql;
                END;

                INSERT #ignore_plan_ids
                (
                    plan_id
                )
                EXEC sys.sp_executesql @sql;

                IF @troubleshoot_performance = 1
                BEGIN

                    SET STATISTICS XML OFF;

                    EXEC sys.sp_executesql @troubleshoot_update,
                                           N'@current_table nvarchar(100)',
                                           @current_table;

                    EXEC sys.sp_executesql @troubleshoot_info,
                                           N'@sql nvarchar(max),
                @current_table nvarchar(100)',
                                           @sql,
                                           @current_table;

                END;

                IF @ignore_plan_ids IS NULL
                BEGIN
                    SELECT @where_clause += N'AND   NOT EXISTS
              (
                 SELECT
                    1/0
                 FROM #ignore_plan_ids AS idi
                 WHERE idi.plan_id = qsrs.plan_id
              )' +      @nc10;
                END;
            END;

        END;

        IF @query_text_search IS NOT NULL
        BEGIN

            IF (LEFT(@query_text_search, 1) <> N'%')
            BEGIN
                SELECT @query_text_search = N'%' + @query_text_search;
            END;

            IF (LEFT(REVERSE(@query_text_search), 1) <> N'%')
            BEGIN
                SELECT @query_text_search = @query_text_search + N'%';
            END;

            SELECT @current_table = N'inserting #query_text_search',
                   @sql = @isolation_level;

            IF @troubleshoot_performance = 1
            BEGIN

                EXEC sys.sp_executesql @troubleshoot_insert,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                SET STATISTICS XML ON;

            END;

            SELECT @sql += N'
SELECT DISTINCT
    qsp.plan_id
FROM ' +        @database_name_quoted
                           + N'.sys.query_store_plan AS qsp
WHERE EXISTS
      (
          SELECT
              1/0
          FROM ' + @database_name_quoted
                           + N'.sys.query_store_query AS qsq
          WHERE qsp.query_id = qsq.query_id
          AND EXISTS
              (
                  SELECT
                      1/0
                  FROM ' + @database_name_quoted
                           + N'.sys.query_store_query_text AS qsqt
                  WHERE qsqt.query_text_id = qsq.query_text_id
                  AND   qsqt.query_sql_text LIKE @query_text_search
              )
      )
OPTION(RECOMPILE);' + @nc10;

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            INSERT #query_text_search WITH (TABLOCK)
            (
                plan_id
            )
            EXEC sys.sp_executesql @sql,
                                   N'@query_text_search nvarchar(4000)',
                                   @query_text_search;

            IF @troubleshoot_performance = 1
            BEGIN

                SET STATISTICS XML OFF;

                EXEC sys.sp_executesql @troubleshoot_update,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                EXEC sys.sp_executesql @troubleshoot_info,
                                       N'@sql nvarchar(max),
            @current_table nvarchar(100)',
                                       @sql,
                                       @current_table;

            END;

            SELECT @where_clause += N'AND   EXISTS
       (
           SELECT
               1/0
           FROM #query_text_search AS qst
           WHERE qst.plan_id = qsrs.plan_id
       )' +     @nc10;

        END;

        /*
Validate wait stats stuff
*/
        IF @wait_filter IS NOT NULL
        BEGIN

            BEGIN

                SELECT @current_table = N'inserting #wait_filter',
                       @sql = @isolation_level;

                IF @troubleshoot_performance = 1
                BEGIN

                    EXEC sys.sp_executesql @troubleshoot_insert,
                                           N'@current_table nvarchar(100)',
                                           @current_table;

                    SET STATISTICS XML ON;

                END;

                SELECT @sql += N'
SELECT TOP (@top)
    qsws.plan_id
FROM  ' +           @database_name_quoted
                               + N'.sys.query_store_wait_stats AS qsws
WHERE qsws.execution_type = 0
AND   qsws.wait_category = ' + CASE @wait_filter
                                   WHEN 'cpu' THEN
                                       N'1'
                                   WHEN 'lock' THEN
                                       N'3'
                                   WHEN 'locks' THEN
                                       N'3'
                                   WHEN 'latch' THEN
                                       N'4'
                                   WHEN 'latches' THEN
                                       N'4'
                                   WHEN 'buffer latch' THEN
                                       N'5'
                                   WHEN 'buffer latches' THEN
                                       N'5'
                                   WHEN 'buffer io' THEN
                                       N'6'
                                   WHEN 'log' THEN
                                       N'14'
                                   WHEN 'log io' THEN
                                       N'14'
                                   WHEN 'network' THEN
                                       N'15'
                                   WHEN 'network io' THEN
                                       N'15'
                                   WHEN 'parallel' THEN
                                       N'16'
                                   WHEN 'parallelism' THEN
                                       N'16'
                                   WHEN 'memory' THEN
                                       N'17'
                               END
                               + N'
GROUP BY qsws.plan_id
HAVING SUM(qsws.avg_query_wait_time_ms) > 1000.
ORDER BY SUM(qsws.avg_query_wait_time_ms) DESC
OPTION(RECOMPILE, OPTIMIZE FOR (@top = 9223372036854775807));' + @nc10;

            END;

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            INSERT #wait_filter WITH (TABLOCK)
            (
                plan_id
            )
            EXEC sys.sp_executesql @sql, N'@top bigint', @top;

            IF @troubleshoot_performance = 1
            BEGIN

                SET STATISTICS XML OFF;

                EXEC sys.sp_executesql @troubleshoot_update,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                EXEC sys.sp_executesql @troubleshoot_info,
                                       N'@sql nvarchar(max),
            @current_table nvarchar(100)',
                                       @sql,
                                       @current_table;

            END;

            SELECT @where_clause += N'AND   EXISTS
       (
           SELECT
               1/0
           FROM #wait_filter AS wf
           WHERE wf.plan_id = qsrs.plan_id
       )' +     @nc10;

        END;


        /*
This section screens out index create and alter statements because who cares
*/

        SELECT @current_table = N'inserting #maintenance_plans',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT DISTINCT
   qsp.plan_id
FROM ' +    @database_name_quoted
                       + N'.sys.query_store_plan AS qsp
WHERE NOT EXISTS
          (
              SELECT
                 1/0
              FROM ' + @database_name_quoted + N'.sys.query_store_query AS qsq
              JOIN ' + @database_name_quoted
                       + N'.sys.query_store_query_text AS qsqt
                  ON qsqt.query_text_id = qsq.query_text_id
              WHERE qsq.query_id = qsp.query_id
              AND   qsqt.query_sql_text NOT LIKE ''ALTER INDEX%''
              AND   qsqt.query_sql_text NOT LIKE ''CREATE%INDEX%''
              AND   qsqt.query_sql_text NOT LIKE ''CREATE STATISTICS%''
              AND   qsqt.query_sql_text NOT LIKE ''UPDATE STATISTICS%''
              AND   qsqt.query_sql_text NOT LIKE ''SELECT StatMan%''
          )
OPTION(RECOMPILE);' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        INSERT #maintenance_plans WITH (TABLOCK)
        (
            plan_id
        )
        EXEC sys.sp_executesql @sql;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        SELECT @where_clause += N'AND   NOT EXISTS
          (
              SELECT
                  1/0
              FROM #maintenance_plans AS mp
              WHERE mp.plan_id = qsrs.plan_id
          )' + @nc10;

        /*
Tidy up the where clause a bit
*/
        SELECT @where_clause = SUBSTRING(@where_clause, 1, LEN(@where_clause) - 1);

        /*
This gets the plan_ids we care about
*/
        SELECT @current_table = N'inserting #distinct_plans',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT TOP (@top)
    qsrs.plan_id
FROM ' +    @database_name_quoted + N'.sys.query_store_runtime_stats AS qsrs
WHERE qsrs.execution_type = 0
' +         @where_clause + N'
GROUP BY qsrs.plan_id
ORDER BY MAX(' + CASE @sort_order
                     WHEN 'cpu' THEN
                         N'qsrs.avg_cpu_time'
                     WHEN 'logical reads' THEN
                         N'qsrs.avg_logical_io_reads'
                     WHEN 'physical reads' THEN
                         N'qsrs.avg_physical_io_reads'
                     WHEN 'writes' THEN
                         N'qsrs.avg_logical_io_writes'
                     WHEN 'duration' THEN
                         N'qsrs.avg_duration'
                     WHEN 'memory' THEN
                         N'qsrs.avg_query_max_used_memory'
                     WHEN 'tempdb' THEN
                         CASE
                             WHEN @new = 1 THEN
                                 N'qsrs.avg_tempdb_space_used'
                             ELSE
                                 N'qsrs.avg_cpu_time'
                         END
                     WHEN 'executions' THEN
                         N'qsrs.count_executions'
                     ELSE
                         N'qsrs.avg_cpu_time'
                 END + N') DESC
OPTION(RECOMPILE, OPTIMIZE FOR (@top = 9223372036854775807));' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        INSERT #distinct_plans WITH (TABLOCK)
        (
            plan_id
        )
        EXEC sys.sp_executesql @sql,
                               @parameters,
                               @top,
                               @start_date,
                               @end_date,
                               @execution_count,
                               @duration_ms;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        /*
This gets the runtime stats for the plans we care about
*/
        SELECT @current_table = N'inserting #query_store_runtime_stats',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT
    qsrs.runtime_stats_id,
    qsrs.plan_id,
    qsrs.runtime_stats_interval_id,
    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.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 = 1
        BEGIN

            SELECT @sql += N'
    ((qsrs.avg_num_physical_io_reads * 8.) / 1024.),
    ((qsrs.last_num_physical_io_reads * 8.) / 1024.),
    ((qsrs.min_num_physical_io_reads * 8.) / 1024.),
    ((qsrs.max_num_physical_io_reads * 8.) / 1024.),
    (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 = 0
        BEGIN

            SELECT @sql += N'
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,'  ;

        END;

        SELECT @sql += N'
    context_settings = NULL
FROM #distinct_plans AS dp
CROSS APPLY
(
    SELECT TOP (1)
        qsrs.*
    FROM ' + @database_name_quoted
                       + N'.sys.query_store_runtime_stats AS qsrs
    WHERE qsrs.plan_id = dp.plan_id
    AND   qsrs.execution_type = 0
    ' +     @where_clause + N'
ORDER BY ' + CASE @sort_order
                 WHEN 'cpu' THEN
                     N'qsrs.avg_cpu_time'
                 WHEN 'logical reads' THEN
                     N'qsrs.avg_logical_io_reads'
                 WHEN 'physical reads' THEN
                     N'qsrs.avg_physical_io_reads'
                 WHEN 'writes' THEN
                     N'qsrs.avg_logical_io_writes'
                 WHEN 'duration' THEN
                     N'qsrs.avg_duration'
                 WHEN 'memory' THEN
                     N'qsrs.avg_query_max_used_memory'
                 WHEN 'tempdb' THEN
                     CASE
                         WHEN @new = 1 THEN
                             N'qsrs.avg_tempdb_space_used'
                         ELSE
                             N'qsrs.avg_cpu_time'
                     END
                 WHEN 'executions' THEN
                     N'qsrs.count_executions'
                 ELSE
                     N'qsrs.avg_cpu_time'
             END + N' DESC
) AS qsrs
OPTION(RECOMPILE);' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        INSERT #query_store_runtime_stats WITH (TABLOCK)
        (
            runtime_stats_id,
            plan_id,
            runtime_stats_interval_id,
            execution_type_desc,
            first_execution_time,
            last_execution_time,
            count_executions,
            avg_duration_ms,
            last_duration_ms,
            min_duration_ms,
            max_duration_ms,
            avg_cpu_time_ms,
            last_cpu_time_ms,
            min_cpu_time_ms,
            max_cpu_time_ms,
            avg_logical_io_reads_mb,
            last_logical_io_reads_mb,
            min_logical_io_reads_mb,
            max_logical_io_reads_mb,
            avg_logical_io_writes_mb,
            last_logical_io_writes_mb,
            min_logical_io_writes_mb,
            max_logical_io_writes_mb,
            avg_physical_io_reads_mb,
            last_physical_io_reads_mb,
            min_physical_io_reads_mb,
            max_physical_io_reads_mb,
            avg_clr_time_ms,
            last_clr_time_ms,
            min_clr_time_ms,
            max_clr_time_ms,
            last_dop,
            min_dop,
            max_dop,
            avg_query_max_used_memory_mb,
            last_query_max_used_memory_mb,
            min_query_max_used_memory_mb,
            max_query_max_used_memory_mb,
            avg_rowcount,
            last_rowcount,
            min_rowcount,
            max_rowcount,
            avg_num_physical_io_reads_mb,
            last_num_physical_io_reads_mb,
            min_num_physical_io_reads_mb,
            max_num_physical_io_reads_mb,
            avg_log_bytes_used_mb,
            last_log_bytes_used_mb,
            min_log_bytes_used_mb,
            max_log_bytes_used_mb,
            avg_tempdb_space_used_mb,
            last_tempdb_space_used_mb,
            min_tempdb_space_used_mb,
            max_tempdb_space_used_mb,
            context_settings
        )
        EXEC sys.sp_executesql @sql,
                               @parameters,
                               @top,
                               @start_date,
                               @end_date,
                               @execution_count,
                               @duration_ms;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        /*
This gets context info and settings
*/
        SELECT @current_table = N'inserting #query_context_settings',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT
    context_settings_id,
    set_options,
    language_id,
    date_format,
    date_first,
    status,
    required_cursor_options,
    acceptable_cursor_options,
    merge_action_type,
    default_schema_id,
    is_replication_specific,
    is_contained
FROM ' +    @database_name_quoted + N'.sys.query_context_settings;';

        INSERT #query_context_settings WITH (TABLOCK)
        (
            context_settings_id,
            set_options,
            language_id,
            date_format,
            date_first,
            status,
            required_cursor_options,
            acceptable_cursor_options,
            merge_action_type,
            default_schema_id,
            is_replication_specific,
            is_contained
        )
        EXEC sys.sp_executesql @sql;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        /*
This gets the query plans we're after
*/
        SELECT @current_table = N'inserting #query_store_plan',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT
    qsp.plan_id,
    qsp.query_id,
    all_plan_ids =
        STUFF
        (
            (
                SELECT DISTINCT
                    '', '' +
                    RTRIM
                        (qsp_plans.plan_id)
                FROM ' + @database_name_quoted
                       + N'.sys.query_store_plan AS qsp_plans
                WHERE qsp_plans.query_id = qsp.query_id
                FOR XML PATH(''''), TYPE
            ).value(''./text()[1]'', ''varchar(max)''),
            1,
            2,
            ''''
        ),
    qsp.plan_group_id,
    qsp.engine_version,
    qsp.compatibility_level,
    qsp.query_plan_hash,
    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,
    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.),';

        IF @new = 1
        BEGIN
            SELECT @sql += N'
    qsp.plan_forcing_type,
    qsp.plan_forcing_type_desc';
        END;

        IF @new = 0
        BEGIN
            SELECT @sql += N'
    NULL,
    NULL'   ;
        END;

        SELECT @sql += N'
FROM #query_store_runtime_stats AS qsrs
CROSS APPLY
(
    SELECT TOP (@plans_top)
        qsp.*
    FROM ' + @database_name_quoted
                       + N'.sys.query_store_plan AS qsp
    WHERE qsp.plan_id = qsrs.plan_id
    AND   qsp.is_online_index_plan = 0
    ORDER BY qsp.last_execution_time DESC
) AS qsp
OPTION(RECOMPILE, OPTIMIZE FOR (@plans_top = 9223372036854775807));' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        INSERT #query_store_plan WITH (TABLOCK)
        (
            plan_id,
            query_id,
            all_plan_ids,
            plan_group_id,
            engine_version,
            compatibility_level,
            query_plan_hash,
            query_plan,
            is_online_index_plan,
            is_trivial_plan,
            is_parallel_plan,
            is_forced_plan,
            is_natively_compiled,
            force_failure_count,
            last_force_failure_reason,
            last_force_failure_reason_desc,
            count_compiles,
            initial_compile_start_time,
            last_compile_start_time,
            last_execution_time,
            avg_compile_duration_ms,
            last_compile_duration_ms,
            plan_forcing_type,
            plan_forcing_type_desc
        )
        EXEC sys.sp_executesql @sql, N'@plans_top bigint', @plans_top;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        /*
This gets some query information
*/
        SELECT @current_table = N'inserting #query_store_query',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT
    qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.object_id,
    qsq.batch_sql_handle,
    qsq.query_hash,
    qsq.is_internal_query,
    qsq.query_parameterization_type,
    qsq.query_parameterization_type_desc,
    qsq.initial_compile_start_time,
    qsq.last_compile_start_time,
    qsq.last_execution_time,
    qsq.last_compile_batch_sql_handle,
    qsq.last_compile_batch_offset_start,
    qsq.last_compile_batch_offset_end,
    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 * 8) / 1024.),
    ((qsq.last_compile_memory_kb * 8) / 1024.),
    ((qsq.max_compile_memory_kb * 8) / 1024.),
    qsq.is_clouddb_internal_query,
    @database_id
FROM #query_store_plan AS qsp
CROSS APPLY
(
    SELECT TOP (1)
        qsq.*
    FROM ' + @database_name_quoted
                       + N'.sys.query_store_query AS qsq
    WHERE qsq.query_id = qsp.query_id
    ORDER BY qsq.last_execution_time DESC
) AS qsq
OPTION(RECOMPILE);' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        INSERT #query_store_query WITH (TABLOCK)
        (
            query_id,
            query_text_id,
            context_settings_id,
            object_id,
            batch_sql_handle,
            query_hash,
            is_internal_query,
            query_parameterization_type,
            query_parameterization_type_desc,
            initial_compile_start_time,
            last_compile_start_time,
            last_execution_time,
            last_compile_batch_sql_handle,
            last_compile_batch_offset_start,
            last_compile_batch_offset_end,
            count_compiles,
            avg_compile_duration_ms,
            last_compile_duration_ms,
            avg_bind_duration_ms,
            last_bind_duration_ms,
            avg_bind_cpu_time_ms,
            last_bind_cpu_time_ms,
            avg_optimize_duration_ms,
            last_optimize_duration_ms,
            avg_optimize_cpu_time_ms,
            last_optimize_cpu_time_ms,
            avg_compile_memory_mb,
            last_compile_memory_mb,
            max_compile_memory_mb,
            is_clouddb_internal_query,
            database_id
        )
        EXEC sys.sp_executesql @sql, N'@database_id int', @database_id;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        /*
This gets the query text for them!
*/
        SELECT @current_table = N'inserting #query_store_query_text',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;


        SELECT @sql += N'
SELECT
    qsqt.query_text_id,
    query_sql_text =
        (
             SELECT
                 [processing-instruction(query)] =
                     qsqt.query_sql_text
             FOR XML PATH(''''), TYPE
        ),
    qsqt.statement_sql_handle,
    qsqt.is_part_of_encrypted_module,
    qsqt.has_restricted_text
FROM #query_store_query AS qsq
CROSS APPLY
(
    SELECT TOP (1)
        qsqt.*
    FROM ' + @database_name_quoted
                       + N'.sys.query_store_query_text AS qsqt
    WHERE qsqt.query_text_id = qsq.query_text_id
) AS qsqt
OPTION(RECOMPILE);' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        INSERT #query_store_query_text WITH (TABLOCK)
        (
            query_text_id,
            query_sql_text,
            statement_sql_handle,
            is_part_of_encrypted_module,
            has_restricted_text
        )
        EXEC sys.sp_executesql @sql;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        /*
Here we try to get some data from the "plan cache"
that isn't available in Query Store :(
*/
        SELECT @sql = N'',
               @current_table = N'inserting #dm_exec_query_stats';

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        INSERT #dm_exec_query_stats WITH (TABLOCK)
        (
            statement_sql_handle,
            total_grant_mb,
            last_grant_mb,
            min_grant_mb,
            max_grant_mb,
            total_used_grant_mb,
            last_used_grant_mb,
            min_used_grant_mb,
            max_used_grant_mb,
            total_ideal_grant_mb,
            last_ideal_grant_mb,
            min_ideal_grant_mb,
            max_ideal_grant_mb,
            total_reserved_threads,
            last_reserved_threads,
            min_reserved_threads,
            max_reserved_threads,
            total_used_threads,
            last_used_threads,
            min_used_threads,
            max_used_threads
        )
        SELECT deqs.statement_sql_handle,
               MAX(deqs.total_grant_kb) / 1024.,
               MAX(deqs.last_grant_kb) / 1024.,
               MAX(deqs.min_grant_kb) / 1024.,
               MAX(deqs.max_grant_kb) / 1024.,
               MAX(deqs.total_used_grant_kb) / 1024.,
               MAX(deqs.last_used_grant_kb) / 1024.,
               MAX(deqs.min_used_grant_kb) / 1024.,
               MAX(deqs.max_used_grant_kb) / 1024.,
               MAX(deqs.total_ideal_grant_kb) / 1024.,
               MAX(deqs.last_ideal_grant_kb) / 1024.,
               MAX(deqs.min_ideal_grant_kb) / 1024.,
               MAX(deqs.max_ideal_grant_kb) / 1024.,
               MAX(deqs.total_reserved_threads),
               MAX(deqs.last_reserved_threads),
               MAX(deqs.min_reserved_threads),
               MAX(deqs.max_reserved_threads),
               MAX(deqs.total_used_threads),
               MAX(deqs.last_used_threads),
               MAX(deqs.min_used_threads),
               MAX(deqs.max_used_threads)
        FROM sys.dm_exec_query_stats AS deqs
        WHERE EXISTS
        (
            SELECT 1 / 0
            FROM #query_store_query_text AS qsqt
            WHERE qsqt.statement_sql_handle = deqs.statement_sql_handle
        )
        GROUP BY deqs.statement_sql_handle
        OPTION (RECOMPILE);

        SELECT @rc = @@ROWCOUNT;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        IF @rc > 0
        BEGIN

            SELECT @current_table = N'updating #dm_exec_query_stats';

            IF @troubleshoot_performance = 1
            BEGIN

                EXEC sys.sp_executesql @troubleshoot_insert,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                SET STATISTICS XML ON;

            END;

            UPDATE qsqt
            SET qsqt.total_grant_mb = deqs.total_grant_mb,
                qsqt.last_grant_mb = deqs.last_grant_mb,
                qsqt.min_grant_mb = deqs.min_grant_mb,
                qsqt.max_grant_mb = deqs.max_grant_mb,
                qsqt.total_used_grant_mb = deqs.total_used_grant_mb,
                qsqt.last_used_grant_mb = deqs.last_used_grant_mb,
                qsqt.min_used_grant_mb = deqs.min_used_grant_mb,
                qsqt.max_used_grant_mb = deqs.max_used_grant_mb,
                qsqt.total_ideal_grant_mb = deqs.total_ideal_grant_mb,
                qsqt.last_ideal_grant_mb = deqs.last_ideal_grant_mb,
                qsqt.min_ideal_grant_mb = deqs.min_ideal_grant_mb,
                qsqt.max_ideal_grant_mb = deqs.max_ideal_grant_mb,
                qsqt.total_reserved_threads = deqs.total_reserved_threads,
                qsqt.last_reserved_threads = deqs.last_reserved_threads,
                qsqt.min_reserved_threads = deqs.min_reserved_threads,
                qsqt.max_reserved_threads = deqs.max_reserved_threads,
                qsqt.total_used_threads = deqs.total_used_threads,
                qsqt.last_used_threads = deqs.last_used_threads,
                qsqt.min_used_threads = deqs.min_used_threads,
                qsqt.max_used_threads = deqs.max_used_threads
            FROM #query_store_query_text AS qsqt
                JOIN #dm_exec_query_stats AS deqs
                    ON qsqt.statement_sql_handle = deqs.statement_sql_handle
            OPTION (RECOMPILE);

            IF @troubleshoot_performance = 1
            BEGIN

                SET STATISTICS XML OFF;

                EXEC sys.sp_executesql @troubleshoot_update,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                EXEC sys.sp_executesql @troubleshoot_info,
                                       N'@sql nvarchar(max),
            @current_table nvarchar(100)',
                                       @sql,
                                       @current_table;

            END;

        END;

        /*
Let's check on settings, etc.
We do this first so we can see if wait stats capture mode is true more easily
*/
        SELECT @current_table = N'inserting #database_query_store_options',
               @sql = @isolation_level;

        IF @troubleshoot_performance = 1
        BEGIN

            EXEC sys.sp_executesql @troubleshoot_insert,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            SET STATISTICS XML ON;

        END;

        SELECT @sql += N'
SELECT
    dqso.desired_state_desc,
    dqso.actual_state_desc,
    readonly_reason =
        CASE dqso.readonly_reason
             WHEN 0
             THEN ''None''
             WHEN 2
             THEN ''Database in single user mode''
             WHEN 4
             THEN ''Database is in emergency mode''
             WHEN 8
             THEN ''Database is AG secondary''
             WHEN 65536
             THEN ''Reached max size: '' +
                  FORMAT(dqso.current_storage_size_mb, ''N0'') +
                  '' of '' +
                  FORMAT(dqso.max_storage_size_mb, ''N0'') +
                  ''.''
             WHEN 131072
             THEN ''The number of different statements in Query Store has reached the internal memory limit''
             WHEN 262144
             THEN ''Size of in-memory items waiting to be persisted on disk has reached the internal memory limit''
             WHEN 524288
             THEN ''Database has reached disk size limit''
             ELSE ''WOAH''
        END,
    dqso.current_storage_size_mb,
    dqso.flush_interval_seconds,
    dqso.interval_length_minutes,
    dqso.max_storage_size_mb,
    dqso.stale_query_threshold_days,
    dqso.max_plans_per_query,
    dqso.query_capture_mode_desc,'
                       + CASE
                             WHEN
                             (
                                 @product_version > 14
                                 OR @azure
            =                    1
                             ) THEN
                                 N'
    dqso.capture_policy_execution_count,
    dqso.capture_policy_total_compile_cpu_time_ms,
    dqso.capture_policy_total_execution_cpu_time_ms,
    dqso.capture_policy_stale_threshold_hours,'
                             ELSE
                                 N'
    NULL,
    NULL,
    NULL,
    NULL,'
                         END + N'
    dqso.size_based_cleanup_mode_desc,' + CASE
                                              WHEN
                                              (
                                                  @product_version = 13
                                                  AND @azure = 0
                                              ) THEN
                                                  N'
    NULL'
                                              ELSE
                                                  N'
    dqso.wait_stats_capture_mode_desc'
                                          END + N'
FROM ' +    @database_name_quoted + N'.sys.database_query_store_options AS dqso
OPTION(RECOMPILE);' + @nc10;

        IF @debug = 1
        BEGIN
            PRINT LEN(@sql);
            PRINT @sql;
        END;

        INSERT #database_query_store_options WITH (TABLOCK)
        (
            desired_state_desc,
            actual_state_desc,
            readonly_reason,
            current_storage_size_mb,
            flush_interval_seconds,
            interval_length_minutes,
            max_storage_size_mb,
            stale_query_threshold_days,
            max_plans_per_query,
            query_capture_mode_desc,
            capture_policy_execution_count,
            capture_policy_total_compile_cpu_time_ms,
            capture_policy_total_execution_cpu_time_ms,
            capture_policy_stale_threshold_hours,
            size_based_cleanup_mode_desc,
            wait_stats_capture_mode_desc
        )
        EXEC sys.sp_executesql @sql;

        IF @troubleshoot_performance = 1
        BEGIN

            SET STATISTICS XML OFF;

            EXEC sys.sp_executesql @troubleshoot_update,
                                   N'@current_table nvarchar(100)',
                                   @current_table;

            EXEC sys.sp_executesql @troubleshoot_info,
                                   N'@sql nvarchar(max),
        @current_table nvarchar(100)',
                                   @sql,
                                   @current_table;

        END;

        /*
If wait stats are available, we'll grab them here
*/
        IF (
               @new = 1
               AND EXISTS
        (
            SELECT 1 / 0
            FROM #database_query_store_options AS dqso
            WHERE dqso.wait_stats_capture_mode_desc = 'ON'
        )
           )
        BEGIN

            SELECT @current_table = N'inserting #query_store_wait_stats',
                   @sql = @isolation_level;

            IF @troubleshoot_performance = 1
            BEGIN

                EXEC sys.sp_executesql @troubleshoot_insert,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                SET STATISTICS XML ON;

            END;

            SELECT @sql += N'
SELECT
    qsws.plan_id,
    qsws.wait_category_desc,
    total_query_wait_time_ms =
        SUM(qsws.total_query_wait_time_ms),
    avg_query_wait_time_ms =
        SUM(qsws.avg_query_wait_time_ms),
    last_query_wait_time_ms =
        SUM(qsws.last_query_wait_time_ms),
    min_query_wait_time_ms =
        SUM(qsws.min_query_wait_time_ms),
    max_query_wait_time_ms =
        SUM(qsws.max_query_wait_time_ms)
FROM #query_store_runtime_stats AS qsrs
CROSS APPLY
(
    SELECT TOP (5)
        qsws.*
    FROM ' +    @database_name_quoted
                           + N'.sys.query_store_wait_stats AS qsws
    WHERE qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
    AND   qsws.plan_id = qsrs.plan_id
    AND   qsws.execution_type = 0
    AND   qsws.wait_category > 0
    ORDER BY qsws.avg_query_wait_time_ms DESC
) AS qsws
GROUP BY
    qsws.plan_id,
    qsws.wait_category_desc
HAVING SUM(qsws.min_query_wait_time_ms) >= 0.
OPTION(RECOMPILE);' + @nc10;

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            INSERT #query_store_wait_stats WITH (TABLOCK)
            (
                plan_id,
                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 @sql;

            IF @troubleshoot_performance = 1
            BEGIN

                SET STATISTICS XML OFF;

                EXEC sys.sp_executesql @troubleshoot_update,
                                       N'@current_table nvarchar(100)',
                                       @current_table;

                EXEC sys.sp_executesql @troubleshoot_info,
                                       N'@sql nvarchar(max),
            @current_table nvarchar(100)',
                                       @sql,
                                       @current_table;

            END;

        END;

        /*
Update things to get the context settings for each query
*/
        SELECT @current_table = N'updating context_settings in #query_store_runtime_stats';

        UPDATE qsrs
        SET qsrs.context_settings = SUBSTRING(
                                                 CASE
                                                     WHEN CONVERT(INT, qcs.set_options) & 1 = 1 THEN
                                                         ', ANSI_PADDING'
                                                     ELSE
                                                         ''
                                                 END + CASE
                                                           WHEN CONVERT(INT, qcs.set_options) & 8 = 8 THEN
                                                               ', CONCAT_NULL_YIELDS_NULL'
                                                           ELSE
                                                               ''
                                                       END + CASE
                                                                 WHEN CONVERT(INT, qcs.set_options) & 16 = 16 THEN
                                                                     ', ANSI_WARNINGS'
                                                                 ELSE
                                                                     ''
                                                             END
                                                 + CASE
                                                       WHEN CONVERT(INT, qcs.set_options) & 32 = 32 THEN
                                                           ', ANSI_NULLS'
                                                       ELSE
                                                           ''
                                                   END + CASE
                                                             WHEN CONVERT(INT, qcs.set_options) & 64 = 64 THEN
                                                                 ', QUOTED_IDENTIFIER'
                                                             ELSE
                                                                 ''
                                                         END
                                                 + CASE
                                                       WHEN CONVERT(INT, qcs.set_options) & 4096 = 4096 THEN
                                                           ', ARITH_ABORT'
                                                       ELSE
                                                           ''
                                                   END + CASE
                                                             WHEN CONVERT(INT, qcs.set_options) & 8192 = 8192 THEN
                                                                 ', NUMERIC_ROUNDABORT'
                                                             ELSE
                                                                 ''
                                                         END,
                                                 2,
                                                 256
                                             )
        FROM #query_store_runtime_stats AS qsrs
            JOIN #query_store_plan AS qsp
                ON qsrs.plan_id = qsp.plan_id
            JOIN #query_store_query AS qsq
                ON qsp.query_id = qsq.query_id
            JOIN #query_context_settings AS qcs
                ON qsq.context_settings_id = qcs.context_settings_id
        OPTION (RECOMPILE);

        /*
This is where we start returning results
*/
        IF EXISTS (SELECT 1 / 0 FROM #query_store_runtime_stats AS qsrs)
        BEGIN

            SELECT @sql = @isolation_level,
                   @current_table = N'selecting #query_store_wait_stats';

            SELECT @sql += N'
SELECT
    x.*
FROM
('          ;

            /*
    Expert mode returns more columns from runtime stats
    */
            IF (@expert_mode = 1 AND @format_output = 0)
            BEGIN

                SELECT @sql += N'
    SELECT
        source =
            ''runtime_stats'',
        qsp.query_id,
        qsrs.plan_id,
        qsp.all_plan_ids,
        qsrs.execution_type_desc,
        qsq.object_name,
        qsqt.query_sql_text,
        qsp.compatibility_level,
        query_plan = TRY_CONVERT(XML, qsp.query_plan),'
                               + CASE @new
                                     WHEN 1 THEN
                                         N'
        qsp.plan_forcing_type_desc,
        w.top_waits,'
                                     ELSE
                                         N''
                                 END
                               + N'
        qsrs.first_execution_time,
        qsrs.last_execution_time,
        qsrs.count_executions,
        qsrs.executions_per_second,
        qsrs.avg_duration_ms,
        qsrs.total_duration_ms,
        qsrs.last_duration_ms,
        qsrs.min_duration_ms,
        qsrs.max_duration_ms,
        qsrs.avg_cpu_time_ms,
        qsrs.total_cpu_time_ms,
        qsrs.last_cpu_time_ms,
        qsrs.min_cpu_time_ms,
        qsrs.max_cpu_time_ms,
        qsrs.avg_logical_io_reads_mb,
        qsrs.total_logical_io_reads_mb,
        qsrs.last_logical_io_reads_mb,
        qsrs.min_logical_io_reads_mb,
        qsrs.max_logical_io_reads_mb,
        qsrs.avg_logical_io_writes_mb,
        qsrs.total_logical_io_writes_mb,
        qsrs.last_logical_io_writes_mb,
        qsrs.min_logical_io_writes_mb,
        qsrs.max_logical_io_writes_mb,
        qsrs.avg_physical_io_reads_mb,
        qsrs.total_physical_io_reads_mb,
        qsrs.last_physical_io_reads_mb,
        qsrs.min_physical_io_reads_mb,
        qsrs.max_physical_io_reads_mb,
        qsrs.avg_clr_time_ms,
        qsrs.total_clr_time_ms,
        qsrs.last_clr_time_ms,
        qsrs.min_clr_time_ms,
        qsrs.max_clr_time_ms,
        qsrs.last_dop,
        qsrs.min_dop,
        qsrs.max_dop,
        qsrs.avg_query_max_used_memory_mb,
        qsrs.total_query_max_used_memory_mb,
        qsrs.last_query_max_used_memory_mb,
        qsrs.min_query_max_used_memory_mb,
        qsrs.max_query_max_used_memory_mb,
        qsrs.avg_rowcount,
        qsrs.total_rowcount,
        qsrs.last_rowcount,
        qsrs.min_rowcount,
        qsrs.max_rowcount,' + CASE @new
                                  WHEN 1 THEN
                                      N'
        qsrs.avg_num_physical_io_reads_mb,
        qsrs.total_num_physical_io_reads_mb,
        qsrs.last_num_physical_io_reads_mb,
        qsrs.min_num_physical_io_reads_mb,
        qsrs.max_num_physical_io_reads_mb,
        qsrs.avg_log_bytes_used_mb,
        qsrs.total_log_bytes_used_mb,
        qsrs.last_log_bytes_used_mb,
        qsrs.min_log_bytes_used_mb,
        qsrs.max_log_bytes_used_mb,
        qsrs.avg_tempdb_space_used_mb,
        qsrs.total_tempdb_space_used_mb,
        qsrs.last_tempdb_space_used_mb,
        qsrs.min_tempdb_space_used_mb,
        qsrs.max_tempdb_space_used_mb,'
                                  ELSE
                                      N''
                              END
                               + N'
        qsrs.context_settings,
        n =
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    qsrs.plan_id
                ORDER BY
                    ' + CASE @sort_order
                            WHEN 'cpu' THEN
                                N'qsrs.avg_cpu_time_ms'
                            WHEN 'logical reads' THEN
                                N'qsrs.avg_logical_io_reads_mb'
                            WHEN 'physical reads' THEN
                                N'qsrs.avg_physical_io_reads_mb'
                            WHEN 'writes' THEN
                                N'qsrs.avg_logical_io_writes_mb'
                            WHEN 'duration' THEN
                                N'qsrs.avg_duration_ms'
                            WHEN 'memory' THEN
                                N'qsrs.avg_query_max_used_memory_mb'
                            WHEN 'tempdb' THEN
                                CASE
                                    WHEN @new = 1 THEN
                                        N'qsrs.avg_tempdb_space_used_mb'
                                    ELSE
                                        N'qsrs.avg_cpu_time'
                                END
                            WHEN 'executions' THEN
                                N'qsrs.count_executions'
                            ELSE
                                N'qsrs.avg_cpu_time_ms'
                        END + N' DESC
            )'  ;

            END;

            /*
    Do we want to format things?
    */
            IF (@expert_mode = 1 AND @format_output = 1)
            BEGIN

                SELECT @sql += N'
    SELECT
        source =
            ''runtime_stats'',
        qsp.query_id,
        qsrs.plan_id,
        qsp.all_plan_ids,
        qsrs.execution_type_desc,
        qsq.object_name,
        qsqt.query_sql_text,
        qsp.compatibility_level,
        query_plan = TRY_CONVERT(XML, qsp.query_plan),'
                               + CASE @new
                                     WHEN 1 THEN
                                         N'
        qsp.plan_forcing_type_desc,
        w.top_waits,'
                                     ELSE
                                         N''
                                 END
                               + N'
        qsrs.first_execution_time,
        qsrs.last_execution_time,
        count_executions = FORMAT(qsrs.count_executions, ''N0''),
        executions_per_second = FORMAT(qsrs.executions_per_second, ''N0''),
        avg_duration_ms = FORMAT(qsrs.avg_duration_ms, ''N0''),
        total_duration_ms = FORMAT(qsrs.total_duration_ms, ''N0''),
        last_duration_ms = FORMAT(qsrs.last_duration_ms, ''N0''),
        min_duration_ms = FORMAT(qsrs.min_duration_ms, ''N0''),
        max_duration_ms = FORMAT(qsrs.max_duration_ms, ''N0''),
        avg_cpu_time_ms = FORMAT(qsrs.avg_cpu_time_ms, ''N0''),
        total_cpu_time_ms = FORMAT(qsrs.total_cpu_time_ms, ''N0''),
        last_cpu_time_ms = FORMAT(qsrs.last_cpu_time_ms, ''N0''),
        min_cpu_time_ms = FORMAT(qsrs.min_cpu_time_ms, ''N0''),
        max_cpu_time_ms = FORMAT(qsrs.max_cpu_time_ms, ''N0''),
        avg_logical_io_reads_mb = FORMAT(qsrs.avg_logical_io_reads_mb, ''N0''),
        total_logical_io_reads_mb = FORMAT(qsrs.total_logical_io_reads_mb, ''N0''),
        last_logical_io_reads_mb = FORMAT(qsrs.last_logical_io_reads_mb, ''N0''),
        min_logical_io_reads_mb = FORMAT(qsrs.min_logical_io_reads_mb, ''N0''),
        max_logical_io_reads_mb = FORMAT(qsrs.max_logical_io_reads_mb, ''N0''),
        avg_logical_io_writes_mb = FORMAT(qsrs.avg_logical_io_writes_mb, ''N0''),
        total_logical_io_writes_mb = FORMAT(qsrs.total_logical_io_writes_mb, ''N0''),
        last_logical_io_writes_mb = FORMAT(qsrs.last_logical_io_writes_mb, ''N0''),
        min_logical_io_writes_mb = FORMAT(qsrs.min_logical_io_writes_mb, ''N0''),
        max_logical_io_writes_mb = FORMAT(qsrs.max_logical_io_writes_mb, ''N0''),
        avg_physical_io_reads_mb = FORMAT(qsrs.avg_physical_io_reads_mb, ''N0''),
        total_physical_io_reads_mb = FORMAT(qsrs.total_physical_io_reads_mb, ''N0''),
        last_physical_io_reads_mb = FORMAT(qsrs.last_physical_io_reads_mb, ''N0''),
        min_physical_io_reads_mb = FORMAT(qsrs.min_physical_io_reads_mb, ''N0''),
        max_physical_io_reads_mb = FORMAT(qsrs.max_physical_io_reads_mb, ''N0''),
        avg_clr_time_ms = FORMAT(qsrs.avg_clr_time_ms, ''N0''),
        total_clr_time_ms = FORMAT(qsrs.total_clr_time_ms, ''N0''),
        last_clr_time_ms = FORMAT(qsrs.last_clr_time_ms, ''N0''),
        min_clr_time_ms = FORMAT(qsrs.min_clr_time_ms, ''N0''),
        max_clr_time_ms = FORMAT(qsrs.max_clr_time_ms, ''N0''),
        qsrs.last_dop,
        qsrs.min_dop,
        qsrs.max_dop,
        avg_query_max_used_memory_mb = FORMAT(qsrs.avg_query_max_used_memory_mb, ''N0''),
        total_query_max_used_memory_mb = FORMAT(qsrs.total_query_max_used_memory_mb, ''N0''),
        last_query_max_used_memory_mb = FORMAT(qsrs.last_query_max_used_memory_mb, ''N0''),
        min_query_max_used_memory_mb = FORMAT(qsrs.min_query_max_used_memory_mb, ''N0''),
        max_query_max_used_memory_mb = FORMAT(qsrs.max_query_max_used_memory_mb, ''N0''),
        avg_rowcount = FORMAT(qsrs.avg_rowcount, ''N0''),
        total_rowcount = FORMAT(qsrs.total_rowcount, ''N0''),
        last_rowcount = FORMAT(qsrs.last_rowcount, ''N0''),
        min_rowcount = FORMAT(qsrs.min_rowcount, ''N0''),
        max_rowcount = FORMAT(qsrs.max_rowcount, ''N0''),'
                               + CASE @new
                                     WHEN 1 THEN
                                         CONVERT(
                                                    NVARCHAR(MAX),
                                                    N'
        avg_num_physical_io_reads_mb = FORMAT(qsrs.avg_num_physical_io_reads_mb, ''N0''),
        total_num_physical_io_reads_mb = FORMAT(qsrs.total_num_physical_io_reads_mb, ''N0''),
        last_num_physical_io_reads_mb = FORMAT(qsrs.last_num_physical_io_reads_mb, ''N0''),
        min_num_physical_io_reads_mb = FORMAT(qsrs.min_num_physical_io_reads_mb, ''N0''),
        max_num_physical_io_reads_mb = FORMAT(qsrs.max_num_physical_io_reads_mb, ''N0''),
        avg_log_bytes_used_mb = FORMAT(qsrs.avg_log_bytes_used_mb, ''N0''),
        total_log_bytes_used_mb = FORMAT(qsrs.total_log_bytes_used_mb, ''N0''),
        last_log_bytes_used_mb = FORMAT(qsrs.last_log_bytes_used_mb, ''N0''),
        min_log_bytes_used_mb = FORMAT(qsrs.min_log_bytes_used_mb, ''N0''),
        max_log_bytes_used_mb = FORMAT(qsrs.max_log_bytes_used_mb, ''N0''),
        avg_tempdb_space_used_mb = FORMAT(qsrs.avg_tempdb_space_used_mb, ''N0''),
        total_tempdb_space_used_mb = FORMAT(qsrs.total_tempdb_space_used_mb, ''N0''),
        last_tempdb_space_used_mb = FORMAT(qsrs.last_tempdb_space_used_mb, ''N0''),
        min_tempdb_space_used_mb = FORMAT(qsrs.min_tempdb_space_used_mb, ''N0''),
        max_tempdb_space_used_mb = FORMAT(qsrs.max_tempdb_space_used_mb, ''N0''),'
                                                )
                                     ELSE
                                         N''
                                 END
                               + N'
        qsrs.context_settings,
        n =
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    qsrs.plan_id
                ORDER BY
                    ' + CASE @sort_order
                            WHEN 'cpu' THEN
                                N'qsrs.avg_cpu_time_ms'
                            WHEN 'logical reads' THEN
                                N'qsrs.avg_logical_io_reads_mb'
                            WHEN 'physical reads' THEN
                                N'qsrs.avg_physical_io_reads_mb'
                            WHEN 'writes' THEN
                                N'qsrs.avg_logical_io_writes_mb'
                            WHEN 'duration' THEN
                                N'qsrs.avg_duration_ms'
                            WHEN 'memory' THEN
                                N'qsrs.avg_query_max_used_memory_mb'
                            WHEN 'tempdb' THEN
                                CASE
                                    WHEN @new = 1 THEN
                                        N'qsrs.avg_tempdb_space_used_mb'
                                    ELSE
                                        N'qsrs.avg_cpu_time'
                                END
                            WHEN 'executions' THEN
                                N'qsrs.count_executions'
                            ELSE
                                N'qsrs.avg_cpu_time_ms'
                        END + N' DESC
            )'  ;

            END;

            /*
    For non-experts only!
    */
            IF (@expert_mode = 0 AND @format_output = 0)
            BEGIN

                SELECT @sql += N'
    SELECT
        source =
            ''runtime_stats'',
        qsp.query_id,
        qsrs.plan_id,
        qsp.all_plan_ids,
        qsrs.execution_type_desc,
        qsq.object_name,
        qsqt.query_sql_text,
        qsp.compatibility_level,
        query_plan = TRY_CONVERT(XML, qsp.query_plan),'
                               + CASE @new
                                     WHEN 1 THEN
                                         N'
        qsp.plan_forcing_type_desc,
        w.top_waits,'
                                     ELSE
                                         N''
                                 END
                               + N'
        qsrs.first_execution_time,
        qsrs.last_execution_time,
        qsrs.count_executions,
        qsrs.executions_per_second,
        qsrs.avg_duration_ms,
        qsrs.total_duration_ms,
        qsrs.avg_cpu_time_ms,
        qsrs.total_cpu_time_ms,
        qsrs.avg_logical_io_reads_mb,
        qsrs.total_logical_io_reads_mb,
        qsrs.avg_logical_io_writes_mb,
        qsrs.total_logical_io_writes_mb,
        qsrs.avg_physical_io_reads_mb,
        qsrs.total_physical_io_reads_mb,
        qsrs.avg_clr_time_ms,
        qsrs.total_clr_time_ms,
        qsrs.min_dop,
        qsrs.max_dop,
        qsrs.avg_query_max_used_memory_mb,
        qsrs.total_query_max_used_memory_mb,
        qsrs.avg_rowcount,
        qsrs.total_rowcount,' + CASE @new
                                    WHEN 1 THEN
                                        N'
        qsrs.avg_num_physical_io_reads_mb,
        qsrs.total_num_physical_io_reads_mb,
        qsrs.avg_log_bytes_used_mb,
        qsrs.total_log_bytes_used_mb,
        qsrs.avg_tempdb_space_used_mb,
        qsrs.total_tempdb_space_used_mb,'
                                    ELSE
                                        N''
                                END
                               + N'
        qsrs.context_settings,
        n =
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    qsrs.plan_id
                ORDER BY
                    ' + CASE @sort_order
                            WHEN 'cpu' THEN
                                N'qsrs.avg_cpu_time_ms'
                            WHEN 'logical reads' THEN
                                N'qsrs.avg_logical_io_reads_mb'
                            WHEN 'physical reads' THEN
                                N'qsrs.avg_physical_io_reads_mb'
                            WHEN 'writes' THEN
                                N'qsrs.avg_logical_io_writes_mb'
                            WHEN 'duration' THEN
                                N'qsrs.avg_duration_ms'
                            WHEN 'memory' THEN
                                N'qsrs.avg_query_max_used_memory_mb'
                            WHEN 'tempdb' THEN
                                CASE
                                    WHEN @new = 1 THEN
                                        N'qsrs.avg_tempdb_space_used_mb'
                                    ELSE
                                        N'qsrs.avg_cpu_time'
                                END
                            WHEN 'executions' THEN
                                N'qsrs.count_executions'
                            ELSE
                                N'qsrs.avg_cpu_time_ms'
                        END + N' DESC
            )'  ;

            END;

            /*
    Formatted but not still not expert output
    */
            IF (@expert_mode = 0 AND @format_output = 1)
            BEGIN

                SELECT @sql += N'
    SELECT
        source =
            ''runtime_stats'',
        qsp.query_id,
        qsrs.plan_id,
        qsp.all_plan_ids,
        qsrs.execution_type_desc,
        qsq.object_name,
        qsqt.query_sql_text,
        qsp.compatibility_level,
        query_plan = TRY_CONVERT(XML, qsp.query_plan),'
                               + CASE @new
                                     WHEN 1 THEN
                                         N'
        qsp.plan_forcing_type_desc,
        w.top_waits,'
                                     ELSE
                                         N''
                                 END
                               + N'
        qsrs.first_execution_time,
        qsrs.last_execution_time,
        count_executions = FORMAT(qsrs.count_executions, ''N0''),
        executions_per_second = FORMAT(qsrs.executions_per_second, ''N0''),
        avg_duration_ms = FORMAT(qsrs.avg_duration_ms, ''N0''),
        total_duration_ms = FORMAT(qsrs.total_duration_ms, ''N0''),
        avg_cpu_time_ms = FORMAT(qsrs.avg_cpu_time_ms, ''N0''),
        total_cpu_time_ms = FORMAT(qsrs.total_cpu_time_ms, ''N0''),
        avg_logical_io_reads_mb = FORMAT(qsrs.avg_logical_io_reads_mb, ''N0''),
        total_logical_io_reads_mb = FORMAT(qsrs.total_logical_io_reads_mb, ''N0''),
        avg_logical_io_writes_mb = FORMAT(qsrs.avg_logical_io_writes_mb, ''N0''),
        total_logical_io_writes_mb = FORMAT(qsrs.total_logical_io_writes_mb, ''N0''),
        avg_physical_io_reads_mb = FORMAT(qsrs.avg_physical_io_reads_mb, ''N0''),
        total_physical_io_reads_mb = FORMAT(qsrs.total_physical_io_reads_mb, ''N0''),
        avg_clr_time_ms = FORMAT(qsrs.avg_clr_time_ms, ''N0''),
        total_clr_time_ms = FORMAT(qsrs.total_clr_time_ms, ''N0''),
        min_dop = FORMAT(qsrs.min_dop, ''N0''),
        max_dop = FORMAT(qsrs.max_dop, ''N0''),
        avg_query_max_used_memory_mb = FORMAT(qsrs.avg_query_max_used_memory_mb, ''N0''),
        total_query_max_used_memory_mb = FORMAT(qsrs.total_query_max_used_memory_mb, ''N0''),
        avg_rowcount = FORMAT(qsrs.avg_rowcount, ''N0''),
        total_rowcount = FORMAT(qsrs.total_rowcount, ''N0''),'
                               + CASE @new
                                     WHEN 1 THEN
                                         N'
        avg_num_physical_io_reads_mb = FORMAT(qsrs.avg_num_physical_io_reads_mb, ''N0''),
        total_num_physical_io_reads_mb = FORMAT(qsrs.total_num_physical_io_reads_mb, ''N0''),
        avg_log_bytes_used_mb = FORMAT(qsrs.avg_log_bytes_used_mb, ''N0''),
        total_log_bytes_used_mb = FORMAT(qsrs.total_log_bytes_used_mb, ''N0''),
        avg_tempdb_space_used_mb = FORMAT(qsrs.avg_tempdb_space_used_mb, ''N0''),
        total_tempdb_space_used_mb = FORMAT(qsrs.total_tempdb_space_used_mb, ''N0''),'
                                     ELSE
                                         N''
                                 END
                               + N'
        qsrs.context_settings,
        n =
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    qsrs.plan_id
                ORDER BY
                    ' + CASE @sort_order
                            WHEN 'cpu' THEN
                                N'qsrs.avg_cpu_time_ms'
                            WHEN 'logical reads' THEN
                                N'qsrs.avg_logical_io_reads_mb'
                            WHEN 'physical reads' THEN
                                N'qsrs.avg_physical_io_reads_mb'
                            WHEN 'writes' THEN
                                N'qsrs.avg_logical_io_writes_mb'
                            WHEN 'duration' THEN
                                N'qsrs.avg_duration_ms'
                            WHEN 'memory' THEN
                                N'qsrs.avg_query_max_used_memory_mb'
                            WHEN 'tempdb' THEN
                                CASE
                                    WHEN @new = 1 THEN
                                        N'qsrs.avg_tempdb_space_used_mb'
                                    ELSE
                                        N'qsrs.avg_cpu_time'
                                END
                            WHEN 'executions' THEN
                                N'qsrs.count_executions'
                            ELSE
                                N'qsrs.avg_cpu_time_ms'
                        END + N' DESC
            )'  ;

            END;

            /*
    Add on the from and stuff
    */
            SELECT @sql += N'
    FROM #query_store_runtime_stats AS qsrs
    CROSS APPLY
    (
        SELECT
            x.*
        FROM
        (
            SELECT
                qsp.*,
                pn =
                    ROW_NUMBER() OVER
                    (
                        PARTITION BY
                            qsp.query_plan_hash
                        ORDER BY
                            qsp.last_execution_time DESC
                    )
            FROM #query_store_plan AS qsp
            WHERE qsp.plan_id = qsrs.plan_id
        ) AS x
        WHERE x.pn = 1
    ) AS qsp
    CROSS APPLY
    (
        SELECT TOP (1)
            qsqt.*
        FROM #query_store_query AS qsq
        JOIN #query_store_query_text AS qsqt
            ON qsqt.query_text_id = qsq.query_text_id
        WHERE qsq.query_id = qsp.query_id
        ORDER BY qsq.last_execution_time DESC
    ) AS qsqt
    CROSS APPLY
    (
        SELECT TOP (1)
            qsq.*
        FROM #query_store_query AS qsq
        WHERE qsq.query_id = qsp.query_id
        ORDER BY qsq.last_execution_time DESC
    ) AS qsq';

            /*
    Get wait stats if we can
    */
            IF (@new = 1 AND @format_output = 0)
            BEGIN

                SELECT @sql += N'
    CROSS APPLY
    (
        SELECT TOP (1)
            top_waits =
                STUFF
                (
                    (
                       SELECT TOP (5)
                            '', '' +
                            qsws.wait_category_desc +
                            '' ('' +
                            CONVERT
                            (
                                varchar(20),
                                SUM
                                (
                                    CONVERT
                                    (
                                        bigint,
                                        qsws.avg_query_wait_time_ms
                                    )
                                )
                            ) +
                            '' ms)''
                       FROM #query_store_wait_stats AS qsws
                       WHERE qsws.plan_id = qsrs.plan_id
                       GROUP BY qsws.wait_category_desc
                       ORDER BY SUM(qsws.avg_query_wait_time_ms) DESC
                       FOR XML PATH(''''), TYPE
                    ).value(''./text()[1]'', ''varchar(max)''),
                    1,
                    2,
                    ''''
                )
    ) AS w'     ;

            END;

            IF (@new = 1 AND @format_output = 1)
            BEGIN

                SELECT @sql += N'
    CROSS APPLY
    (
        SELECT TOP (1)
            top_waits =
                STUFF
                (
                    (
                       SELECT TOP (5)
                            '', '' +
                            qsws.wait_category_desc +
                            '' ('' +
                            FORMAT
                            (
                                SUM
                                (
                                    CONVERT
                                    (
                                        bigint,
                                        qsws.avg_query_wait_time_ms
                                    )
                                ), ''N0''
                            ) +
                            '' ms)''
                       FROM #query_store_wait_stats AS qsws
                       WHERE qsws.plan_id = qsrs.plan_id
                       GROUP BY qsws.wait_category_desc
                       ORDER BY SUM(qsws.avg_query_wait_time_ms) DESC
                       FOR XML PATH(''''), TYPE
                    ).value(''./text()[1]'', ''varchar(max)''),
                    1,
                    2,
                    ''''
                )
    ) AS w'     ;

            END;

            SELECT @sql += N'
) AS x
WHERE x.n = 1
ORDER BY ' +    CASE @format_output
                    WHEN 0 THEN
                        CASE @sort_order
                            WHEN 'cpu' THEN
                                N'x.avg_cpu_time_ms'
                            WHEN 'logical reads' THEN
                                N'x.avg_logical_io_reads_mb'
                            WHEN 'physical reads' THEN
                                N'x.avg_physical_io_reads_mb'
                            WHEN 'writes' THEN
                                N'x.avg_logical_io_writes_mb'
                            WHEN 'duration' THEN
                                N'x.avg_duration_ms'
                            WHEN 'memory' THEN
                                N'x.avg_query_max_used_memory_mb'
                            WHEN 'tempdb' THEN
                                CASE
                                    WHEN @new = 1 THEN
                                        N'x.avg_tempdb_space_used_mb'
                                    ELSE
                                        N'x.avg_cpu_time'
                                END
                            WHEN 'executions' THEN
                                N'x.count_executions'
                            ELSE
                                N'x.avg_cpu_time_ms'
                        END
                    WHEN 1 THEN
                        CASE @sort_order
                            WHEN 'cpu' THEN
                                N'CONVERT(money, x.avg_cpu_time_ms)'
                            WHEN 'logical reads' THEN
                                N'CONVERT(money, x.avg_logical_io_reads_mb)'
                            WHEN 'physical reads' THEN
                                N'CONVERT(money, x.avg_physical_io_reads_mb)'
                            WHEN 'writes' THEN
                                N'CONVERT(money, x.avg_logical_io_writes_mb)'
                            WHEN 'duration' THEN
                                N'CONVERT(money, x.avg_duration_ms)'
                            WHEN 'memory' THEN
                                N'CONVERT(money, x.avg_query_max_used_memory_mb)'
                            WHEN 'tempdb' THEN
                                CASE
                                    WHEN @new = 1 THEN
                                        N'CONVERT(money, x.avg_tempdb_space_used_mb)'
                                    ELSE
                                        N'CONVERT(money, x.avg_cpu_time)'
                                END
                            WHEN 'executions' THEN
                                N'CONVERT(money, x.count_executions)'
                            ELSE
                                N'CONVERT(money, x.avg_cpu_time_ms)'
                        END
                END + N' DESC
OPTION(RECOMPILE);' + @nc10;

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT SUBSTRING(@sql, 0, 4000);
                PRINT SUBSTRING(@sql, 4000, 8000);
            END;


            EXEC sys.sp_executesql @sql;

        END;
        ELSE
        BEGIN
            SELECT result = '#query_store_runtime_stats is empty';
        END;

        /*
Return special things, unformatted
*/
        IF (@expert_mode = 1 AND @format_output = 0)
        BEGIN

            IF EXISTS (SELECT 1 / 0 FROM #query_store_query)
            BEGIN

                SELECT @current_table = N'selecting compilation stats';

                SELECT x.*
                FROM
                (
                    SELECT source = 'compilation_stats',
                           qsq.query_id,
                           qsq.object_name,
                           qsq.query_text_id,
                           qsq.query_parameterization_type_desc,
                           qsq.initial_compile_start_time,
                           qsq.last_compile_start_time,
                           qsq.last_execution_time,
                           qsq.count_compiles,
                           qsq.avg_compile_duration_ms,
                           qsq.total_compile_duration_ms,
                           qsq.last_compile_duration_ms,
                           qsq.avg_bind_duration_ms,
                           qsq.total_bind_duration_ms,
                           qsq.last_bind_duration_ms,
                           qsq.avg_bind_cpu_time_ms,
                           qsq.total_bind_cpu_time_ms,
                           qsq.last_bind_cpu_time_ms,
                           qsq.avg_optimize_duration_ms,
                           qsq.total_optimize_duration_ms,
                           qsq.last_optimize_duration_ms,
                           qsq.avg_optimize_cpu_time_ms,
                           qsq.total_optimize_cpu_time_ms,
                           qsq.last_optimize_cpu_time_ms,
                           qsq.avg_compile_memory_mb,
                           qsq.total_compile_memory_mb,
                           qsq.last_compile_memory_mb,
                           qsq.max_compile_memory_mb,
                           qsq.query_hash,
                           qsq.batch_sql_handle,
                           qsqt.statement_sql_handle,
                           qsq.last_compile_batch_sql_handle,
                           qsq.last_compile_batch_offset_start,
                           qsq.last_compile_batch_offset_end,
                           ROW_NUMBER() OVER (PARTITION BY qsq.query_id, qsq.query_text_id ORDER BY qsq.query_id) AS n
                    FROM #query_store_query AS qsq
                        CROSS APPLY
                    (
                        SELECT TOP (1)
                               qsqt.*
                        FROM #query_store_query_text AS qsqt
                        WHERE qsqt.query_text_id = qsq.query_text_id
                    ) AS qsqt
                ) AS x
                WHERE x.n = 1
                ORDER BY x.query_id
                OPTION (RECOMPILE);

            END;
            ELSE
            BEGIN
                SELECT result = '#query_store_query is empty';
            END;

            IF @rc > 0
            BEGIN

                SELECT @current_table = N'selecting resource stats';

                SELECT source = 'resource_stats',
                       qsq.query_id,
                       qsq.object_name,
                       qsqt.total_grant_mb,
                       qsqt.last_grant_mb,
                       qsqt.min_grant_mb,
                       qsqt.max_grant_mb,
                       qsqt.total_used_grant_mb,
                       qsqt.last_used_grant_mb,
                       qsqt.min_used_grant_mb,
                       qsqt.max_used_grant_mb,
                       qsqt.total_ideal_grant_mb,
                       qsqt.last_ideal_grant_mb,
                       qsqt.min_ideal_grant_mb,
                       qsqt.max_ideal_grant_mb,
                       qsqt.total_reserved_threads,
                       qsqt.last_reserved_threads,
                       qsqt.min_reserved_threads,
                       qsqt.max_reserved_threads,
                       qsqt.total_used_threads,
                       qsqt.last_used_threads,
                       qsqt.min_used_threads,
                       qsqt.max_used_threads
                FROM #query_store_query AS qsq
                    JOIN #query_store_query_text AS qsqt
                        ON qsq.query_text_id = qsqt.query_text_id
                WHERE (
                          qsqt.total_grant_mb IS NOT NULL
                          OR qsqt.total_reserved_threads IS NOT NULL
                      )
                ORDER BY qsq.query_id
                OPTION (RECOMPILE);

            END;
            ELSE
            BEGIN
                SELECT result = '#dm_exec_query_stats is empty';
            END;

            IF @new = 1
            BEGIN

                IF EXISTS (SELECT 1 / 0 FROM #query_store_wait_stats)
                BEGIN

                    SELECT @current_table = N'selecting wait stats by query';

                    SELECT DISTINCT
                           source = 'query_store_wait_stats_by_query',
                           qsws.plan_id,
                           x.object_name,
                           qsws.wait_category_desc,
                           qsws.total_query_wait_time_ms,
                           total_query_duration_ms = x.total_duration_ms,
                           qsws.avg_query_wait_time_ms,
                           avg_query_duration_ms = x.avg_duration_ms,
                           qsws.last_query_wait_time_ms,
                           last_query_duration_ms = x.last_duration_ms,
                           qsws.min_query_wait_time_ms,
                           min_query_duration_ms = x.min_duration_ms,
                           qsws.max_query_wait_time_ms,
                           max_query_duration_ms = x.max_duration_ms
                    FROM #query_store_wait_stats AS qsws
                        CROSS APPLY
                    (
                        SELECT qsrs.avg_duration_ms,
                               qsrs.last_duration_ms,
                               qsrs.min_duration_ms,
                               qsrs.max_duration_ms,
                               qsrs.total_duration_ms,
                               qsq.object_name
                        FROM #query_store_runtime_stats AS qsrs
                            JOIN #query_store_plan AS qsp
                                ON qsrs.plan_id = qsp.plan_id
                            JOIN #query_store_query AS qsq
                                ON qsp.query_id = qsq.query_id
                        WHERE qsws.plan_id = qsrs.plan_id
                    ) AS x
                    ORDER BY qsws.plan_id,
                             qsws.total_query_wait_time_ms DESC
                    OPTION (RECOMPILE);

                    SELECT @current_table = N'selecting wait stats in total';

                    SELECT source = 'query_store_wait_stats_total',
                           qsws.wait_category_desc,
                           total_query_wait_time_ms = SUM(qsws.total_query_wait_time_ms),
                           total_query_duration_ms = SUM(x.total_duration_ms),
                           avg_query_wait_time_ms = SUM(qsws.avg_query_wait_time_ms),
                           avg_query_duration_ms = SUM(x.avg_duration_ms),
                           last_query_wait_time_ms = SUM(qsws.last_query_wait_time_ms),
                           last_query_duration_ms = SUM(x.last_duration_ms),
                           min_query_wait_time_ms = SUM(qsws.min_query_wait_time_ms),
                           min_query_duration_ms = SUM(x.min_duration_ms),
                           max_query_wait_time_ms = SUM(qsws.max_query_wait_time_ms),
                           max_query_duration_ms = SUM(x.max_duration_ms)
                    FROM #query_store_wait_stats AS qsws
                        CROSS APPLY
                    (
                        SELECT qsrs.avg_duration_ms,
                               qsrs.last_duration_ms,
                               qsrs.min_duration_ms,
                               qsrs.max_duration_ms,
                               qsrs.total_duration_ms,
                               qsq.object_name
                        FROM #query_store_runtime_stats AS qsrs
                            JOIN #query_store_plan AS qsp
                                ON qsrs.plan_id = qsp.plan_id
                            JOIN #query_store_query AS qsq
                                ON qsp.query_id = qsq.query_id
                        WHERE qsws.plan_id = qsrs.plan_id
                    ) AS x
                    GROUP BY qsws.wait_category_desc
                    ORDER BY SUM(qsws.total_query_wait_time_ms) DESC
                    OPTION (RECOMPILE);

                END;
                ELSE
                BEGIN
                    SELECT result = '#query_store_wait_stats is empty' + CASE
                                                                             WHEN
                                                                             (
                                                                                 @product_version = 13
                                                                                 AND @azure = 0
                                                                             ) THEN
                                                                                 ' because it''s not available < 2017'
                                                                             ELSE
                                                                                 ''
                                                                         END;
                END;

            END;

            SELECT @current_table = N'selecting query store options',
                   @sql = N'';

            SELECT @sql += N'
    SELECT
        source =
            ''query_store_options'',
        dqso.desired_state_desc,
        dqso.actual_state_desc,
        dqso.readonly_reason,
        dqso.current_storage_size_mb,
        dqso.flush_interval_seconds,
        dqso.interval_length_minutes,
        dqso.max_storage_size_mb,
        dqso.stale_query_threshold_days,
        dqso.max_plans_per_query,
        dqso.query_capture_mode_desc,';

            IF (@azure = 1 OR @product_version > 13)
            BEGIN
                SELECT @sql += N'
        dqso.wait_stats_capture_mode_desc,';
            END;

            IF (@azure = 1 OR @product_version > 14)
            BEGIN
                SELECT @sql += N'
        dqso.capture_policy_execution_count,
        dqso.capture_policy_total_compile_cpu_time_ms,
        dqso.capture_policy_total_execution_cpu_time_ms,
        dqso.capture_policy_stale_threshold_hours,';
            END;

            SELECT @sql += N'
    dqso.size_based_cleanup_mode_desc
    FROM #database_query_store_options AS dqso
    OPTION(RECOMPILE);';

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            EXEC sys.sp_executesql @sql;

        END;

        /*
Return special things, formatted
*/
        IF (@expert_mode = 1 AND @format_output = 1)
        BEGIN

            IF EXISTS (SELECT 1 / 0 FROM #query_store_query)
            BEGIN

                SELECT @current_table = N'selecting compilation stats';

                SELECT x.*
                FROM
                (
                    SELECT source = 'compilation_stats',
                           qsq.query_id,
                           qsq.object_name,
                           qsq.query_text_id,
                           qsq.query_parameterization_type_desc,
                           qsq.initial_compile_start_time,
                           qsq.last_compile_start_time,
                           qsq.last_execution_time,
                           count_compiles = FORMAT(qsq.count_compiles, 'N0'),
                           avg_compile_duration_ms = FORMAT(qsq.avg_compile_duration_ms, 'N0'),
                           total_compile_duration_ms = FORMAT(qsq.total_compile_duration_ms, 'N0'),
                           last_compile_duration_ms = FORMAT(qsq.last_compile_duration_ms, 'N0'),
                           avg_bind_duration_ms = FORMAT(qsq.avg_bind_duration_ms, 'N0'),
                           total_bind_duration_ms = FORMAT(qsq.total_bind_duration_ms, 'N0'),
                           last_bind_duration_ms = FORMAT(qsq.last_bind_duration_ms, 'N0'),
                           avg_bind_cpu_time_ms = FORMAT(qsq.avg_bind_cpu_time_ms, 'N0'),
                           total_bind_cpu_time_ms = FORMAT(qsq.total_bind_cpu_time_ms, 'N0'),
                           last_bind_cpu_time_ms = FORMAT(qsq.last_bind_cpu_time_ms, 'N0'),
                           avg_optimize_duration_ms = FORMAT(qsq.avg_optimize_duration_ms, 'N0'),
                           total_optimize_duration_ms = FORMAT(qsq.total_optimize_duration_ms, 'N0'),
                           last_optimize_duration_ms = FORMAT(qsq.last_optimize_duration_ms, 'N0'),
                           avg_optimize_cpu_time_ms = FORMAT(qsq.avg_optimize_cpu_time_ms, 'N0'),
                           total_optimize_cpu_time_ms = FORMAT(qsq.total_optimize_cpu_time_ms, 'N0'),
                           last_optimize_cpu_time_ms = FORMAT(qsq.last_optimize_cpu_time_ms, 'N0'),
                           avg_compile_memory_mb = FORMAT(qsq.avg_compile_memory_mb, 'N0'),
                           total_compile_memory_mb = FORMAT(qsq.total_compile_memory_mb, 'N0'),
                           last_compile_memory_mb = FORMAT(qsq.last_compile_memory_mb, 'N0'),
                           max_compile_memory_mb = FORMAT(qsq.max_compile_memory_mb, 'N0'),
                           qsq.query_hash,
                           qsq.batch_sql_handle,
                           qsqt.statement_sql_handle,
                           qsq.last_compile_batch_sql_handle,
                           qsq.last_compile_batch_offset_start,
                           qsq.last_compile_batch_offset_end,
                           ROW_NUMBER() OVER (PARTITION BY qsq.query_id, qsq.query_text_id ORDER BY qsq.query_id) AS n
                    FROM #query_store_query AS qsq
                        CROSS APPLY
                    (
                        SELECT TOP (1)
                               qsqt.*
                        FROM #query_store_query_text AS qsqt
                        WHERE qsqt.query_text_id = qsq.query_text_id
                    ) AS qsqt
                ) AS x
                WHERE x.n = 1
                ORDER BY x.query_id
                OPTION (RECOMPILE);

            END;
            ELSE
            BEGIN
                SELECT result = '#query_store_query is empty';
            END;

            IF @rc > 0
            BEGIN

                SELECT @current_table = N'selecting resource stats';

                SELECT source = 'resource_stats',
                       qsq.query_id,
                       qsq.object_name,
                       total_grant_mb = FORMAT(qsqt.total_grant_mb, 'N0'),
                       last_grant_mb = FORMAT(qsqt.last_grant_mb, 'N0'),
                       min_grant_mb = FORMAT(qsqt.min_grant_mb, 'N0'),
                       max_grant_mb = FORMAT(qsqt.max_grant_mb, 'N0'),
                       total_used_grant_mb = FORMAT(qsqt.total_used_grant_mb, 'N0'),
                       last_used_grant_mb = FORMAT(qsqt.last_used_grant_mb, 'N0'),
                       min_used_grant_mb = FORMAT(qsqt.min_used_grant_mb, 'N0'),
                       max_used_grant_mb = FORMAT(qsqt.max_used_grant_mb, 'N0'),
                       total_ideal_grant_mb = FORMAT(qsqt.total_ideal_grant_mb, 'N0'),
                       last_ideal_grant_mb = FORMAT(qsqt.last_ideal_grant_mb, 'N0'),
                       min_ideal_grant_mb = FORMAT(qsqt.min_ideal_grant_mb, 'N0'),
                       max_ideal_grant_mb = FORMAT(qsqt.max_ideal_grant_mb, 'N0'),
                       qsqt.total_reserved_threads,
                       qsqt.last_reserved_threads,
                       qsqt.min_reserved_threads,
                       qsqt.max_reserved_threads,
                       qsqt.total_used_threads,
                       qsqt.last_used_threads,
                       qsqt.min_used_threads,
                       qsqt.max_used_threads
                FROM #query_store_query AS qsq
                    JOIN #query_store_query_text AS qsqt
                        ON qsq.query_text_id = qsqt.query_text_id
                WHERE (
                          qsqt.total_grant_mb IS NOT NULL
                          OR qsqt.total_reserved_threads IS NOT NULL
                      )
                ORDER BY qsq.query_id
                OPTION (RECOMPILE);

            END;
            ELSE
            BEGIN
                SELECT result = '#dm_exec_query_stats is empty';
            END;

            IF @new = 1
            BEGIN

                IF EXISTS (SELECT 1 / 0 FROM #query_store_wait_stats)
                BEGIN

                    SELECT @current_table = N'selecting wait stats by query';

                    SELECT source = 'query_store_wait_stats_by_query',
                           qsws.plan_id,
                           x.object_name,
                           qsws.wait_category_desc,
                           total_query_wait_time_ms = FORMAT(qsws.total_query_wait_time_ms, 'N0'),
                           total_query_duration_ms = FORMAT(x.total_duration_ms, 'N0'),
                           avg_query_wait_time_ms = FORMAT(qsws.avg_query_wait_time_ms, 'N0'),
                           avg_query_duration_ms = FORMAT(x.avg_duration_ms, 'N0'),
                           last_query_wait_time_ms = FORMAT(qsws.last_query_wait_time_ms, 'N0'),
                           last_query_duration_ms = FORMAT(x.last_duration_ms, 'N0'),
                           min_query_wait_time_ms = FORMAT(qsws.min_query_wait_time_ms, 'N0'),
                           min_query_duration_ms = FORMAT(x.min_duration_ms, 'N0'),
                           max_query_wait_time_ms = FORMAT(qsws.max_query_wait_time_ms, 'N0'),
                           max_query_duration_ms = FORMAT(x.max_duration_ms, 'N0')
                    FROM #query_store_wait_stats AS qsws
                        CROSS APPLY
                    (
                        SELECT DISTINCT
                               qsrs.avg_duration_ms,
                               qsrs.last_duration_ms,
                               qsrs.min_duration_ms,
                               qsrs.max_duration_ms,
                               qsrs.total_duration_ms,
                               qsq.object_name
                        FROM #query_store_runtime_stats AS qsrs
                            JOIN #query_store_plan AS qsp
                                ON qsrs.plan_id = qsp.plan_id
                            JOIN #query_store_query AS qsq
                                ON qsp.query_id = qsq.query_id
                        WHERE qsws.plan_id = qsrs.plan_id
                    ) AS x
                    ORDER BY qsws.plan_id,
                             qsws.total_query_wait_time_ms DESC
                    OPTION (RECOMPILE);

                    SELECT @current_table = N'selecting wait stats in total';

                    SELECT source = 'query_store_wait_stats_total',
                           qsws.wait_category_desc,
                           total_query_wait_time_ms = FORMAT(SUM(qsws.total_query_wait_time_ms), 'N0'),
                           total_query_duration_ms = FORMAT(SUM(x.total_duration_ms), 'N0'),
                           avg_query_wait_time_ms = FORMAT(SUM(qsws.avg_query_wait_time_ms), 'N0'),
                           avg_query_duration_ms = FORMAT(SUM(x.avg_duration_ms), 'N0'),
                           last_query_wait_time_ms = FORMAT(SUM(qsws.last_query_wait_time_ms), 'N0'),
                           last_query_duration_ms = FORMAT(SUM(x.last_duration_ms), 'N0'),
                           min_query_wait_time_ms = FORMAT(SUM(qsws.min_query_wait_time_ms), 'N0'),
                           min_query_duration_ms = FORMAT(SUM(x.min_duration_ms), 'N0'),
                           max_query_wait_time_ms = FORMAT(SUM(qsws.max_query_wait_time_ms), 'N0'),
                           max_query_duration_ms = FORMAT(SUM(x.max_duration_ms), 'N0')
                    FROM #query_store_wait_stats AS qsws
                        CROSS APPLY
                    (
                        SELECT qsrs.avg_duration_ms,
                               qsrs.last_duration_ms,
                               qsrs.min_duration_ms,
                               qsrs.max_duration_ms,
                               qsrs.total_duration_ms,
                               qsq.object_name
                        FROM #query_store_runtime_stats AS qsrs
                            JOIN #query_store_plan AS qsp
                                ON qsrs.plan_id = qsp.plan_id
                            JOIN #query_store_query AS qsq
                                ON qsp.query_id = qsq.query_id
                        WHERE qsws.plan_id = qsrs.plan_id
                    ) AS x
                    GROUP BY qsws.wait_category_desc
                    ORDER BY SUM(qsws.total_query_wait_time_ms) DESC
                    OPTION (RECOMPILE);

                END;

            END;
            ELSE
            BEGIN
                SELECT result = '#query_store_wait_stats is empty'
                                + CASE
                                      WHEN
                                      (
                                          @product_version = 13
                                          AND @azure = 0
                                      ) THEN
                                          ' because it''s not available < 2017'
                                      WHEN EXISTS
            (
                SELECT 1 / 0
                FROM #database_query_store_options AS dqso
                WHERE dqso.wait_stats_capture_mode_desc <> 'ON'
            )       THEN
                                          ' because you have it disabled in your Query Store options'
                                      ELSE
                                          ' for the queries in the results'
                                  END;
            END;

            SELECT @current_table = N'selecting query store options',
                   @sql = N'';

            SELECT @sql += N'
    SELECT
        source =
            ''query_store_options'',
        dqso.desired_state_desc,
        dqso.actual_state_desc,
        dqso.readonly_reason,
        current_storage_size_mb =
            FORMAT(dqso.current_storage_size_mb, ''N0''),
        flush_interval_seconds =
            FORMAT(dqso.flush_interval_seconds, ''N0''),
        interval_length_minutes =
            FORMAT(dqso.interval_length_minutes, ''N0''),
        max_storage_size_mb =
            FORMAT(dqso.max_storage_size_mb, ''N0''),
        dqso.stale_query_threshold_days,
        max_plans_per_query =
            FORMAT(dqso.max_plans_per_query, ''N0''),
        dqso.query_capture_mode_desc,';

            IF (@azure = 1 OR @product_version > 13)
            BEGIN
                SELECT @sql += N'
        dqso.wait_stats_capture_mode_desc,';
            END;

            IF (@azure = 1 OR @product_version > 14)
            BEGIN
                SELECT @sql += N'
        capture_policy_execution_count =
            FORMAT(dqso.capture_policy_execution_count, ''N0''),
        capture_policy_total_compile_cpu_time_ms =
            FORMAT(dqso.capture_policy_total_compile_cpu_time_ms, ''N0''),
        capture_policy_total_execution_cpu_time_ms =
           FORMAT(dqso.capture_policy_total_execution_cpu_time_ms, ''N0''),
        capture_policy_stale_threshold_hours =
            FORMAT(dqso.capture_policy_stale_threshold_hours, ''N0''),';
            END;

            SELECT @sql += N'
    dqso.size_based_cleanup_mode_desc
    FROM #database_query_store_options AS dqso
    OPTION(RECOMPILE);';

            IF @debug = 1
            BEGIN
                PRINT LEN(@sql);
                PRINT @sql;
            END;

            EXEC sys.sp_executesql @sql;

        END;

        SELECT x.all_done,
               x.support,
               x.help,
               x.problems,
               x.performance,
               x.thanks
        FROM
        (
            SELECT sort = 1,
                   all_done = 'brought to you by erik darling data!',
                   support = 'for support, head over to github',
                   help = 'for local help, use @help = 1',
                   problems = 'to debug issues, use @debug = 1;',
                   performance = 'if this runs slowly, use to get query plans',
                   thanks = 'thanks for using sp_QuickieStore!'
            UNION ALL
            SELECT sort = 2,
                   all_done = 'https://www.erikdarlingdata.com/',
                   support = 'https://github.com/erikdarlingdata/DarlingData',
                   help = 'EXEC sp_QuickieStore @help = 1;',
                   problems = 'EXEC sp_QuickieStore @debug = 1;',
                   performance = 'EXEC sp_QuickieStore @troubleshoot_performance = 1;',
                   thanks = 'i hope you find it useful, or whatever'
        ) AS x
        ORDER BY x.sort;

    END TRY
    BEGIN CATCH

        /*
    Where the error happened and the message
    */
        IF @current_table IS NOT NULL
        BEGIN
            RAISERROR('error while %s', 11, 1, @current_table) WITH NOWAIT;
        END;

        /*
        Query that caused the error
        */
        IF @sql IS NOT NULL
        BEGIN
            RAISERROR('offending query:', 10, 1) WITH NOWAIT;
            RAISERROR('%s', 10, 1, @sql) WITH NOWAIT;
        END;

        /*
    This reliably throws the actual error from dynamic SQL
    */
        THROW;

    END CATCH;

    /*
Debug elements!
*/
    IF @debug = 1
    BEGIN

        SELECT parameter_type = 'procedure_parameters',
               database_name = @database_name,
               sort_order = @sort_order,
               [top] = @top,
               start_date = @start_date,
               end_date = @end_date,
               execution_count = @execution_count,
               duration_ms = @duration_ms,
               procedure_schema = @procedure_schema,
               procedure_name = @procedure_name,
               include_plan_ids = @include_plan_ids,
               include_query_ids = @include_query_ids,
               ignore_plan_ids = @ignore_plan_ids,
               ignore_query_ids = @ignore_query_ids,
               query_text_search = @query_text_search,
               wait_filter = @wait_filter,
               expert_mode = @expert_mode,
               format_output = @format_output,
               version = @version,
               version_date = @version_date,
               help = @help,
               debug = @debug,
               troubleshoot_performance = @troubleshoot_performance;

        SELECT parameter_type = 'declared_parameters',
               azure = @azure,
               engine = @engine,
               product_version = @product_version,
               database_id = @database_id,
               database_name_quoted = @database_name_quoted,
               procedure_name_quoted = @procedure_name_quoted,
               collation = @collation,
               new = @new,
               [sql] = @sql,
               len_sql = LEN(@sql),
               isolation_level = @isolation_level,
               parameters = @parameters,
               plans_top = @plans_top,
               nc10 = @nc10,
               where_clause = @where_clause,
               procedure_exists = @procedure_exists,
               query_store_exists = @query_store_exists,
               query_store_waits_enabled = @query_store_waits_enabled,
               [string_split] = @string_split,
               current_table = @current_table,
               troubleshoot_insert = @troubleshoot_insert,
               troubleshoot_update = @troubleshoot_update,
               xml_info = @troubleshoot_info,
               rc = @rc;

        IF EXISTS (SELECT 1 / 0 FROM #distinct_plans AS dp)
        BEGIN
            SELECT table_name = '#distinct_plans',
                   dp.*
            FROM #distinct_plans AS dp
            ORDER BY dp.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#distinct_plans is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #procedure_plans AS pp)
        BEGIN
            SELECT table_name = '#procedure_plans',
                   pp.*
            FROM #procedure_plans AS pp
            ORDER BY pp.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#procedure_plans is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #include_plan_ids AS ipi)
        BEGIN
            SELECT table_name = '#include_plan_ids',
                   ipi.*
            FROM #include_plan_ids AS ipi
            ORDER BY ipi.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#include_plan_ids is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #include_query_ids AS iqi)
        BEGIN
            SELECT table_name = '#include_query_ids',
                   iqi.*
            FROM #include_query_ids AS iqi
            ORDER BY iqi.query_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#include_query_ids is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #ignore_plan_ids AS ipi)
        BEGIN
            SELECT table_name = '#ignore_plan_ids',
                   ipi.*
            FROM #ignore_plan_ids AS ipi
            ORDER BY ipi.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#ignore_plan_ids is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #ignore_query_ids AS iqi)
        BEGIN
            SELECT table_name = '#ignore_query_ids',
                   iqi.*
            FROM #ignore_query_ids AS iqi
            ORDER BY iqi.query_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#ignore_query_ids is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #query_text_search AS qst)
        BEGIN
            SELECT table_name = '#query_text_search',
                   qst.*
            FROM #query_text_search AS qst
            ORDER BY qst.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#query_text_search is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #wait_filter AS wf)
        BEGIN
            SELECT table_name = '#wait_filter',
                   wf.*
            FROM #wait_filter AS wf
            ORDER BY wf.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#wait_filter is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #maintenance_plans AS mp)
        BEGIN
            SELECT table_name = '#maintenance_plans',
                   mp.*
            FROM #maintenance_plans AS mp
            ORDER BY mp.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#maintenance_plans is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #database_query_store_options AS qst)
        BEGIN
            SELECT table_name = '#database_query_store_options',
                   dqso.*
            FROM #database_query_store_options AS dqso
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#database_query_store_options is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #query_store_plan AS qsp)
        BEGIN
            SELECT table_name = '#query_store_plan',
                   qsp.*
            FROM #query_store_plan AS qsp
            ORDER BY qsp.plan_id,
                     qsp.query_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#query_store_plan is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #query_store_query AS qsq)
        BEGIN
            SELECT table_name = '#query_store_query',
                   qsq.*
            FROM #query_store_query AS qsq
            ORDER BY qsq.query_id,
                     qsq.query_text_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#query_store_query is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #query_store_query_text AS qsqt)
        BEGIN
            SELECT table_name = '#query_store_query_text',
                   qsqt.*
            FROM #query_store_query_text AS qsqt
            ORDER BY qsqt.query_text_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#query_store_query_text is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #dm_exec_query_stats AS deqs)
        BEGIN
            SELECT table_name = '#dm_exec_query_stats ',
                   deqs.*
            FROM #dm_exec_query_stats AS deqs
            ORDER BY deqs.statement_sql_handle
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#dm_exec_query_stats is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #query_store_runtime_stats AS qsrs)
        BEGIN
            SELECT table_name = '#query_store_runtime_stats',
                   qsrs.*
            FROM #query_store_runtime_stats AS qsrs
            ORDER BY qsrs.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#query_store_runtime_stats is empty';
        END;

        IF (@new = 1 AND EXISTS (SELECT 1 / 0 FROM #query_store_wait_stats AS qsws))
        BEGIN
            SELECT table_name = '#query_store_wait_stats',
                   qsws.*
            FROM #query_store_wait_stats AS qsws
            ORDER BY qsws.plan_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#query_store_wait_stats is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #query_context_settings AS qcs)
        BEGIN
            SELECT table_name = '#query_context_settings',
                   qcs.*
            FROM #query_context_settings AS qcs
            ORDER BY qcs.context_settings_id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#query_context_settings is empty';
        END;

        IF EXISTS (SELECT 1 / 0 FROM #troubleshoot_performance AS qcs)
        BEGIN
            SELECT table_name = '#troubleshoot_performance',
                   tp.*
            FROM #troubleshoot_performance AS tp
            ORDER BY tp.id
            OPTION (RECOMPILE);
        END;
        ELSE
        BEGIN
            SELECT result = '#troubleshoot_performance is empty';
        END;

        RETURN;

    END;

    RETURN;

END;
/*
Final End
*/
GO