How to determine whether RECOMPILE is required? If so, at the creation or execution?

When we know that the parameters we pass for stored procedures are widely vary, we insist engine to recompile the procedure. But it does not mean that we have to recompile just because of the values are vary, we need make sure that there are different best plans for these values because there can be an instance where we see just a single plan best for all values. Therefore we need a way of seeing that there are different best plans for different values.

What is the simplest way? Easiest way is, execute the stored procedure with multiple different values with RECOMPILE option, enabling Actual Execution Plan. This tells us whether engines makes different plans for different execution indicating whether we need to have RECOMPILE option.

Here is the way;

-- Clearing the cache
DBCC FREEPROCCACHE
GO

USE AdventureWorks2014;
GO

-- Drop if it is exist
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL
 DROP PROC dbo.GetOrderDetails;
GO

-- Create the procedure
CREATE PROC dbo.GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

-- Enable "Include Actual Execution Plan"
-- and execute all together
EXEC dbo.GetOrderDetails 1 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 10000 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 10 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 1000 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 100 WITH RECOMPILE;
GO

Let's have a look on plans generated;

Since this is a simple stored procedure, we do not see many different plans for values passed. But if you see many different plans and procedure is always executed with values that are vary, it indicates that we need to add RECOMPILE at the creation.

If you see a common plan with 80-90% of sample executions and see one or two different plans only few few executions, it indicates that RECOMPILE is required at the execution only when passing values similar to those executions.