Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2013-04-17 : 00:45:00
|
Hi All,i executed below piece of code.declare@qry nvarchar(4000),@var intset @var=2set @qry='select * from test where id='+cast(@var as varchar)while(@var<6)beginexec(@qry)set @var=@var+2;endWhile i was reading about EXEC i came across one statement like"the difference between EXEC and execute sp_executesql areEXEC won't store the execution plans in plan cache for re use where as sp_executesql will store execution plans for further executions."But after executing above code and then i look at my plan cahce from below querySELECT cp.objtype AS ObjectType,OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.usecounts AS ExecutionCount,st.TEXT AS QueryText,qp.query_plan AS QueryPlanFROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qpCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS sti was able to see use count for the select statement mention in the above code is 2.I need a clarification like will EXEC store the execution plans for further executions(Re Use)?Thanks.M.MURALI kRISHNA |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-17 : 01:54:05
|
Check the following link...http://sqlserverpedia.com/wiki/Query_Processing_-_Caching_Execution_PlansSee this point:1.Ad-hoc SELECT, INSERT, UPDATE and DELETE statements. In order for an execution plan to be cached and re-used for such constructs the two statements must be identical, including spacing and case of each word. Even if one letter is capitalized in one statement but not in the other, the execution plan for ad-hoc SQL statements cannot be reused.--Chandu |
 |
|
|
|
|