question

pjkarthikmca avatar image
pjkarthikmca 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
execution-plan
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

All accurate. There will be a recompile. The likelihood is that the plan will be the same, but it's going to get recompiled.
1 Like 1 ·
JohnM avatar image
JohnM answered
@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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.