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!
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL BEGIN DROP TABLE dbo.t; END CREATE TABLE dbo.t ( i int NOT NULL CONSTRAINT t_pk PRIMARY KEY CLUSTERED IDENTITY(1,1) , s varchar(3) NOT NULL INDEX t_s ) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE); CREATE INDEX t_s_filtered ON dbo.t(s) WHERE s = 'a'; |
Let’s insert a single row into the table, then see if SQL Server updated the statistics properties:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO dbo.t (s) VALUES ('xyz'); SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name) , ddsp.stats_id , last_updated = CONVERT(datetime2(1), ddsp.last_updated) , ddsp.rows , mod_count = ddsp.modification_counter , ddsp.unfiltered_rows FROM sys.schemas sch INNER JOIN sys.objects o ON sch.schema_id = o.schema_id INNER JOIN sys.stats s ON o.object_id = s.object_id OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp WHERE o.is_ms_shipped = 0; |
╔══════════════════════════╦══════════╦══════════════╦══════╦═══════════╦═════════════════╗ ║ 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?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
UPDATE STATISTICS dbo.t(t_pk) WITH FULLSCAN; UPDATE STATISTICS dbo.t(t_s) WITH FULLSCAN; UPDATE STATISTICS dbo.t(t_s_filtered) WITH FULLSCAN; SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name) , ddsp.stats_id , last_updated = CONVERT(datetime2(1), ddsp.last_updated) , ddsp.rows , mod_count = ddsp.modification_counter , ddsp.unfiltered_rows FROM sys.schemas sch INNER JOIN sys.objects o ON sch.schema_id = o.schema_id INNER JOIN sys.stats s ON o.object_id = s.object_id OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp WHERE o.is_ms_shipped = 0; |
╔══════════════════════════╦══════════╦═══════════════════════╦══════╦═══════════╦═════════════════╗ ║ 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:
|
1 2 3 4 5 6 7 8 |
INSERT INTO dbo.t (s) SELECT CASE WHEN CONVERT(int, CRYPT_GEN_RANDOM(2)) % 255 = 0 THEN 'a' ELSE CRYPT_GEN_RANDOM(2) END FROM sys.syscolumns sc1 CROSS JOIN sys.syscolumns sc2; SELECT COUNT(1) FROM dbo.t WHERE t.s = 'a'; |
╔══════════════════╗ ║ (No column name) ║ ╠══════════════════╣ ║ 3498 ║ ╚══════════════════╝
Now, let’s check the stats DMV:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name) , ddsp.stats_id , last_updated = CONVERT(datetime2(1), ddsp.last_updated) , ddsp.rows , mod_count = ddsp.modification_counter , ddsp.unfiltered_rows FROM sys.schemas sch INNER JOIN sys.objects o ON sch.schema_id = o.schema_id INNER JOIN sys.stats s ON o.object_id = s.object_id OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp WHERE o.is_ms_shipped = 0; |
╔══════════════════════════╦══════════╦═══════════════════════╦════════╦═══════════╦═════════════════╗ ║ 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
UPDATE STATISTICS dbo.t(t_pk) WITH FULLSCAN; UPDATE STATISTICS dbo.t(t_s) WITH FULLSCAN; UPDATE STATISTICS dbo.t(t_s_filtered) WITH FULLSCAN; SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name) , ddsp.stats_id , last_updated = CONVERT(datetime2(1), ddsp.last_updated) , ddsp.rows , mod_count = ddsp.modification_counter , ddsp.unfiltered_rows FROM sys.schemas sch INNER JOIN sys.objects o ON sch.schema_id = o.schema_id INNER JOIN sys.stats s ON o.object_id = s.object_id OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp WHERE o.is_ms_shipped = 0; |
╔══════════════════════════╦══════════╦═══════════════════════╦════════╦═══════════╦═════════════════╗ ║ 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.