Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10

    Question Unanswered: Periodic recompiles?

    Here is a general question that I've been wondering about for a while: Is it a good idea to periodically recompile all of your stored procedures?

    I'm thinking it might be. As your database grows, execution plans for queries might change. A recompile would select the most optimized plan for the size and structure of your database. Right? Or am I just wasting my time?

    Thanks in advance...

    Mike

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You should re-build your indexes, update statistics then recompile stored procedures, triggers and views.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10
    Hmmm... How do I rebuild indexes and update statistics? And how often should I do this?


    Originally posted by Paul Young
    You should re-build your indexes, update statistics then recompile stored procedures, triggers and views.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    you can use DBCC DBREINDEX or DBCC INDEXDEFRAG. To update Statistics you can use UPDATE STATISTICS .

    As an alternative you can set up a maintance job to update indexes, statistics and re-compile any needed code.

    All of these topics can be found in Books Online.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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