Sql Update Stats Tip

Auto Update Stats only works with the default sampling which are changes that affect 20% of the total number of rows. For very large tables or even for smaller tables with a highly jagged distribution of data, we often need to manually update the stats using the FULLSCAN option. Otherwise the stats will remain highly skewed and the cardinality estimates that the Sql Optimizer depends on will be unreliable.

Note that FULLSCAN is not the panacea of all skewed data and if the data distribution is very jagged, it may not help much in updating the stats. That's where creating filtered stats comes handy, but that's another topic.

Command to check the histogram of the stats for an index, run
DBCC SHOW_STATISTICS('tablename', 'indexname')

Another option (Sql2008 and higher) to automatically trigger updating the stats (rather than the 20% default threshold) on say large tables is by turning on the trace 2371 like so

DBCC TRACEON(2371, -1)  -- the -1 is for all sessions
GO

DBCC TRACESTATUS
GO

The downside of turning on this trace is it results in recompilation of all queries accessing the table.


From SQL2008 onwars, the DMV sys.dm_db_stats_properties is useful in determining how much changes have taken place in the table before the update stats would be triggered. Of particular interest is the modification_counter column using the query

--Examine stats of a table
SELECT
     s.name,
     p.object_id,
     p.stats_id,
     p.last_updated,
     p.rows,
     p.rows_sampled,
     p.steps,
     p.unfiltered_rows,
     p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties
  (s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID('tablename')


References:
http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx
http://support.microsoft.com/kb/2754171