asked
Execution plan recompile
Hi, I have a table with all NVARCHAR columns and i have a procedure which manipulates that table data. But in my sp i have declared all the variables in VARCHAR data type only. Now how the execution plan will be created? after sometime i will be changing my procedure in to NVARCHAR data type variables. After changing it to NVARCHAR type, will the same execution plan get executed or it will create new execution plan?? Because there is no change in any query. Just changing the data type from VARCHAR to NVARCHAR. Regards, Karthik
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Hi Execution plan will change for sure. You could test it by creating a sample db and table inside it. 1. Create a proc using varchar variable first here is the script :- create table p_cache (i nvarchar(100)) insert into p_cache values ('lokesh') go 1282 create proc sampl as declare @lk varchar(100) set @lk='loku' update p_cache set
i=@lk 2 use the following query, change the procedure and check whether the execution plan is reused or not (defined by usecounts): SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 1 ORDER BY usecounts DESC; GO 3 Here are some scenarios where execution plan could change (taken from @Grant's book ebook-dissectingsqlserverexecutionplans): 1. changing schema of the table 2. changing index 3. mising ddl or dml 4. Changes to cursor options Whosoever sees it, please correct me if I am missing something here
All accurate. There will be a recompile. The likelihood is that the plan will be the same, but it's going to get recompiled.
@erlokeshsharma08 is correct. It's also specified in the post from Technet below that performing an ALTER PROCEDURE will invalidate the existing plan in the cache.
https://technet.microsoft.com/en-us/library/cc293624.aspx Look under the cache size management section. Hope that helps!
20 People are following this question.
