How to clean missing statistics information from system tables.
After enabling 'capture missing statistics' our master database filled up in the past. We would like to enable this option periodically and then collect that information, analyse it to create or update existing indexes and also clean the missing stats from system tables before we enable the option to start analysing again.
Is there any way to clean old missing statistics information from system tables in ASE 16?
Kevin Sherlock replied
Missing statistics are stored in the "sysstatistics" table for each database. Look for "formatid" of 110.
from sysobjects so INNER JOIN sysstatistics ss
ON so.id = ss.id
where so.type = "U"
and so.sysstat2 & 1024 != 1024
and so.sysstat2 & 2048 != 2048 /* regular tables */
and ss.formatid = 110 /* missing statistics formatid */
One could write a "delete" statement based on the logic above and create a stored proc (while "allow system table updates" is set on) that could delete these rows from sysstatistics. The proc could accept a table name (and even a column name), or such that would control doing this for certain tables/columns, or all tables/columns etc.