sql server session state stored proc recompilation

  • Thread starter Thread starter Jason Collins
  • Start date Start date
J

Jason Collins

There are a number of stored procedures involved in sql server based session
maintenance:

- TempUpdateStateItemShort
- TempResetTimeout
- TempUpdateStateItemLong

etc. These stored procs are executed by the framework, e.g.,

exec TempResetTimeout @id = '1iucllii0f42ksvfg4zsajae00000001'

Performing a trace shows many SP:CacheMiss (indicating perhaps that the
stored proc is recompiling everytime).

According to Q263889
(http://support.microsoft.com/default.aspx?scid=kb;en-us;263889), the stored
proc owner (e.g., "dbo") should be specified in the call to avoid this
recompilation (or at least the cache miss).

Can anyone tell me if my assessment is correct? Will this optimization be
included in future .NET (1.1) Service Packs?

Thanks,
Jason Collins
 
Frank gave you all the details... You just need to call the procedure as
owner.sp, in your case (probably) as dbo.TempResetTimeout instead of just
simply TempResetTimeout. This has absolutely nothing to do with .NET, unless
you want it to be magically guessing and changing your SQL statements when
it feels like you didn't really mean them...

Jerry
 
Except that the TempResetTimeout, etc. methods are .NET methods for session
maintenance; i.e., I have nothing to do with them, and can't alter them.

j
 
You should do both, create it with a fully qualified name and call it with
one too. As for the tracing - I don't know, try using the SQL Server
profiler and look around. And if you can't figure it out ask in a SQL Server
newsgroup.

Jerry
 
Jerry,

Thank you for your answer(s)


Jerry III said:
You should do both, create it with a fully qualified name and call it with
one too. As for the tracing - I don't know, try using the SQL Server
profiler and look around. And if you can't figure it out ask in a SQL Server
newsgroup.

Jerry
Click to expand...
 
Back
Top