Professional Web Applications Themes

a question about procedure cache flushing ... - Microsoft SQL / MS SQL Server

Howdy - I'm trying to 'map' my knowledge of Teradata to SQL Server - sometimes a worthwhile exercise, others not. My question is about performance of stored procedures. Is it a valid assumption that the procedure cache holds the parsing engine's plan for the SQL in the procedure? If so, isn't it necessary to flush this cache periodically in concert with updating table stats in order for the plan to be rebuilt with a fresher view of the tables? Or am I off base? Please advise. Tia...

  1. #1

    Default a question about procedure cache flushing ...

    Howdy -

    I'm trying to 'map' my knowledge of Teradata to SQL
    Server - sometimes a worthwhile exercise, others not.

    My question is about performance of stored procedures.
    Is it a valid assumption that the procedure cache holds
    the parsing engine's plan for the SQL in the procedure?

    If so, isn't it necessary to flush this cache periodically
    in concert with updating table stats in order for the plan
    to be rebuilt with a fresher view of the tables?

    Or am I off base?

    Please advise.

    Tia
    randyv Guest

  2. #2

    Default Re: a question about procedure cache flushing ...

    SQL-Server will do this for you.

    Assuming you have auto create statistics and auto update statistics on
    (which is the default), the statistics will be updated regularly.
    Whenever statistics for a table/index are updated, the query plans that
    use these tables are invalidated. The next time the query or stored
    procedure is run a new query plan is determined.

    You can also force a stored procedure to be invalidated, either by
    applying sp_recompile to a table or by applying sp_recompile to a stored
    procedure.

    Gert-Jan


    randyv wrote: 
    Gert-Jan Guest

  3. #3

    Default Re: a question about procedure cache flushing ...

    Cool. That's good to know. 
    statistics on 
    regularly. 
    query plans that 
    or stored 
    either by 
    sp_recompile to a stored [/ref]
    periodically [/ref]
    plan 
    >.
    >[/ref]
    randyv Guest

Similar Threads

  1. Cache Question
    By Mike P in forum PHP Development
    Replies: 11
    Last Post: August 13th, 07:11 AM
  2. Cache object question
    By James J. Foster in forum ASP.NET General
    Replies: 0
    Last Post: August 11th, 07:27 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