-- Code and scripts from Chapter 5: 
--    Plan Caching and Recompilation


-- This is the query we’ll use, which we’ll refer to as the
-- usecount query:

SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';




-- For example, if the following three queries are executed in 
-- the Northwind2 database, the first and third queries will use 
-- the same plan, but the second one will need to generate a new 
-- plan:

SELECT * FROM Orders WHERE CustomerID = 'HANAR';
SELECT * FROM Orders WHERE CustomerID = 'CHOPS';
SELECT * FROM Orders WHERE CustomerID = 'HANAR';





-- You can verify this by first clearing out plan cache, and 
-- then running the three queries above, in separate batches. 
-- Then run the usecount query referred to above:

USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CHOPS';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';




-- If you run the code below, you will see that none of the 
-- queries can reuse the same plan.

USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM orders WHERE customerID = 'HANAR';
GO

-- Try it again
SELECT * FROM orders WHERE customerID = 'HANAR';
GO
SELECT * FROM orders
WHERE customerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
GO
select * from orders where customerid = 'HANAR'
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';




-- For example, these two queries run in the Northwind2 database 
-- can use the same plan:

SELECT FirstName, LastName, Title FROM Employees
WHERE EmployeeID = 6;
SELECT FirstName, LastName, Title FROM Employees
WHERE EmployeeID = 2;




-- You can observe this behavior by running the following code, 
-- and observing the output of the usecount query:

USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 2;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO




-- As strange as it may seem, even if you switch the order of 
-- the queries, and use the bigger value first, you will get 
-- two prepared queries with two different parameter datatypes.

USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 622;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO




-- However, with PARAMETERIZATION FORCED, that’s not what we get,
-- as you can see when you run the code below.

USE Northwind2;
GO
ALTER DATABASE Northwind2 SET PARAMETERIZATION FORCED;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
SELECT * FROM BigOrders WHERE CustomerID = 'CENTC';
GO
SELECT * FROM BigOrders WHERE CustomerID = 'SAVEA';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
ALTER DATABASE Northwind2 SET PARAMETERIZATION SIMPLE;
GO




-- The same cached plan can be used for all the following queries:

EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 6;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 2;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 6;




-- If we take the same example used earlier when we set the 
-- database to PARAMETERIZATION FORCED, we can see that using 
-- sp_executesql is just as inappropriate.

USE Northwind2;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
WHERE CustomerID = @p', N'@p nvarchar(10)', 'CENTC';
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
WHERE CustomerID = @p', N'@p nvarchar(10)', 'SAVEA';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
SET STATISTICS IO OFF;
GO




-- Here is an example in the Northwind2 database of forcing 
-- recompilation for a stored procedure:

USE Northwind2;
GO
CREATE PROCEDURE P_Customers
@cust nvarchar(10)
AS
SELECT RowNum, CustomerID, OrderDate, ShipCountry
FROM BigOrders
WHERE CustomerID = @cust;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
GO
EXEC P_Customers 'CENTC';
GO
EXEC P_Customers 'SAVEA';
GO
EXEC P_Customers 'SAVEA' WITH RECOMPILE;




-- Here is an example of a function that masks part of a 
-- Social Security number. We will create it in the pubs 
-- sample database, because the authors table contains a Social
-- Security number in the au_id column.

USE pubs;
GO
CREATE FUNCTION dbo.fnMaskSSN (@ssn char(11))
RETURNS char(11)
AS
BEGIN
SELECT @SSN = 'xxx-xx-' + right (@ssn,4);
RETURN @SSN;
END;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-45-6789';
SELECT @mask;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-66-1111';
SELECT @mask;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-66-1111' WITH RECOMPILE;
SELECT @mask;
GO





-- If a scalar function is used within an expression, as in the 
-- example below, there is no way to request recompilation.

SELECT dbo.fnMaskSSN(au_id), au_lname, au_fname, au_id 
FROM authors;




-- Here are two functions that do the same thing.

CREATE FUNCTION Fnc_Inline_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
RETURN
(SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
  WHERE CustomerID = @cust);
GO
CREATE FUNCTION Fnc_Multi_Customers (@cust nvarchar(10))
RETURNS @T TABLE 
   (RowNum int, CustomerID nchar(10), OrderDate datetime,
    ShipCountry nvarchar(30))
AS
BEGIN
INSERT INTO @T
  SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
  WHERE CustomerID = @cust;
RETURN;
END;
GO


Here are the calls to the functions:

DBCC FREEPROCCACHE;
GO
SELECT * FROM Fnc_Multi_Customers('CENTC');
GO
SELECT * FROM Fnc_Inline_Customers('CENTC');
GO
SELECT * FROM Fnc_Multi_Customers('SAVEA');
GO
SELECT * FROM Fnc_Inline_Customers('SAVEA');
GO




-- In this next query, we want to retrieve the set_options, the 
-- object_id and the sql_handle from the list of attributes.

SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
      FROM sys.dm_exec_cached_plans
           OUTER APPLY 
               sys.dm_exec_plan_attributes(plan_handle) AS epa
      WHERE cacheobjtype = 'Compiled Plan'
      ) AS ecpa
   PIVOT (MAX(ecpa.value) FOR ecpa.attribute
       IN (“set_options”, “object_id”, “sql_handle”)) AS pvt;




-- You can determine what values these two SET options had for 
-- your objects by selecting from the OBJECTPROPERTY function, 
-- as shown:

SELECT OBJECTPROPERTY(object_id('<object name>'), 
             'ExecIsQuotedIdentOn');
SELECT OBJECTPROPERTY(object_id('<object name>'), 
             'ExecIsAnsiNullsOn');




-- The DMV sys.dm_os_memory_cache_hash_tables contains information
-- about each hash table, including its size. You can query this 
-- view to retrieve the number of buckets for each of the plan 
-- cache stores using the following query:

SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
               'CACHESTORE_PHDR', 'CACHESTORE_XPROC');




-- Here is the same query we discussed earlier to return attribute
-- information and pivot it so that three of the attributes are
-- returned in the same row as the plan_handle value.
 
SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
      FROM sys.dm_exec_cached_plans
           OUTER APPLY 
               sys.dm_exec_plan_attributes(plan_handle) AS epa
      WHERE cacheobjtype = 'Compiled Plan'
     ) AS ecpa
  PIVOT (MAX(ecpa.value) FOR ecpa.attribute
        IN (“set_options”, “object_id”, “sql_handle”)) AS pvt;




-- After executing both batches, we examine 
-- the sys.dm_exec_query_stats view.

USE Northwind2;
DBCC FREEPROCCACHE;
SET QUOTED_IDENTIFIER OFF;
GO
--- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SET QUOTED_IDENTIFIER ON;
GO
--- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SELECT st.text, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;
GO




-- We can use the query below to retrieve the text, usecounts, 
-- and size_in_bytes of the compiled plan and cacheobjtype for 
-- all the plans in cache. The results will be returned in 
-- order of frequency, with the batch having the most use 
-- showing up first:

SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes,
       cp.cacheobjtype, cp.objtype
FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY cp.usecounts DESC;




-- The example below uses sys.dm_exec_cached_plan_dependent_objects,
-- as well as sys.dm_exec_cached_plans, to retrieve the dependent
--  objects for all compiled plans, the plan_handle, and their 
-- usecounts. It also calls the sys.dm_exec_sql_text function to 
-- return the associated Transact-SQL batch.

SELECT text, plan_handle, d.usecounts, d.cacheobjtype
FROM sys.dm_exec_cached_plans
   CROSS APPLY 
      sys.dm_exec_sql_text(plan_handle)
   CROSS APPLY
      sys.dm_exec_cached_plan_dependent_objects(plan_handle) d;




-- This query returns the ten longest-running queries currently 
-- executing:
 
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
  ((CASE statement_end_offset
      WHEN -1
         THEN DATALENGTH(text)
      ELSE statement_end_offset
    END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_requests
   CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_elapsed_time DESC;




-- The following query will return the top-ten queries by total 
-- CPU time, to help you identify the most expensive queries 
-- running on your SQL Server.

SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
  ((CASE statement_end_offset
       WHEN -1
           THEN DATALENGTH(text)
       ELSE statement_end_offset
    END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle)
      CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY total_elapsed_time/execution_count DESC;




-- The queries below can be used to determine the number of 
-- buckets in the hash tables for the object store and the 
-- SQL store, and the number of entries in each of those stores.

SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP');

SELECT type, count(*) total_entries
FROM sys.dm_os_memory_cache_entries
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
GROUP BY type;




-- The DMV sys.dm_os_memory_cache_entries can show you the 
-- current and original cost of any cache entry, as well as 
-- the components that make up that cost.

SELECT text, objtype, refcounts, usecounts, size_in_bytes,
       disk_ios_count, context_switches_count,
       pages_allocated_count, original_cost, current_cost
FROM sys.dm_exec_cached_plans p
   CROSS APPLY sys.dm_exec_sql_text(plan_handle)
     JOIN sys.dm_os_memory_cache_entries e
        ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
  AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
ORDER BY objtype desc, usecounts DESC;



-- Create the sp_cacheobjects view
USE master
GO
CREATE VIEW sp_cacheobjects
(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid,
   refcounts, usecounts, pagesused, setopts, langid, dateformat,
   status, lasttime, maxexectime, avgexectime, lastreads,
   lastwrites, sqlbytes, sql)
AS
SELECT pvt.bucketid,
   CONVERT(nvarchar(17), pvt.cacheobjtype) AS cacheobjtype,
   pvt.objtype,
   CONVERT(int, pvt.objectid) AS object_id,
   CONVERT(smallint, pvt.dbid) AS dbid,
   CONVERT(smallint, pvt.dbid_execute) AS execute_dbid,
   CONVERT(smallint, pvt.user_id) AS user_id,
   pvt.refcounts, pvt.usecounts,
   pvt.size_in_bytes / 8192 AS size_in_bytes,
   CONVERT(int, pvt.set_options) AS setopts,
   CONVERT(smallint, pvt.language_id) AS langid,
   CONVERT(smallint, pvt.date_format) AS date_format,
   CONVERT(int, pvt.status) AS status,
   CONVERT(bigint, 0),
   CONVERT(bigint, 0),
   CONVERT(bigint, 0),
   CONVERT(bigint, 0),
   CONVERT(bigint, 0),
   CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2),
   CONVERT(nvarchar(3900), fgs.text)
FROM (SELECT ecp.*, epa.attribute, epa.value
      FROM sys.dm_exec_cached_plans ecp
    OUTER APPLY
         sys.dm_exec_plan_attributes(ecp.plan_handle) epa) AS ecpa
      PIVOT (MAX(ecpa.value) for ecpa.attribute IN
                 (“set_options”, “objectid”, “dbid”,
                  “dbid_execute”, “user_id”, “language_id”,
                   “date_format”, “status”)) AS pvt
        OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs;




-- The query below will list all of the resources that your 
-- SQL Server service might have to wait for, and it will display
-- the resources with the longest waiting list:

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;




-- You can look at the view sys.dm_os_memory_cache_counters to 
-- see the amount of memory allocated in the multipage units.

SELECT name, type, single_pages_kb, multi_pages_kb,
       single_pages_in_use_kb, multi_pages_in_use_kb
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP';




-- If you notice a high value for RESOURCE_SEMAPHORE_QUERY_COMPILE
-- waits, you can examine the entries in the plan cache through 
-- the sys.dm_exec_cached_plans view, as shown:

SELECT usecounts, cacheobjtype, objtype, bucketid, text
FROM sys.dm_exec_cached_plans
   CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY objtype;




-- For varchar data type, server side parameterization will not 
-- be so dependent on the length of the actual value. Take a 
-- look at these two queries in the Northwind2 database.

SELECT * FROM Customers
WHERE CompanyName = 'Around the Horn';
GO
SELECT * FROM Customers
WHERE CompanyName = 'Rattlesnake Canyon Grocery';
GO




-- When the following batch is optimized, SQL Server doesn’t 
-- have a specific value for the variable.

USE Northwind2;
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID;




-- The RECOMPILE hint can be very useful here, as it tells the 
-- SQL Server optimizer to come up with a new plan for the single
-- SELECT statement, right before that statement is executed, 
-- which will be after the SET statement has executed.

USE Northwind2;
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID
OPTION (RECOMPILE);




-- Here is an example of a plan guide that tells SQL Server to 
-- use the OPTIMIZE FOR hint, whenever the specified statement 
-- is found in the Sales.GetOrdersByCountry procedure

EXEC sp_create_plan_guide
@name = N'plan_US_Country',
@stmt =
N'SELECT SalesOrderID, OrderDate, h.CustomerID, h.TerritoryID
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c
ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetOrdersByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';




-- Here is an example of a plan guide that tells SQL Server to 
-- use only one CPU (no parallelization) when a particular query 
-- is executed as a stand-alone query:

EXEC sp_create_plan_guide
@name = N'plan_SalesOrderHeader_DOP1',
@stmt = N'SELECT TOP 10 *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';




-- To see an example of using a template guide and forcing 
-- parameterization, first clear your procedure cache and then 
-- execute these two queries in the AdventureWorks database:

DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;



-- If you’ve created the sp_cacheobjects view described earlier 
-- in the chapter, you could use that, otherwise, replace
-- sp_cacheobjects with sys.syscacheobjects.
 
SELECT objtype, dbid, usecounts, sql
FROM sp_cacheobjects
WHERE cacheobjtype = 'Compiled Plan';




-- In other words, any time a query that parameterizes to the 
-- same form as the query here, it will use the same plan 
-- already cached.

DECLARE @sample_statement nvarchar(max);
DECLARE @paramlist nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;',
@sample_statement OUTPUT,
@paramlist OUTPUT
SELECT @paramlist as parameters, @sample_statement as statement
EXEC sp_create_plan_guide @name = N'Template_Plan',
@stmt = @sample_statement,
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = @paramlist,
@hints = N'OPTION(PARAMETERIZATION FORCED)';


-- After creating the plan guide, run the same two statements 
-- as above, and then examine the plan cache:

DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
GO
SELECT objtype, dbid, usecounts, sql
FROM sp_cacheobjects
WHERE cacheobjtype = 'Compiled Plan';




-- There are 12 different SpecialOfferID values, and most of them
--  only occur a few hundred times at most, out of the 121,317
-- rows in the Sales.SalesOrderDetail, as the following script 
-- and output illustrates:

USE AdventureWorks
SELECT SpecialOfferID, COUNT(*) as Total
FROM Sales.SalesOrderDetail
GROUP BY SpecialOfferID;




-- As there are 1,238 pages in the table, for most of the values 
-- a nonclustered index on SpecialOfferID could be useful, so 
-- here is the code to build one:

CREATE INDEX Detail_SpecialOfferIndex 
    ON Sales.SalesOrderDetail(SpecialOfferID);




-- So we will create a template plan guide to autoparameterize 
-- queries of this form:

SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4;




-- The hint will force SQL Server to assume a specific value of 4
-- every time the query needs to be reoptimized.

USE AdventureWorks;
-- Get plan template and create plan Guide
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4',
@stmt OUTPUT,
@params OUTPUT
--SELECT @stmt as statement -- show the value when debugging
--SELECT @params as parameters -- show the value when debugging
EXEC sp_create_plan_guide N'Template_Plan_for SpecialOfferID',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION (PARAMETERIZATION FORCED)';
EXEC sp_create_plan_guide
@name = N'Force_Value_for_Prepared_Plan',
@stmt = @stmt,
@type = N'SQL',
@module_or_batch = NULL,
@params = @params,
@hints = N'OPTION (OPTIMIZE FOR (@0 = 4))';
GO




-- You can verify that the plan is being autoparameterized, and 
-- optimized for a value that uses a nonclustered index on 
-- SpecialOfferID by running a few tests:

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 3;
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 4;
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 5;
GO