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?

Question

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?

Accepted Answer

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

Answer

A. Run the EXEC sp_recompile GetCustomerActivity command.

Answer

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

Answer

D. Replace the first statement in the stored procedure with the following Transact-SQL statement.

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…

Question

Options

How the community answered

Community Discussion