plan forcer and forcees example.sql

/*
This code modified from:
https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/automatic-tuning/force-last-good-plan/sql-scripts/demo-full.sql
WideWorldImporters-Full.bak download: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
License info from: https://github.com/Microsoft/sql-server-samples/blob/master/license.txt
This material was modified by Kendra Little of SQLWorkbooks.com.
The code is under the MIT license and is built on:
Microsoft SQL Server Sample Code
Copyright (c) Microsoft Corporation
All rights reserved.
MIT License.
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.
*/



USE master;
GO

IF DB_ID('WideWorldImporters') IS NOT NULL
BEGIN
    ALTER DATABASE WideWorldImporters SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO
RESTORE DATABASE WideWorldImporters FROM DISK=
	'S:\MSSQL\Backup\WideWorldImporters-Full.bak'  
	WITH REPLACE,
	MOVE 'WWI_Primary' to 'S:\MSSQL\Data\WideWorldImporters.mdf',
	MOVE 'WWI_UserData' to 'S:\MSSQL\Data\WideWorldImporters_UserData.ndf',
	MOVE 'WWI_Log' to 'S:\MSSQL\Data\WideWorldImporters.ldf',
	MOVE 'WWI_InMemory_Data_1' to 'S:\MSSQL\Data\WideWorldImporters_InMemory_Data_1';
GO


USE WideWorldImporters;
GO

ALTER DATABASE current SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE, 
    QUERY_CAPTURE_MODE = AUTO /* default is all, this ignores insignifiant queries */,
    MAX_PLANS_PER_QUERY = 200 /*default */,
    MAX_STORAGE_SIZE_MB = 2048 /* starter value */,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    SIZE_BASED_CLEANUP_MODE = AUTO,
    DATA_FLUSH_INTERVAL_SECONDS = 15,
    INTERVAL_LENGTH_MINUTES = 30 /* Available values: 1, 5, 10, 15, 30, 60, 1440 */,
    WAIT_STATS_CAPTURE_MODE = ON /* 2017 gets wait stats! */
    );
GO
ALTER DATABASE current SET QUERY_STORE = ON
GO


ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
ALTER DATABASE current SET QUERY_STORE CLEAR ALL;
GO


-- Enable automatic tuning on the database if you want to test it.
-- Make sure it's OFF if you want to test manually forcing.
ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);
GO

--ALTER DATABASE current
--SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);
--GO

-- Verify that actual state on FLGP is what you want:
SELECT name, actual_state_desc, status = IIF(desired_state_desc <> actual_state_desc, reason_desc, 'Status:OK')
FROM sys.database_automatic_tuning_options
WHERE name = 'FORCE_LAST_GOOD_PLAN';
GO

-- 1. Start workload - execute procedure 30-300 times 
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
						from Sales.OrderLines
						where PackageTypeID = @packagetypeid', N'@packagetypeid int',
					@packagetypeid = 5;
GO 60 -- NOTE: This number should be incrased if you don't get a plan change regression.



-- 2. Cause the plan regression
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

/* Get slow plan in cache */
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
						from Sales.OrderLines
						where PackageTypeID = @packagetypeid', N'@packagetypeid int',
					@packagetypeid = 0;
GO



-- 3. Start the workload again.
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
						from Sales.OrderLines
						where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;
go 20




-- 4. Find a recommendation 
--If you have Automatic Plan Correction on, it should be in "Verifying" or "Success" state
--If you have Automatic Plan Correction off, it should say AutomaticTuningOptionNotEnabled
SELECT reason, score,
	JSON_VALUE(state, '$.currentValue') state,
	JSON_VALUE(state, '$.reason') state_transition_reason,
    JSON_VALUE(details, '$.implementationDetails.script') script,
    planForceDetails.*
FROM sys.dm_db_tuning_recommendations
  CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            [new plan_id] int '$.regressedPlanId',
            [recommended plan_id] int '$.recommendedPlanId'
          ) as planForceDetails;
GO

--If you're testing manual forcing, use the script in the recommendation to manually force
--Or you can do it using a report if you want
--exec sys.sp_query_store_force_plan @query_id = 18, @plan_id = 1



-- 5. The last good plan is forced, so the query will be faster (whichever way you forced it)
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
						from Sales.OrderLines
						where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;
GO


-- Open Query Store/"Top Resource Consuming Queries" dialog in SSMS and show that a plan is forced.
-- Notice that we have THREE plans

--Inspect them here
--One plan has is_forced_plan = 1, that plan is forcing the shape
select 
    qst.query_sql_text,
    qsq.query_id,
    qsp.plan_id,
    qsp.engine_version,
    qsp.compatibility_level,
    qsp.query_plan_hash,
    qsp.is_forced_plan,
    qsp.plan_forcing_type_desc,
    cast(qsp.query_plan as XML) as plan_xml
from sys.query_store_query as qsq
join sys.query_store_query_text as qst on 
    qsq.query_text_id = qst.query_text_id
join sys.query_store_plan as qsp on qsq.query_id = qsp.query_id
where qst.query_sql_text like N'%(@packagetypeid int)%'
GO


/* Compile this again with @packagetypeid = 5 (the original "fast plan")
and see that it gets another bubble with a check mark in the Query Store Reports */

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
						from Sales.OrderLines
						where PackageTypeID = @packagetypeid', N'@packagetypeid int',
					@packagetypeid = 5;
GO