stats_date is null

Question

If I run this query on our system:

SELECT STATS_DATE(t.object_id, s.stats_id) AS stdate, * 
from    sys.stats s WITH (NOLOCK)
INNER JOIN sys.tables t WITH (NOLOCK) ON t.object_id = s.object_id
WHERE t.name LIKE 'mytable%'

I sometimes see that the stats_date function returns NULL. This should not be possible according to BOL, except if there is an error. It does not state which error.

The table is quite big, 11 billion rows. If I run:

UPDATE STATISTICS fct.mytable ( pk_mytable)

the stats_date comes back again as not null.

Why is the date null, and how can I prevent that?

It is only the stats for the primary key that is NULL.

If I run:

DBCC SHOW_STATISTICS ('fct.MyTable', 'IX_MyTableID')

it comes back with the 3 result sets that I expect. If I run:

DBCC SHOW_STATISTICS ('fct.MyTable', 'PK_MyTable')

It returns:

score 2 · Accepted Answer · answered Nov 04 '16 at 16:59

Per MSDN it can return null on error, although why you would experience an error with this I am not sure. Have you considered doing an outer apply to the sys.dm_db_stats_properties function to see if you get the same results?

SELECT  t.name ,
        s.object_id ,
        s.stats_id ,
        c.name ,
        sc.stats_column_id ,
        s.name ,
        sp.last_updated ,
        sp.rows_sampled ,
        sp.modification_counter ,
        sp.steps ,
        sp.rows
FROM    [sys].[stats] AS [s]
        INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id
                                            AND s.object_id = sc.object_id
        INNER JOIN sys.columns c ON c.object_id = sc.object_id
                                    AND c.column_id = sc.column_id
        INNER JOIN sys.tables t ON c.object_id = t.object_id
        OUTER APPLY sys.dm_db_stats_properties([s].[object_id],
                                                [s].[stats_id]) AS [sp]
WHERE   t.name LIKE 'mytable%';

stats_date is null

1 Answers1