Yes ! Auto update stats causing Performance issues??
What is Statistic in Sql Server Part - 1
What is Statistic in Sql Server Part - 2
Is it really yes answer depends on operations in your environment but summary says it helps to increase performance for future operations but reduces performance of current operations
Now let see why??If statistics is invalidate due to dml operation but statistics is not referred by sql server optimizer for any query than it will remain in same state until it’s manually updated by dba. When some query refer outdated statistics then only auto update stats will start updating statistics and once statistics is updated then only query will start executing which cause increase in query execution time.
So how to avoid same??
If we use "Auto Update Statistics Asynchronously option" then statistics will update as per bandwidth i.e. If Auto Update Statistics Asynchronously is true, queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling.
Subsequent queries will use the updated statistics when they are available. This will help current query to finish in time in case of huge table queries.
No comments:
Post a Comment