Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Unanswered: Is the problem with update statistics

    Hi,

    I have report job it usually takes 45 mins.previously i did not put the update statistics on tables.But before running the reports i started a job with update statistics on tables.Now it is taking 2 hours.What might be the reason ?

    Thanks
    PVVS NAVEEN

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    hard to say dude unless you compare the old and new plans

  3. #3
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi

    I am running the same procedures in the two cases.

  4. #4
    Join Date
    Mar 2007
    Posts
    72

    update stats

    Hi,
    Are you running the update stats on the tables used in stored proc everytime you call this proc. If so it will obviously take more time, update stats will have a serious impact on performance if its run during database activity.

    Suda

  5. #5
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi,

    After BCP in the data into tables i am running the update statistics on tables.
    After this step i have to wait for 30 mins. and run reports


    Thanks

  6. #6
    Join Date
    Mar 2007
    Posts
    72
    naveen,
    if you want your reports to run faster do the bcp overnight and run update stats overnight as well and then run report in the morning or whenever. Update statistics will take time to run. hence the wait.

    if you are having clustered index on the table during bcp then you don't have to run update stats again.

  7. #7
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi

    I am doing the BCP in at 1.00 AM.
    and I am running the update statistics at 1.30 AM
    This activity is completed at 1.40 AM
    I am starting the reports at 2.00 AM
    Still this problem is happening . There are no other jobs on server from 12:00 night to morning 8.00 Only this activity is going on.


    Thanks

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Like willy_and_the_ci said, you'll have to compare the showplan output.
    Maybe the updated stats cause the optimiser to choose an incorrect path
    Are the statistics on the other tables in your query up to date?

  9. #9
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi

    I have one doubt about this issue.
    Is it require to run sp_recompile <object_name>.If the object is recreated
    But i can say we are not using object_id in any of procedures.
    Is it relating to update statistics ??

  10. #10
    Join Date
    Apr 2007
    Posts
    8
    set showplan on and statistics io on to capture the details of proc execution before update stats.
    if u execute proc after the update stats command the optimizer will use the same plan in memory (you should see this in showplan output). However, if you were to run same proc a second time optimizer will pick up details of update stats and readjust its strategy accordingly!

    so in conclusion run sp_recompile or execute <proc name>with recompile after update statistics command

    HTH

Posting Permissions

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