You are a database developer. You plan to design a database solution by using SQL Server 2008. You plan to design a complex multi-statement stored procedure in the following manner.

CREATE PROCEDURE Sales.GetCustomerActivity
@StartDate datetime
AS
SELECT order_id, order_date, customer_id
FROM Sales.Orders
WHERE order_date >= @StartDate
…

On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute. You discover that this degradation is caused by the first statement in the stored procedure. You need to ensure that the stored procedure is consistently executed in the minimum possible time. What should you do?

A.
Run the EXEC sp_recompile GetCustomerActivity command.

B.
Create a plan guide to apply the OPTION (RECOMPILE) clause to the first statement.

C.
Modify the stored procedure by adding the WITH RECOMPILE clause.

D.
Replace the first statement in the stored procedure with the following Transact-SQL statement.UPDATE STATISTICS Sales.GetCustomerActivity WITH RESAMPLE