Hi,
In the trace I got, I noticed bunch of missing column
statistics events, always on the same table. In the text
field of this event, there is a list of all columns in the
table, which is more than 16. I cannot create statistics
on more than 16 columns. Does this mean that I have to
create statistics on each column, one by one?
Auto-update statistics feature is turned on, and beside
that, we update statistics as part of daily maintenance
plan with sample of 50%.
Thanks,
OJ

Hi OJ,

Do you have auto create statistics on for the database?  SELECT
DATABASEPROPERTY('dbname', 'IsAutoCreateStatistics') should return 1.  SQL
Server will automatically create stats for columns used frequently in the
WHERE clause.

Does your table have any auto stats on it already?  exec sp_helpstats
<tablename>.  Auto created stats are prefixed by _WA_Sys.  Also, auto create
stats can be turned on/off at the table level, are all the columns on?  exec
sp_autostats <tablename>.

Did you want to create one set of statistics of all 16 columns?

If you have a large number of columns and indexes it is possible that
sysindexes cannot hold any further entries for that table - you would need
to have around 247 column/indexes.  Hence further stats cannot be created.

If you are running a 50% update of all statistics daily, the auto update
stats feature will probably never be used. SQL Server tracks modifications
to the table and will automatically update the approriate statistics when a
threshold is reached.  This really depends on the size of your table and the
number of modifications.

Cheers

J.

Show quoteHide quote

How get column headers in QA CSV export
if elseif .. in Store procedure:
Errors in the trace from Enterprise manager
windows 2003 and Sqlserver2000
SQL Mail and Replication Alerts
Sp and Jobs
Backup job Fails every time:
SQL Server Database Convert to MS Access
Replication initializing
Database Backup & Restore