SQLServer: Tracking Down Stat Issues

The old debate of auto update statistics. I've had issues with it in the past, and coming from an old UNIX / Oracle background, I've always been of the opinion that I know best ( probably not, but it's fun to believe that).

The advantage of use sp_updatestats over update statistics is that is looks at sys.sysindexes for the status of indexes and whether or not they need to be updated or not, before the tables/indexes are scanned for updating stats.

Given this, we can then get a look at distribution changes in order to determine how often we should be updating stats on different tables indexes.

The following script gives a look at how many changes have been done since statistics on an object were last updated:

SELECT a.rowcnt AS total_rows,
a.rowmodctr AS rows_updated,
object_name (a.id) AS table_name,
a.name AS stats_object
FROM sys.sysindexes a
WHERE object_name (a.id) IN ( [table name],[table name],...)
ORDER BY 2 DESC,1 DESC

Then just keep track of how fast the rows_updated grows, and use that to get an idea of how often stats should be updated.