Professional Web Applications Themes

Stored procedures and database in the table - Microsoft SQL / MS SQL Server

I have read somewhere before that if the data in the table changes due to inserts and updates, it is beneficial to recompile the stored procedures accessing those tables. this would help a a new query plan being generated for the stored procedures and help in perfomance. However, I would like to know when would be a good time for the stored procedures to be rebuilt. How do I determine that the data has changed and it would be benfeficial for the stored procedure to be recompiled. Do I need to guess based on system usage and come up with ...

  1. #1

    Default Stored procedures and database in the table

    I have read somewhere before that if the data in the table
    changes due to inserts and updates, it is beneficial to
    recompile the stored procedures accessing those tables.
    this would help a a new query plan being generated for the
    stored procedures and help in perfomance.

    However, I would like to know when would be a good time
    for the stored procedures to be rebuilt. How do I
    determine that the data has changed and it would be
    benfeficial for the stored procedure to be recompiled. Do
    I need to guess based on system usage and come up with my
    own optimum schedule (monthly, weekly etc.) for the
    recompile of the stored procedures.

    Thanks,
    Bhupesh Saini
    Bhupesh Saini Guest

  2. #2

    Default Re: Stored procedures and database in the table

    As long as you have AutoUpdate statistics turned on (which is the default)
    this will happen automatically at the proper times. SQL Server keeps track
    of the number of changes in each table and will update the statistics at a
    point that is determined via an internal algorithm. When this happens any
    sp's accessing that table will get recompiled the next time they are run.
    You can manually force the update of statistics via sp_updatestats or UPDATE
    STATISTICS if you feel the need. But you should first determine for sure
    that the automatic way is not adequate before doing so, otherwise you should
    leave it as is.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Bhupesh Saini" <bsainibenefitconcepts.com> wrote in message
    news:008901c343ee$6a4ec350$a501280aphx.gbl...
    > I have read somewhere before that if the data in the table
    > changes due to inserts and updates, it is beneficial to
    > recompile the stored procedures accessing those tables.
    > this would help a a new query plan being generated for the
    > stored procedures and help in perfomance.
    >
    > However, I would like to know when would be a good time
    > for the stored procedures to be rebuilt. How do I
    > determine that the data has changed and it would be
    > benfeficial for the stored procedure to be recompiled. Do
    > I need to guess based on system usage and come up with my
    > own optimum schedule (monthly, weekly etc.) for the
    > recompile of the stored procedures.
    >
    > Thanks,
    > Bhupesh Saini

    Andrew J. Kelly Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  5. (Stored Procedures) Can't get one table to move contents to another
    By Alexander Risy in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 13th, 06:29 PM

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