ExecContextHit through SQL Profiler

Hello,
Hopefully someone can answer this question. I am running a procedure which u
ses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to up
date a table. When I run this sp in a query window it runs much faster then
when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:Ex
ecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is r
ecompiling is there anyway to prohibit that?
Thanks !!ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:

>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dyn
amic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. W
hen I run this sp in a query window it runs much faster then when it is called from
a J
ob. I ran Profiler traces with both methods and noticed that there were SP:E
xecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is t
here anyway to prohibit that?
>Thanks !!|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:

> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
>
Job. I ran Profiler traces with both methods and noticed that there were SP:
ExecContectHit entries in the Traces. Does this mean that at the point that
I see these statements that there is an sp_recompile occurring within the sp
? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is
there anyway to prohibit that?[vbcol=seagreen]
>|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
>
a Job. I ran Profiler traces with both methods and noticed that there were S
P:ExecContectHit entries in the Traces. Does this mean that at the point tha
t I see these statements that there is an sp_recompile occurring within the
sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling i
s there anyway to prohibit that?[vbcol=seagreen]