Professional Web Applications Themes

Recompiling SPs? Why so often? Help! - Microsoft SQL / MS SQL Server

Server Info: W2k Adv, WSSE 2k, 4 Proc, 4G RAM Our DBA reports that he has to continually recompile stored procedures to keep our application running. The SPs do not change during the day, so what would cause it to need to be recompiled. Basically, our app times out on queries - fixed by recompiling. Any ideas? (Just pointing me to the right references would be most appreciated.) I'm not looking for a specific fix, but a basic understanding of the cause/effect going on here... or stuff I can look into. Thanks, Eric...

  1. #1

    Default Recompiling SPs? Why so often? Help!

    Server Info: W2k Adv, WSSE 2k, 4 Proc, 4G RAM

    Our DBA reports that he has to continually recompile stored procedures
    to keep our application running. The SPs do not change during the
    day, so what would cause it to need to be recompiled.

    Basically, our app times out on queries - fixed by recompiling. Any
    ideas? (Just pointing me to the right references would be most
    appreciated.)

    I'm not looking for a specific fix, but a basic understanding of the
    cause/effect going on here... or stuff I can look into.

    Thanks,

    Eric
    Eric Guest

  2. #2

    Default Re: Recompiling SPs? Why so often? Help!

    Pls refer to the following link

    http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp

    HTH,
    Srinivas Sampangi

    "Eric Camp" <com> wrote in message
    news:google.com... 


    sampangi Guest

  3. #3

    Default Re: Recompiling SPs? Why so often? Help!

    Eric,

    The link that Srinivas gave while informative may not actually answer your
    question. That deals with sp's that recompile on their own and you are
    stating that you need to recompile to fix the issue. One reason why
    recompiling a sp will increase it's speed is due to the fact you have a
    cached plan that is no longer efficient for the data currently in the db.
    This may be due to lots of data inserts, update or deletes, especially bulk
    loads. If the statistics are not being automatically kept up the stats can
    easily become out of date and produce bad query plans where they may have
    been correct for the previous size of the data. Do you have Auto Create and
    Auto Update statistics turned on for that db? If not you might think about
    turning it on. Has your dba done any ysis to see what is different in
    the plans between the time it runs OK and the time it doesn't? Another
    possibility could be something called parameter sniffing. Here is a very
    good description from Bart at MS that may be of help:


    The reason for the performance difference stems from a feature called
    "parameter sniffing". Consider a stored proc defined as follows:

    CREATE PROC proc1 p1 int AS
    SELECT * FROM table1 WHERE c1 = p1
    GO

    Keep in mind that the server has to compile a complete execution plan for
    the proc before the proc begins to execute. In 6.5, at compile time SQL
    didn't know what the value of p1 was, so it had to make a lot of guesses
    when compiling a plan. Suppose all of the actual parameter values for
    "p1 int" that a user ever passed into this stored proc were unique
    integers that were greater than 0, but suppose 40% of the [c1] values in
    [table1] were, in fact, 0. SQL would use the average density of the
    column to estimate the number of rows that this predicate would return;
    this would be an overestimate, and SQL would might choose a table scan
    over an index seek based on the rowcount estimates. A table scan would
    be the best plan if the parameter value was 0, but unfortunately it
    happens that users will never or rarely pass p1=0, so performance of the
    stored proc for more typical parameters suffers.

    In SQL 7.0 or 2000, suppose you executed this proc for the first time
    (when the sp plan is not in cache) with the command "EXEC proc1 p1 =
    10". Parameter sniffing allows SQL to insert the known value of
    parameter p1 into the query at compile time before a plan for the query
    is generated. Because SQL knows that the value of p1 is not 0, it can
    compile a plan that is tailored to the class of parameters that is
    actually passed into the proc, so for example it might select an index
    seek instead of a table scan based on the smaller estimated rowcount --
    this is a good thing if most of the time 0 is not the value passed as
    p1. Generally speaking, this feature allows more efficient stored proc
    execution plans, but a key requirement for everything to work as expected
    is that the parameter values used for compilation be "typical".

    In your case, the problem is that you have default NULL values for your
    parameters ("Today DATETIME = NULL, ...") that are not typical because
    the parameter values are changed inside the stored proc before they are
    used -- as a result NULL will never actually be used to search the
    column. If the first execution of this stored proc doesn't pass in an
    explicit value for the Today parameter, SQL believes that its value will
    be NULL. When SQL compiles the plan for this sp it substitutes NULL for
    each occurrence of Today that is embedded within a query.
    Unfortunately, after execution begins the first thing the stored proc
    does is change Today to a non-NULL value if it is found to be NULL, but
    unfortunately SQL doesn't know about this at compile time. Because NULL
    is a very atypical parameter value, the plan that SQL generates may not
    be a good one for the new value of the parameter that is assigned at
    execution time.

    So, the bottom line is that if you assign defaults to your sp parameters
    and later use those same parameters in a query, the defaults should be
    "typical" because they will be used during plan generation. If you must
    use defaults and business logic dictates that they be atypical (as may be
    the case here if app modifications are not an option), there are two
    possible solutions if you determine that the substitution of atypical
    parameter values is causing bad plans:

    1. "Disable" parameter sniffing by using local DECLARE'd variables that
    you SET equal to the parameters inside the stored proc, and use the local
    variables instead of the offending parameters in the queries. This is the
    solution that you found yourself. SQL can't use parameter sniffing in
    this case so it must make some guesses, but in this case the guess based
    on average column density is better than the plan based on a specific but
    "wrong" parameter value (NULL).

    2. Nest the affected queries somehow so that they run within a different
    context that will require a distinct execution plan. There are several
    possibilities here. for example:
    a. Put the affected queries in a different "child" stored proc. If
    you execute that stored proc within this one *after* the parameter Today
    has been changed to its final value, parameter sniffing will suddenly
    become your friend because the value SQL uses to compile the queries
    inside the child stored proc is the actual value that will be used in the
    query.
    b. Use sp_executesql to execute the affected queries. The plan won't
    be generated until the sp_executesql stmt actually runs, which is of
    course after the parameter values have been changed.
    c. Use dynamic SQL ("EXEC (sql)") to execute the affected queries.
    An equivalent approach would be to put the query in a child stored proc
    just like 2.a, but execute it within the parent proc with EXEC WITH
    RECOMPILE.

    Option #1 seems to have worked well for you in this case, although
    sometimes one of the options in #2 is a preferable choice. Here are some
    guidelines, although when you're dealing with something as complicated as
    the query optimizer experimentation is often the best approach <g>:

    - If you have only one "class" (defined as values that have similar
    density in the table) of actual parameter value that is used within a
    query (even if there are other classes of data in the base table that are
    never or rarely searched on), 2.a. or 2.b is probably the best option.
    This is because these options permit the actual parameter values to be
    used during compilation which should result in the most efficient query
    plan for that class of parameter.
    - If you have multiple "classes" of parameter value (for example, for
    the column being searched, half the table data is NULL, the other half
    are unique integers, and you may do searches on either class), 2.c can be
    effective. The downside is that a new plan for the query must be
    compiled on each execution, but the upside is that the plan will always
    be tailored to the parameter value being used for that particular
    execution. This is best when there is no single execution plan that
    provides acceptable execution time for all classes of parameters.

    HTH -
    Bart
    ------------
    Bart Duncan
    Microsoft SQL Server Support

    Please reply to the newsgroup only - thanks.

    This posting is provided "AS IS" with no warranties, and confers no
    rights.



    --

    Andrew J. Kelly
    SQL Server MVP


    "Eric Camp" <com> wrote in message
    news:google.com... 


    Andrew Guest

Similar Threads

  1. How to Repoint without Recompiling
    By David Schornack in forum ASP.NET Web Services
    Replies: 4
    Last Post: March 15th, 11:24 AM
  2. Recompiling the Kernel for better ATA support
    By Benjamin in forum FreeBSD
    Replies: 2
    Last Post: March 9th, 11:17 PM
  3. Need Help Recompiling PHP
    By Matt Rossiter in forum PHP Development
    Replies: 0
    Last Post: February 20th, 01:24 AM
  4. extending PHP on UNIX without recompiling
    By Drazen Gemic in forum PHP Development
    Replies: 2
    Last Post: July 13th, 11:14 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