Easier troubleshooting for forced plan failures

[BrentOzar]

Is your feature request related to a problem? Please describe. The "Automatic Tuning" (aka regressed plan forcing) in Azure SQL DB & SQL Server can go awry, leaving behind plan guides that fail and repeatedly recompile.

I'd like to have a better way to find out that:

• sys.query_store_plan has problematic rows with recent last_compile_start_time
• Plan force failures have happened in the last 15 minutes (sp_BlitzFirst)
• Plan force failures are happening since startup (sp_Blitz)
• Query Store has gone into read-only mode on a writeable database, or failed due to running out of space
• Probably other issues, but starting this Github issue now for the design process

Describe the solution you'd like TBD. Today's issue was in Azure SQL DB, and I don't use sp_Blitz in Azure SQL DB. It may be time to do a separate sp_Blitz for Azure SQL DB with all of the foreachdb checks ripped out and converted into current-database checks, but that's a separate issue.

Describe alternatives you've considered Manual troubleshooting like I did today. Dang, Query Store failures were the last place I thought of looking, and I burned 4 hours before discovering it was on fire.

Are you ready to build the code for the feature? Yep.

[erikdarlingdata]

To get a check like this into Blitz, I think you'd need something along these lines. I had most of this already in place from QuickieStore, I just haven't had a chance to consolidate it until now.

SET NOCOUNT ON;

DECLARE
    @database_name sysname = N'',
    @database_name_quoted sysname = N'',
    @query_store_exists bit = 0,
    @new bit = 0,
    @sql nvarchar(MAX) = N''

DROP TABLE IF EXISTS
    #databases,
    #query_store_plan;

CREATE TABLE
    #databases
(
    database_name sysname PRIMARY KEY
);

CREATE TABLE
    #query_store_plan
(
    database_name sysname NOT NULL,
    plan_id bigint NOT NULL,
    query_id bigint NOT NULL,
    force_failure_count bigint 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,
    plan_forcing_type_desc nvarchar(60) NULL
);

SELECT
    @new = 
        CASE
           WHEN EXISTS
           (
               SELECT
                   1/0
               FROM sys.all_columns AS ac
               WHERE ac.object_id = OBJECT_ID(N'sys.query_store_plan')
               AND   ac.name = N'plan_forcing_type_desc'           
           )
           THEN 1
           ELSE 0
        END;

DECLARE
    database_cursor 
    CURSOR
    LOCAL
    STATIC
FOR
SELECT
    database_name =
        d.name
FROM sys.databases AS d
WHERE d.is_query_store_on = 1
AND   d.database_id > 4
OPTION(RECOMPILE);

OPEN database_cursor;

FETCH NEXT
FROM database_cursor
INTO @database_name;

WHILE @@FETCH_STATUS = 0
BEGIN        
    SET @database_name_quoted = QUOTENAME(@database_name);
    
    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);';
    
    EXEC sys.sp_executesql
        @sql,
      N'@query_store_exists bit OUTPUT',
        @query_store_exists OUTPUT;
    
    IF @query_store_exists = 0
    BEGIN
        RAISERROR('Query Store doesn''t seem to be enabled for database: %s', 10, 1, @database_name) WITH NOWAIT;
    END;
    
    SELECT
        @sql = N'
    SELECT
        @database_name,
        qsp.plan_id,
        qsp.query_id,
        qsp.force_failure_count,
        qsp.last_force_failure_reason_desc,
        qsp.count_compiles,
        qsp.initial_compile_start_time,
        qsp.last_compile_start_time,
        qsp.last_execution_time,';
    
    IF @new = 1
    BEGIN
        SELECT
            @sql += N'
        qsp.plan_forcing_type_desc';
    END;
    
    IF @new = 0
    BEGIN
        SELECT
            @sql += N'
        NULL';
    END;
    
    SELECT
        @sql += N'
    FROM ' + @database_name_quoted + N'.sys.query_store_plan AS qsp
    WHERE qsp.force_failure_count > 0
    OPTION(RECOMPILE);'
    
    INSERT
        #query_store_plan WITH(TABLOCKX)
        (
            database_name,
            plan_id,
            query_id,
            force_failure_count,
            last_force_failure_reason_desc,
            count_compiles,
            initial_compile_start_time,
            last_compile_start_time,
            last_execution_time,
            plan_forcing_type_desc
        )
    EXEC sys.sp_executesql
        @sql,
      N'@database_name sysname',
        @database_name;

    FETCH NEXT
    FROM database_cursor
    INTO @database_name;
END;

SELECT
    qsp.database_name,
    qsp.plan_id,
    qsp.query_id,
    qsp.plan_forcing_type_desc,
    qsp.force_failure_count,
    qsp.last_force_failure_reason_desc,
    qsp.count_compiles,
    qsp.initial_compile_start_time,
    qsp.last_compile_start_time,
    qsp.last_execution_time
FROM #query_store_plan AS qsp
ORDER BY
    qsp.database_name,
    qsp.last_compile_start_time DESC,
    qsp.force_failure_count DESC;

CLOSE database_cursor;
DEALLOCATE database_cursor;