sql server session state stored proc recompilation

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
 
J

Jerry III

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
 
J

Jason Collins

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
 
J

Jerry III

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
 
M

MS News \(MS ILM\)

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
 
J

Jason Collins

Yes, basically, I was posting here to bring it to someone at MS's
attention...
j
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top