Usage and good practices with UPDATE STATISTICS feature in SQL 2005

UPDATE STATISTICS is very helpful to get the performance by updating the distribution of key values for one or more statistics groups or set of collections in the specified table or indexed view. So how this is handled within the SQL Server, you may be aware that SQL 2000 version uses a counter (rowmodctr) to track row modification  and within SQL 2005 this counter is used to tracks changes on column level.

To perform UPDATE STATISTICS within the database maintenance plan when you trigger an update stats with the default sample set those stats are overwritten again with 'less' accurate stats within SQL 2000 version, but the difference in SQL Server 2005 is when you use sp_updatestats only statistics that require an update are actually updated. Microsoft recommends to set AUTO_UPDATE_STATISTICS ON on all the user databases to enable the optimizer to build any missing statistics required by a query for optimization are automatically built during query optimization. I would also suggest to set AUTO_CREATE_STATISTICS options against those user databases.

If you are using auto create and auto update statistics and you are getting a bad query plan because the statistics are not accurate or current, do the following:

• As I have seen that for a large majority of SQL Server installations, the most important best practice is to use auto create and auto update statistics database-wide.
• Auto create and auto update statistics are on by default.
• If you observe bad plans and suspect that missing or out of date statistics are at fault, verify that auto create and auto update statistics are on.
• In this regard SQL Server 2005 gives the option to update statistics async so the query triggering the update will not be blocked while updating the statistics, but not always.
• It is always better to perform the manual UPDATE STATS against a table that will have huge updates & modifications on day to day basis but nevertheless you might not even want this to happen.
• If you find those statistics are not accurate or current enough then use CREATE STATISTICS.. WITH FULLSCAN, NORECOMPUTE statement.
• If so you can perform a periodic execution of UPDATE STATS using FULLSCAN, NORECOMPUTE method before the rebuild of indexes job is taken care of, but do not perform on a big databases within your production environment.

You might ask that how often you have to perform this, the frequency with which you should update statistics depends on your application and may require some experimentation to determine. One of the Technet article refer that "A good starting point for the frequency of fullscan update is that if the table under consideration has a high update rate, run fullscan statistics update nightly. If the table has a low update rate, run fullscan statistics update weekly".

Also look at the queries that are not optimized enough to provide better performance, say for instance if you use a local variable in a query process instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer typically will be able to pick a better query plan. Try with WITH FULLSCAN, NORECOMPUTE periodically, when automatic statistics with the default sampling rate are not sufficient, you can control statistics sampling rates, and creation and update time, explicitly.

Further references on UPDATE STATISTICS practices are as follows: QueryRecompilation article is a good reference in this case. Also with AsynchronousStatistics article & MSDNBlog - on SQL optimizer.