Over the next few days we are going to start the migration to a new forum application. I'm going to start with high post count and active users. You may receive an invitation to the new forums. It's not spam. It's just me trying to seed the user base. My goal is to open it up over the weekend.

Once we open the site we'll block registrations here. We should be open shortly on the new site.

SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 EXEC Cache behaviour while executing Dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

95 Posts

Posted - 04/17/2013 :  00:45:00  Show Profile  Reply with Quote
Hi All,

i executed below piece of code.
@qry nvarchar(4000),
@var int
set @var=2
set @qry=
'select * from test where id='+cast(@var as varchar)
set @var=@var+2;

While i was reading about EXEC i came across one statement like
"the difference between EXEC and execute sp_executesql are
EXEC 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 query

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

i 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)?



Flowing Fount of Yak Knowledge

2241 Posts

Posted - 04/17/2013 :  01:54:05  Show Profile  Reply with Quote
Check the following link...

See 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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000