How to clean missing statistics information from system tables.

[Former Member]

Greetings,

     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?

With regards,

Sid.

[kevin_sherlock · Accepted solution]

Missing statistics are stored in the "sysstatistics" table for each database.  Look for "formatid" of 110.

For example:

select  user_name(so.uid),so.name,ss.colidarray

                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.