morally equivalent plans.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


--Let's get this plan into cache
--We run it 5 times for luck. (It's fast.)
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
						from Sales.OrderLines
						where PackageTypeID = @packagetypeid', N'@packagetypeid int',
					@packagetypeid = 5;
GO 5

--Get the query_id and plan_id
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

--Let's force it!
exec sp_query_store_force_plan @query_id=1, @plan_id=1;
GO

--Now we blow away the procedure cache
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

--Compile it with a different plan
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
						from Sales.OrderLines
						where PackageTypeID = @packagetypeid', N'@packagetypeid int',
					@packagetypeid = 7;
GO


--Now we have a "morally equivalent plan" with a new plan_id
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