This question was
closed
by
nidheesh.r.pillai for the following reason: The question is answered, right answer was accepted
asked
Tracking a Scenario: Is execution query plan reused if T-SQL query inside the EXEC() has defined the OPTION KEEP PLAN/KEEPFIXED PLAN?
Consider a scenario as below- ALTER PROCEDURE sp_GetEmployeeList (@ID INT) AS BEGIN DECLARE @QUERY NVARCHAR(4000) SET @QUERY = 'SELECT * FROM dbo.EMPLOYEE WHERE EMP_ID =
'+@ID+' OPTION (KEEPFIXED PLAN)' EXEC (@QUERY) END As per my knowledge, whenever EXEC()/EXECUTE() is used for dynamic querying, a new execution plan is always created each and everytime it is called; which is one of the reasons why parameterized sp_executesql statements are preffered when it comes to performance factors. However, if you see in the above statement, the OPTION (KEEPFIXED PLAN) is also used along with the dynamic query. So, my question is whether the query plans resued when the EXEC() statement containing the OPTION (KEEPFIXED PLAN)/ OPTION (KEEP PLAN) is called? If yes, then is this a good way to write queries?
No. It won't force the use of the plan on a query that is identified as different based on the T-SQL code. The KEEPFIXED PLAN hint avoids recompiles in situations where statistics are changing. It doesn't force plan use. The appropriate mechanism to get plan use, or reuse, is to parameterize the query in some fashion.
14 People are following this question.
