Of all features added to SQL Server in the past two decades, query store is the one I use most. But the SSMS interface for query store is missing some excruciatingly obvious features. Like a button to drop that horrible gremlin execution plan the optimizer suddenly concocted at 2:17 am and instantly became addicted to.

To drop this terrible plan, I need to execute sp_query_store_remove_plan for a long string of integers. I can't copy/paste that string because the plan_id is not exposed in a copy-capable location in SSMS - not even in the "Properties" window.

Instead, I have to hand write the string with pen and paper and then manually transcribe it back into a query window to drop the bad plan.

I wish I could say the "Force plan" button is a quick cure here, but my forced plans are often ignored by the optimizer, especially if the query in question includes a temporary table. That "Force plan" button needs seven asterisks and disclaimers.

So what can I do? If there's only one gremlin plan, then I can just bite the bullet and remove that bad plan with pen & paper and sp_query_store_remove_plan.

But what if there's a dozen gremlin plans and I don't have time to write down that many plan_id strings? I've written a procedure to drop all plans except one for a given query_id. It's not fast, and it's definitely heavy-handed in a treating-the-symptom-not-the-cause sort of way, but it works in a pinch when I really need to get rid of several gremlin execution plans that are grinding the application to a halt. That procedure looks like this:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE spDropAllPlans 
@query_id bigint,
@KeepThisPlanID bigint=NULL,        --NULL drops all plans
@debug BIT=1                --prints sql without executing
AS
SET XACT_ABORT ON
BEGIN TRY
    SET NOCOUNT ON;
    DECLARE @strSQL NVARCHAR(MAX)='',@PlanCount bigint

    SELECT        @strSQL+=N'exec sp_query_store_remove_plan ' + convert(nvarchar,p.plan_id) + N';' + convert(nvarchar,char(10))
    FROM        sys.query_store_query q
    JOIN        sys.query_store_plan p on q.query_id=p.query_id
    WHERE        q.query_id=@query_id
                and (@KeepThisPlanID IS NULL OR p.plan_id<>@KeepThisPlanID)
    order by    p.plan_id;

    SET @PlanCount=@@ROWCOUNT;

    IF @debug=0
    BEGIN
        EXEC sp_executesql @strSQL
        PRINT CONVERT(VARCHAR,@PlanCount) + ' plans removed from query store.'
    END
    ELSE
    BEGIN    --print the sql without exec
        --from https://www.codeproject.com/Articles/18881/SQL-String-Printing (Yildirim Kocdag)
        SET @strSQL = RTRIM(@strSQL)
        DECLARE @cr CHAR(1)= CHAR(13),@lf CHAR(1) = CHAR(10) 
        DECLARE @len INT,@cr_index INT, @lf_index INT, @crlf_index INT,@has_cr_and_lf BIT, @left NVARCHAR(4000), @reverse NVARCHAR(4000)

        SET @len = 4000 
        WHILE ( LEN( @strSQL ) > @len ) 
        BEGIN 
            SET @left = LEFT( @strSQL, @len )
            SET @reverse = REVERSE( @left )
            SET @cr_index = @len - CHARINDEX( @cr, @reverse ) + 1
            SET @lf_index = @len - CHARINDEX( @lf, @reverse ) + 1
            SET @crlf_index = CASE WHEN @cr_index < @lf_index THEN @cr_index ELSE @lf_index END 
            SET @has_cr_and_lf = CASE WHEN @cr_index < @len AND @lf_index < @len THEN 1 ELSE 0 END 
            --print left( @strSQL, @crlf_index - 1 ) 
            SET @strSQL = RIGHT( @strSQL, LEN( @strSQL ) - @crlf_index - @has_cr_and_lf ) 
        END
        PRINT @strSQL
    END
END TRY
BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION
   END 
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
        ;THROW;
END CATCH
GO

The query_id that procedure takes as a parameter is also not in a copy-capable location in SSMS, so to use the procedure above I have to handwrite both the query_id and the plan_id I want to keep with pen on to paper. It's fun - like the '70s.

As soon as I run this procedure to remove the bad plans, new plans will start being compiled immediately, because I've really just removed the symptom and its history. So before running the procedure, I save that gremlin plan and compare it to the good plans to find out what kind of problem I'm dealing with. Is it parameter sniffing, cached temp tables, maybe stale statistics? In my most recent case, it was temp table caching with stale statistics, which I treated by updating statistics on the temp table in question after populating it in the procedure as detailed here, so the statement no longer used statistics for an earlier, unrelated procedure execution.

Long story short, query store makes things easier, but it does not make things easy.

If you would like a "Drop plan" button included in the SSMS query store interface so we can cherry-pick bad execution plans to remove, please upvote my Microsoft suggestion here.