SQL Server Performance

High CPU and Recompiles

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by rerichards, Mar 14, 2007.

  1. rerichards New Member

    Can high CPU due to high recompiles feed off itself, meaning, the connections and activity on our server increases:
    1. Which causes a higher number of recompiles.
    2. Which causes higher CPU.
    3. Which causes more plans being removed from cache.
    3a. Which causes a higher number of recompiles
    3b. Which causes higher CPU.
    3c. Which causes more plans being removed from cache.
    etc., etc., etc.

    Is the above a possible scenario?
  2. vanirame New Member

    Hi,
    Question is little confusing. Do you think you can re-write the question again.

    Do you mean as below ?
    "As connections and activity on our server increases, does it causes a higher number of recompiles"

    Thank you,
    V
  3. MohammedU New Member

    High recompiles might be one reason for HIG CPU but you need to use profiler to track it down what query/proc is causing this and why?


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  4. rerichards New Member

    I have used Profiler, which led to this thread. I am seeing Full Compiles and Recompiles on several of our most frequently called procedures.

    One in procedure in particular, that does a Full Compile some of the time, but not every execution, is due to a Set Option. The following is a sample of the beginning of the procedure:

    The procedure is quite complex
    and contains about 2,000 lines. The beginning of the stored procedure starts
    out with the following, where @Debug is an argument when the stored procedure
    is executed:

    CREATE PROCEDURE dbo.sel_Main
    [There are about 20 arguments including the following argument]
    @Debug = 0
    AS

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    IF @Debug > 0
    BEGIN
    --
    SET NOCOUNT OFF
    SET ANSI_WARNINGS ON
    --
    END
    ELSE
    BEGIN
    --
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    --
    END

    [The body of the procedure (approximately 2,000 lines) begins here]

    From what I see in Profiler, this procedure is called every time with @Debug = 0.

    And, like I said, the Full Compile due to a Set Option change does not occur every execution, but still quite often.

    So, if @Debug = 0 is not causing the Set Option change, what might be causing the plan to be flushed from cache?
  5. mmarovic Active Member

    Does the procedure use temporary table(s)?
  6. rerichards New Member

    The procedure does utilize temporary tables.

    Upon further analysis of Profiler, taken during the high CPU episode, it was not recompiles occurring, but full out compiles.


    quote:Originally posted by mmarovic

    Does the procedure use temporary table(s)?

Share This Page