Professional Web Applications Themes

Stored procedures recompiling - Microsoft SQL / MS SQL Server

We have a situation where the application programmer is saying that every time a particular stored procedure is used, it is recompiled, even if three people are using it at the same time. We are both assuming that it would be compiled once and kept in cache for a while. Any help appreciated....

  1. #1

    Default Stored procedures recompiling

    We have a situation where the application programmer is
    saying that every time a particular stored procedure is
    used, it is recompiled, even if three people are using it
    at the same time.

    We are both assuming that it would be compiled once and
    kept in cache for a while.

    Any help appreciated.
    Randy Guest

  2. #2

    Default Re: Stored procedures recompiling

    See if this helps:
    http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q243/5/86.ASP&NoWebContent=1
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Randy Patterson" <com> wrote in message
    news:0e1201c3671e$6457a090$gbl... 


    SriSamp Guest

  3. #3

    Default Re: Stored procedures recompiling

    There are many reasons when an particular SP can get recompiled ... Look at
    :
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;243586
    and
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;308737
    for more information ...

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    www.extremeexperts.com


    "Randy Patterson" <com> wrote in message
    news:0e1201c3671e$6457a090$gbl... 


    Vinodk Guest

  4. #4

    Default Re: Stored procedures recompiling

    To add to the other posts:

    In the old architecture (6.5 and older), proc plans weren't re-entrant. This meant that if you had
    two users executing the same proc at the very same time, SQL Server needed to have two plans for
    that proc. Both plans (or possibly more) would then be in cache.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


    "Randy Patterson" <com> wrote in message
    news:0e1201c3671e$6457a090$gbl... 


    Tibor Guest

Similar Threads

  1. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  2. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  3. Stored Procedures
    By Vladi in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: August 13th, 01:52 PM
  4. SQL-DMO and stored procedures
    By in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 24th, 02:46 PM
  5. Need help about stored procedures
    By Tahir in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 23rd, 10:17 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