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 ?
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.
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.
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.
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?
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 ??
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