July 9, 2009 at 1:19 pm
Hello,
I have an existing sql 2000 server that has a maintenance plan whose only step is to UPDATE STATISTICS on the OPTIMIZER tab. I also notice that all of my databases have the option, AUTO UPDATE STATISTICS turned on. With this option turned on, is it necessary to run that maintenance plan??? I'm under the impression that the auto update is doing exactly what the maintenance plan is doing on a scheduled basis
I am recreating this server in a sql 2005 environment so I'm trying to clean up any unnecessary jobs while making the migration.
Now as we are moving our db's from sql2000 to sql2005 we are running dbcc updateusage and sp_updatestats to get our stats all cleaned up.
Thank you.
Juanita
July 9, 2009 at 1:27 pm
Juanita (7/9/2009)
Hello,I have an existing sql 2000 server that has a maintenance plan whose only step is to UPDATE STATISTICS on the OPTIMIZER tab. I also notice that all of my databases have the option, AUTO UPDATE STATISTICS turned on. With this option turned on, is it necessary to run that maintenance plan??? I'm under the impression that the auto update is doing exactly what the maintenance plan is doing on a scheduled basis
It might be.
Auto update stats means that when a query reads a set of column statistics and finds that they are out of date, it requests an update before proceeding with the query. For a set of stats to be out of date 20% of the table must have changed since the stats were last updated. That's a lot of rows on large tables.
The maint plan (as far as I know) executes UPDATE STATISTICS so it updates all stats, regardless of whether they are out dated or not.
It is possible for the auto update threshold to be too high and queries start degrading in performance due to poor stats long before the point that the auto update would kick in.
Now as we are moving our db's from sql2000 to sql2005 we are running dbcc updateusage and sp_updatestats to get our stats all cleaned up.
Don't use sp_updatestats there. Use UPDATE STATISTICS. sp_updatestats only updates stats that are considered outdated. When moving from 2000 to 2005, it's important to update all statistics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2009 at 1:38 pm
Thank you so much !!! Great points I didn't consider and being new to sql2005, thanks for that tip!!
Juanita
July 9, 2009 at 9:58 pm
Adding to What Gail has said :
In Auto update statistics the statistics change occure when the optimizer finds that he query is using the stale Stats , thus creating the stats at the query compiling time .Further the optimizer ensures that the smallest sampling rate is taken into consideration (ful scan for tables less than 8 MB in size)...A small sampling will not be able to give good hints to the optimizer .Its just like asking an advice on a common topin to 100 guys as compared to a nation wide voting on the same ...
Secondly , i would also advice you to create stats on the columns that are not a part on the indexes ..this will also boost the performance ..
Hope that adds to your decision making .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 9, 2009 at 10:13 pm
forgive grammar and typos ...i wrote it in haste ..
Regards.
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 13, 2009 at 1:59 pm
thank you !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply