How Stored Procedure does Reuse of Execution Plans

Execution Plan
While Invoking Procedure SQL Server analyzes and optimizes the queries within the stored procedure and generates an execution plan. An execution plan is a collection of instructions to process the query. These
instructions include which order to access the tables in side the SP.
SQL Server generates multiple execution plans and will choose the one with the lowest cost out of the ones that it generated.

Reuse of Execution Plans
By Default Stored procedures can reuse a previously cached execution plan, thereby saving the resources involved
in generating a new execution plan.

Example for plan reuse. below code creates the get_order_info procedure

CREATE PROC get_order_info
@odate AS DATETIME
AS
SELECT orderid, custid, empid, orderdate /* KPFJ-4959-91D6-F1EC81F8428F */
FROM tbl_order
WHERE orderdate >= @odate

Turn on the STATISTICS IO option to get back I/O information for your session’s activity
SET STATISTICS IO ON

EXEC get_order_info '20080506'

Run the stored procedure for the first time, providing an input with high selectivity (that is, an input for which a small percentage of rows will be returned):

First time the stored procedure is invoked, SQL Server generated an execution plan for it based on the selective input value and cached that plan.

Advantage for Reuse of Execution Plans
Now that you have a plan stored in cache and invocations of the stored
procedure will reuse it. That’s fine if you keep invoking the stored procedure with a highly
selective input. You will enjoy the fact that the plan is reused, and SQL Server will not waste
resources on generating new plans. That’s especially important with systems that invoke
stored procedures very frequently.

Disadvantage for Reuse of Execution Plans

EXEC get_order_info '20120506'

When you invoke same SP 'get_order_info' again with low selectivity (that is, an input for which a high percentage of rows will be returned), In this case the SP reuse the same Execution plan which stored in cache
the problem is this Execution plan created for high selectivity input not for low so the plan efficiency will be reduced and performance too

How to check for Reuse of Execution Plans

The following query is used to find how many time get_order_info's execution plan used

SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%sys%'
AND sql LIKE '%KPFJ-4959-91D6-F1EC81F8428F%';

Output:

cacheobjtype objtype usecounts sql
-------------- -------- ---------- ----------------------------
Compiled Plan Proc 2 CREATE PROC dbo.get_order_info...

Notice that one plan was found for the get_order_info procedure in cache, and that it was used
twice (usecounts = 2).

How to Recompile Stored Procedure and Avoid reusing Plans
The RECOMPILE option tells SQL Server to create a new execution plan every time it is invoked.
This option actually tells SQL Server not to bother to cache the plan, hence every invocation
of the procedure ends up creating a new plan because it won’t find an existing one. It is especially useful when the cost of the recompiles is lower than the extra cost associated with reusing suboptimal plans

Example for Recompile Stored Procedure. below code creates the get_order_info procedure

CREATE PROC get_order_info
@odate AS DATETIME
WITH RECOMPILE
AS
SELECT orderid, custid, empid, orderdate
FROM tbl_order
WHERE orderdate >= @odate