Does dm_db_stats_properties track statistics properties for “small” tables?

A recent question on dba.stackexchange.com about statistics properties asked the following:

dm_db_stats_properties does not keep track of small tables? what is the work around to find the number of records in the table?

The question posits that the sys.dm_db_stats_properties DMV seems to not keep track of small tables. I believe that is untrue, although there are certainly times when that Dynamic Management View isn’t up-to-date.

Keep Statistics Properties up to Date, They Stink Less!

Keep Statistics Properties up to Date, They Stink Less!

In order to test that hypothesis, I created the following MCVE to show when statistics properties get updated. A simple table, dbo.t is created with a clustered index, primary key, and a standard non-clustered index. A filtered non-clustered index is also created.

Let’s insert a single row into the table, then see if SQL Server updated the statistics properties:

╔══════════════════════════╦══════════╦══════════════╦══════╦═══════════╦═════════════════╗
║           Name           ║ stats_id ║ last_updated ║ rows ║ mod_count ║ unfiltered_rows ║
╠══════════════════════════╬══════════╬══════════════╬══════╬═══════════╬═════════════════╣
║ [dbo].[t].[t_pk]         ║        1 ║ NULL         ║ NULL ║ NULL      ║ NULL            ║
║ [dbo].[t].[t_s]          ║        2 ║ NULL         ║ NULL ║ NULL      ║ NULL            ║
║ [dbo].[t].[t_s_filtered] ║        3 ║ NULL         ║ NULL ║ NULL      ║ NULL            ║
╚══════════════════════════╩══════════╩══════════════╩══════╩═══════════╩═════════════════╝

Ok, so the stats have not been updated. What happens if we force a fullscan statistics update?

╔══════════════════════════╦══════════╦═══════════════════════╦══════╦═══════════╦═════════════════╗
║           Name           ║ stats_id ║     last_updated      ║ rows ║ mod_count ║ unfiltered_rows ║
╠══════════════════════════╬══════════╬═══════════════════════╬══════╬═══════════╬═════════════════╣
║ [dbo].[t].[t_pk]         ║        1 ║ 2019-04-23 15:19:22.9 ║ 1    ║ 0         ║ 1               ║
║ [dbo].[t].[t_s]          ║        2 ║ 2019-04-23 15:19:22.9 ║ 1    ║ 0         ║ 1               ║
║ [dbo].[t].[t_s_filtered] ║        3 ║ NULL                  ║ NULL ║ NULL      ║ NULL            ║
╚══════════════════════════╩══════════╩═══════════════════════╩══════╩═══════════╩═════════════════╝

Ahhh, better. SQL Server updated the statistics objects. But, what gives for the filtered non-clustered index? According to Microsoft Docs:

When no data is read to generate statistics data, the statistics blob is not created, the date is not available, and the last_updated column is NULL. This is the case for filtered statistics for which the predicate does not return any rows, or for new empty tables.

So, lets insert a lot of rows into the table, some of which do contain the value we’re filtering for in the filtered index:

╔══════════════════╗
║ (No column name) ║
╠══════════════════╣
║             3498 ║
╚══════════════════╝

Now, let’s check the stats DMV:

╔══════════════════════════╦══════════╦═══════════════════════╦════════╦═══════════╦═════════════════╗
║           Name           ║ stats_id ║     last_updated      ║  rows  ║ mod_count ║ unfiltered_rows ║
╠══════════════════════════╬══════════╬═══════════════════════╬════════╬═══════════╬═════════════════╣
║ [dbo].[t].[t_pk]         ║        1 ║ 2019-04-23 15:19:22.9 ║      1 ║    889249 ║               1 ║
║ [dbo].[t].[t_s]          ║        2 ║ 2019-04-23 15:19:35.6 ║ 889250 ║         0 ║          889250 ║
║ [dbo].[t].[t_s_filtered] ║        3 ║ 2019-04-23 15:19:33.8 ║   3504 ║         0 ║          889250 ║
╚══════════════════════════╩══════════╩═══════════════════════╩════════╩═══════════╩═════════════════╝

Notice the stats properties for [dbo].[t].[t_s_filtered] have now been updated. However, you may notice the stats object for the primary key still only thinks 1 row exists in the table, even though it shows 889,249 modified rows. To get all the stats up to date, we need to force a stats update:

╔══════════════════════════╦══════════╦═══════════════════════╦════════╦═══════════╦═════════════════╗
║           Name           ║ stats_id ║     last_updated      ║  rows  ║ mod_count ║ unfiltered_rows ║
╠══════════════════════════╬══════════╬═══════════════════════╬════════╬═══════════╬═════════════════╣
║ [dbo].[t].[t_pk]         ║        1 ║ 2019-04-23 15:33:58.6 ║ 889250 ║         0 ║          889250 ║
║ [dbo].[t].[t_s]          ║        2 ║ 2019-04-23 15:33:59.2 ║ 889250 ║         0 ║          889250 ║
║ [dbo].[t].[t_s_filtered] ║        3 ║ 2019-04-23 15:33:59.2 ║   3469 ║         0 ║          889250 ║
╚══════════════════════════╩══════════╩═══════════════════════╩════════╩═══════════╩═════════════════╝

Much better. So the moral of this story is statistics properties are only up-to-date if you force an update.

Check out the rest of our posts on statistics.