Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    43

    Unanswered: update statistics and recompile

    Hi,

    I need a clarification regarding 'update statistics and recompile'.

    When update statistics is run on a table is it mandatory to run 'sp_recompile table'?

    If all the data is truncated on the table is there any use running 'sp_recompile table'?

    Thank you

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Also if I add a new index to a table can I get the sprocs to recompile and use the new index. Obviously I could recompile the sprocs concerned or I could execute with recompile but I wondered if there's something like recompile my_sproc?

    Mike

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I should of just googled a little better ...

    recompile table - You can't recompile a table.
    recompile sproc - Will recompile just that sproc.
    update statistics table - will update the stats on that table then recompile all sprocs that use that table

    If all the data is truncated on the table is there any use running 'sp_recompile table'?
    you'll probably find your sprocs will run much faster without any data

    Mike

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by adurga
    When update statistics is run on a table is it mandatory to run 'sp_recompile table'?
    No, but the proc will continue to use the old plan generated with the old stats. (So you just wasted time updating statistics)
    Quote Originally Posted by adurga
    If all the data is truncated on the table is there any use running 'sp_recompile table'?
    If the stats indicate a small or empty table the optimizer will know that a table scan is best and not spend time trying to find the best index to use for your query


    AFAIK with later versions a recompile will be automatic after update statistics.

  5. #5
    Join Date
    Aug 2003
    Posts
    43
    Thanks pdreyer and mike_bike_kite.

    pdreyer, I did not understand AFAIK in your reply. Other things are clear.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    As Far As I Know

Posting Permissions

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