SQL Server parameterizes ad hoc queries automatically. However, SQL Server is very conservative with plan reuse. It does not want to use a wrong plan. SQL Server decides to reuse a plan only when it is sure that the cached plan is the correct one for a query. Changes in parameter data type, in some SET options, in security context, and more, can produce a new plan when you would expect reuse of an existing cached plan.
You can get information about cached plans and the number of times the plans were reused by querying the sys.dm_exec_query_stats dynamic management function. You can get the exact text of the query from the sys.dm_exec_sql_text dynamic management function.
When you are testing plan caching and reuse, you can use the DBCC FREEPROCCACHE T-SQL command to clear the cache. You use it in a very simple way, as shown in the following code.
DBCC FREEPROCCACHE;

——————————————————

IMPORTANT: Using the DBCC FREE PROCCACHE Commmad in Production
The DBCC FREEPROCCACHE T-SQL command is very useful for testing. However, you should be very careful about using it in a production environment. If you clear the cache of a production server, SQL Server has to optimize and compile all new subsequent queries, stored procedures, functions, and triggers. Users could experience a huge performance impact.

—————————————————–

The following three queries each retrieve a single order from the Sales.Orders table in the TSQL2012 database. They all use the orderid column as a parameter for the WHERE clause. The table has a primary key defined on the orderid column. Therefore, SQL Server knows that each query returns a single row only. However, the first two queries use an integer data type for the parameter, whereas the third one defines the parameter as a decimal number data type.
— Parameter INT
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10248;
— Parameter INT
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10249;
— Parameter DECIMAL
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10250.0;
You can check the plans in the cache and the number of executions by using the following query.
SELECT qs.execution_count AS cnt,
qt.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE N’%Orders%’
AND qt.text NOT LIKE N’%qs.execution_count%’
ORDER BY qs.execution_count;
Because this query is used in further examples in this lesson, this lesson refers to it as the
“plan reuse” query for future reference. The plan reuse query returns the following output.

———————
cnt         text
— —————–
1            (@1 numeric(6,1))SELECT [orderid],[custid],[empid],[orderdate] FROM
[Sales].[Orders] WHERE [orderid][email protected]
2            (@1 smallint)SELECT [orderid],[custid],[empid],[orderdate] FROM [Sales].[Orders]
WHERE [orderid][email protected]
You can see that SQL Server parameterized the queries. It reused the execution plan where the parameter was an integer number. When the parameter was a decimal number, SQL Server generated a new plan.

To demonstrate how SQL Server is conservative with plan reuse, the following three
queries use the custid in the WHERE clause. The first query returns a single row, the second
two rows, and the third 31 rows. SQL Server cannot be sure about the selectivity of the custid
column in the Sales.Orders table.
— One row
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 13;
GO
— Two rows
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 33;
GO
— 31 rows
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 71;
GO
If you cleared the cache before executing the previous three queries, then the plan reuse
query returns the following output.
cnt       text
—           —-
1             SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 33;
1            SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 13;
1             SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 71;
Note that the queries were not parameterized.

———————————–
More Info: Why SQL Server SOMETIMES DOES Not ParameterizE Queries
There are many other reasons why SQL Server does not parameterize the queries; for an exhaustive list, see Appendix A of the MSDN article “Plan Caching in SQL Server 2008” at ttp://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx. (Although this article is written for SQL Server 2008, it is still valid for SQL Server 2012.)

——————————————–
To demonstrate further what influences plan reuse, the following two queries are the same as the first two queries from the first example in this section, when the plan was reused. However, a SET option that could influence the query result is changed before the second query.
— Query that is parameterized
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10248;
— Changing a SET option
SET CONCAT_NULL_YIELDS_NULL OFF;

— Query that could use the same plan
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = 10249;
— Restoring the SET option
SET CONCAT_NULL_YIELDS_NULL ON;
If you cleared the cache before executing the previous three queries, then the plan reuse
query returns the following output.
cnt         text
—               —-
1             (@1 smallint)SELECT [orderid],[custid],[empid],[orderdate] FROM [Sales].[Orders]
WHERE [orderid][email protected]
1             (@1 smallint)SELECT [orderid],[custid],[empid],[orderdate] FROM [Sales].[Orders]
WHERE [orderid][email protected]
Although SQL Server parameterized the queries, it did not reuse the first plan.
You might think that SQL Server is too conservative about plan reuse. However, the situation is not that bad. Most of the queries come from applications, and applications typically always generate their queries in the same way, with the same options. In addition, you can help SQL Server by using the sys.sp_executesql system procedure to execute a parameterized dynamic SQL. Actually, you should consider using the sys.sp_executesql system procedure as a much better practice than using ad hoc queries.
In the following example, a parameterized query is created as a SQL string, and then executed twice by using different parameters. The first time the parameter is an integer, and the second time it is a decimal data type.
DECLARE @v INT;
DECLARE @s NVARCHAR(500);
DECLARE @p NVARCHAR(500);
— Build the SQL string
SET @s = N’
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid’;
SET @p = N’@orderid INT’;
— Parameter integer
SET @v = 10248;
EXECUTE sys.sp_executesql @s, @p, @orderid = @v;
— Parameter decimal
SET @v = 10249.0;
EXECUTE sp_executesql @s, @p, @orderid = @v;
If you cleared the cache before calling the sys.sp_executesql procedure twice, then the
plan reuse query returns the following output.
cnt           text
—              —-
2              (@orderid INT) SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE
orderid = @orderid

Note that although the second call implicitly used a decimal parameter, SQL Server knew that the parameter is actually an integer number, because it was explicitly defined as an integer for the sys.sp_executesql procedure. Of course, the value had to be implicitly convertible to the INTEGER data type or the second query would fail.
Using dynamic SQL is not a good practice. In order to enforce plan reuse, you should use programmatic objects such as stored procedures. The following code creates a procedure that wraps the query that retrieves a single order in a stored procedure.
CREATE PROCEDURE Sales.GetOrder
(@orderid INT)
AS
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;
You can call the procedure twice, by using a parameter implicitly passed one time as an integer and one time as a decimal number.
EXEC Sales.GetOrder @orderid = 10248;
EXEC Sales.GetOrder @orderid = 10249.0;
If you cleared the cache before calling the stored procedure twice, then the plan reuse
query returns the following output.
cnt             text
—                —
2                 CREATE PROCEDURE Sales.GetOrder (@orderid INT) AS SELECT orderid, custid, empid,
orderdate        FROM Sales.Orders WHERE orderid = @orderid;
You can see that the plan was successfully reused. Stored procedures enforce plan reuse. However, sometimes you might prefer that the subsequent stored procedure calls do not use a cached plan. A procedure might return a different amount of rows based on a parameter value. For some values, a query inside the procedure might be very selective, and for other values not selective at all. Therefore, you might want to have a different execution plan for each call. You can force SQL Server to recompile a stored procedure if you create it with the WITH RECOMPILE option. In addition, you can force recompilation on a query level. Instead of recompiling the complete procedure, you can recompile only the critical statements. You learn about procedure recompilation in the practice for this lesson, and about query recompilation in the next lesson of this chapter.
After testing the procedure, you should clean up the TSQL2012 database.
DROP PROCEDURE Sales.GetOrder;