Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: Batches/Recompiles

    Is it normal when monitoring via Permon to have instances where the number of batch requests per second is less than the number of recompiles? I am looking at the following stats

    Batches Recompiles

    2.13 4.40
    0.93 3.0
    6.25 9.5

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It is not impossible, and at this level, I would not be too concerned over it. Do you have a lot of stored procedures that include temp tables, and calls to other stored procedures? Temp tables in stored procedures cause recompiles.

  3. #3
    Join Date
    Oct 2008
    Posts
    56
    In stored procs I normall use table variables
    declare @table1 table
    rather than #table1 or ##table1

    Is this a better method to avoid recompiles?
    Thanks

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It will still recompile. Temp table or table variable, the optimizer does not have any idea what statistics to use until runtime.

  5. #5
    Join Date
    Oct 2008
    Posts
    56
    Is there a way around that?

    Thanks

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Short of not using temp tables/variables...no.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •