Professional Web Applications Themes

sql server session state stored proc recompilation - ASP.NET General

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

  1. #1

    Default sql server session state stored proc recompilation

    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


    Jason Guest

  2. #2

    Default Re: sql server session state stored proc recompilation

    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)" <com> wrote in message
    news:phx.gbl... 
    > > session [/ref][/ref]
    the 
    > > stored [/ref]
    > be 
    > >
    > >[/ref]
    >
    >[/ref]


    Jerry Guest

  3. #3

    Default Re: sql server session state stored proc recompilation


    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" <com> wrote in message
    news:%phx.gbl... 
    unless [/ref][/ref]
    as [/ref]
    > the [/ref][/ref]
    the [/ref][/ref]
    this [/ref][/ref]
    optimization 
    > >
    > >[/ref]
    >
    >[/ref]


    Jason Guest

  4. #4

    Default Re: sql server session state stored proc recompilation

    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)" <com> wrote in message
    news:phx.gbl... [/ref]
    just 
    > unless [/ref]
    when [/ref]
    > as [/ref][/ref]
    based [/ref][/ref]
    that [/ref]
    > the [/ref]
    > this [/ref]
    > optimization 
    > >
    > >[/ref]
    >
    >[/ref]


    Jerry Guest

  5. #5

    Default Re: sql server session state stored proc recompilation

    Jerry,

    Thank you for your answer(s)


    "Jerry III" <com> wrote in message
    news:phx.gbl... 
    Server [/ref]
    dbo.AddModuleDefinition" [/ref]
    actually [/ref][/ref]
    as [/ref]
    > just 
    > > unless [/ref]
    > when [/ref][/ref]
    reference [/ref]
    > based [/ref]
    > that [/ref][/ref]
    (http://support.microsoft.com/default.aspx?scid=kb;en-us;263889), [/ref][/ref]
    avoid 
    > > optimization 
    > >
    > >[/ref]
    >
    >[/ref]


    MS Guest

  6. #6

    Default Re: sql server session state stored proc recompilation


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

    "Jerry III" <com> wrote in message
    news:%phx.gbl... 
    > session [/ref][/ref]
    as [/ref]
    > just 
    > > unless [/ref]
    > when [/ref][/ref]
    reference [/ref]
    > based [/ref]
    > that [/ref][/ref]
    (http://support.microsoft.com/default.aspx?scid=kb;en-us;263889), [/ref][/ref]
    avoid 
    > > optimization 
    > >
    > >[/ref]
    >
    >[/ref]


    Jason Guest

Similar Threads

  1. CF Server Choking When Calling STORED PROC
    By mostof in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 15th, 08:32 PM
  2. Session info stored on server
    By Lowell in forum PHP Development
    Replies: 3
    Last Post: September 26th, 06:31 PM
  3. ASP.NET session state on SQL Server
    By Ravikanth[MVP] in forum ASP.NET General
    Replies: 0
    Last Post: July 18th, 10:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139