sql server session state stored proc recompilation

[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...;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

[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

"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

[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

"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
>
>

[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

"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
> >
> >
>
>

[MS News \(MS ILM\)]

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

[Jason Collins]

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