Strategies for Fixing Parameter Sniffing in SQL Server

make_sandbox_parametersniffing.sql

---Create a playground
create database sandbox;
go
use sandbox
go
drop table dbo.tableOfThings
go
create table dbo.tableOfThings (
id int identity(1,1) not null constraint pk_tableOfThings primary key
, date_created datetime2 not null constraint df_date_created  default getdate()
, thing varchar(max) not null
, importance tinyint not null constraint df_importance default 1
);
go
set nocount on;
insert into dbo.tableOfThings(thing) values (replace(newid(), '-', ''));
GO 500000
UPDATE dbo.tableOfThings SET importance = 2 where id % 2 = 0;
UPDATE dbo.tableOfThings SET importance = 3 WHERE id % 3 = 0;
UPDATE dbo.tableOfThings SET importance = 1 WHERE id % 6 = 0;
UPDATE dbo.tableOfThings SET importance = 1 WHERE id % 16 = 0;
UPDATE dbo.tableOfThings SET importance = 4 WHERE id % 97 = 0
GO
CREATE INDEX idx_importance ON dbo.tableOfThings (importance ASC) with (fillfactor = 100)
GO

scenario-1_causeparametersniffing.sql

---Scenario-1: Create a Parameter Sniff
CREATE PROCEDURE #get_things
	@importance tinyint
 AS 
	SELECT thing FROM dbo.tableOfThings WHERE importance = @importance;
GO
EXECUTE #get_things 4;
EXECUTE #get_things 1;
GO

scenario-2_copyparamtolocal.sql

---Scenario-2: Copy parameters to local variables
CREATE PROCEDURE #get_things_localvariables
	@importance tinyint
 AS 
	DECLARE @local_importance tinyint;
	SET @local_importance = @importance;

	SELECT thing FROM dbo.tableOfThings WHERE importance = @local_importance;
GO
EXECUTE #get_things_localvariables 4;
EXECUTE #get_things_localvariables 1;
GO

/*
This produced the following plan...
https://www.brentozar.com/pastetheplan/?id=rkKWzdyrN
*/

Write this in support of the stack overflow post Resolve Intermittent Performance Issue Due to Paramter Sniffing