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.
Replace the first statement in the stored procedure with the following Transact-SQL
statement. UPDATE STATISTICS Sales.GetCustomerActivity WITH RESAMPLE

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

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