By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,973 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,973 IT Pros & Developers. It's quick & easy.

sql server session state stored proc recompilation

P: n/a
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...;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
Nov 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

"MS News (MS ILM)" <sq**********@hotmail.com> wrote in message
news:ee**************@TK2MSFTNGP09.phx.gbl...
What does that mean please give us more details
"Frank Drebin" <no*****@imsickofspam.com> wrote in message
news:1Q**********************@newssvr28.news.prodi gy.com...
That's a general SQL thing.. technically you should always reference as
dbo.StoredProcName

"Jason Collins" <jc*************@point2.com> wrote in message
news:uc**************@TK2MSFTNGP09.phx.gbl...
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...;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



Nov 17 '05 #2

P: n/a

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

"Jerry III" <je******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"MS News (MS ILM)" <sq**********@hotmail.com> wrote in message
news:ee**************@TK2MSFTNGP09.phx.gbl...
What does that mean please give us more details
"Frank Drebin" <no*****@imsickofspam.com> wrote in message
news:1Q**********************@newssvr28.news.prodi gy.com...
That's a general SQL thing.. technically you should always reference as dbo.StoredProcName

"Jason Collins" <jc*************@point2.com> wrote in message
news:uc**************@TK2MSFTNGP09.phx.gbl...
> 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...;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
>
>



Nov 17 '05 #3

P: n/a
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

"MS News (MS ILM)" <sq***********@hotmail.com> wrote in message
news:u7*************@TK2MSFTNGP09.phx.gbl...
Jerry,

Couple of questions:
call the procedure: is it like "create procedure dbo.AddModuleDefinition"
and not as "create procedure AddModuleDefinition" OR when you actually
invoke it? via exec or from Code?

How do you Performing a trace in SQL to show SP:CacheMiss
Thank you.
"Jerry III" <je******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"MS News (MS ILM)" <sq**********@hotmail.com> wrote in message
news:ee**************@TK2MSFTNGP09.phx.gbl...
What does that mean please give us more details
"Frank Drebin" <no*****@imsickofspam.com> wrote in message
news:1Q**********************@newssvr28.news.prodi gy.com...
> That's a general SQL thing.. technically you should always reference as > dbo.StoredProcName
>
> "Jason Collins" <jc*************@point2.com> wrote in message
> news:uc**************@TK2MSFTNGP09.phx.gbl...
> > 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...;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
> >
> >
>
>



Nov 17 '05 #4

P: n/a
Jerry,

Thank you for your answer(s)
"Jerry III" <je******@hotmail.com> wrote in message
news:O4**************@TK2MSFTNGP10.phx.gbl...
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

"MS News (MS ILM)" <sq***********@hotmail.com> wrote in message
news:u7*************@TK2MSFTNGP09.phx.gbl...
Jerry,

Couple of questions:
call the procedure: is it like "create procedure dbo.AddModuleDefinition"
and not as "create procedure AddModuleDefinition" OR when you actually invoke it? via exec or from Code?

How do you Performing a trace in SQL to show SP:CacheMiss
Thank you.
"Jerry III" <je******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"MS News (MS ILM)" <sq**********@hotmail.com> wrote in message
news:ee**************@TK2MSFTNGP09.phx.gbl...
> What does that mean please give us more details
>
>
> "Frank Drebin" <no*****@imsickofspam.com> wrote in message
> news:1Q**********************@newssvr28.news.prodi gy.com...
> > That's a general SQL thing.. technically you should always
reference as
> > dbo.StoredProcName
> >
> > "Jason Collins" <jc*************@point2.com> wrote in message
> > news:uc**************@TK2MSFTNGP09.phx.gbl...
> > > 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...;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
> > >
> > >
> >
> >
>
>



Nov 17 '05 #5

P: n/a

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

"Jerry III" <je******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
I see... Well, not much you can do unless you want to create your own
session management. Maybe Microsoft will start following their own advice
sometime in the future...

Jerry

"Jason Collins" <jc*************@point2.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

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

"Jerry III" <je******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"MS News (MS ILM)" <sq**********@hotmail.com> wrote in message
news:ee**************@TK2MSFTNGP09.phx.gbl...
> What does that mean please give us more details
>
>
> "Frank Drebin" <no*****@imsickofspam.com> wrote in message
> news:1Q**********************@newssvr28.news.prodi gy.com...
> > That's a general SQL thing.. technically you should always
reference
as
> > dbo.StoredProcName
> >
> > "Jason Collins" <jc*************@point2.com> wrote in message
> > news:uc**************@TK2MSFTNGP09.phx.gbl...
> > > 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...;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
> > >
> > >
> >
> >
>
>



Nov 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.