My question relates to a moderately big table (~40GB data space) that is a heap
(Unfortunately, I am not allowed to add a clustered index to the table by the application owners)
An auto created statistic on an Identity column (
ID) was created, but is empty.
- Auto create stats & auto update stats are on
- Modifications have happened in the table
- There are other (auto created) statistics that are getting updated
- There is another statistic on the same column created by an index (duplicate)
- Build: 12.0.5546
The actual question
To my understanding, all stats could be used and modifications are tracked, even if there are two statistics on exactly the same columns (duplicates), so why does this statistic remain empty?
DB stat info
Column Information where the statistic is created on
[ID] [int] IDENTITY(1,1) NOT NULL
select * from sys.stats where name like '%_WA_Sys_0000000A_6B7099F3%';
Getting some info on another statistic
select * From sys.dm_db_stats_properties (1802541555, 3)
In comparison with my empty stat:
Stats + Histogram from "generate scripts":
/****** Object: Statistic [_WA_Sys_0000000A_6B7099F3] Script Date: 2/1/2019 10:18:19 AM ******/ CREATE STATISTICS [_WA_Sys_0000000A_6B7099F3] ON [dbo].[table]([ID]) WITH STATS_STREAM = 0x01000000010000000000000000000000EC03686B0000000040000000000000000000000000000000380348063800000004000A00000000000000000000000000
When creating a copy of the stats, no data is inside
CREATE STATISTICS [_WA_Sys_0000000A_6B7099F3_TEST] ON [dbo].[table]([ID]) WITH STATS_STREAM = 0x01000000010000000000000000000000EC03686B0000000040000000000000000000000000000000380348063800000004000A00000000000000000000000000
When manually updating the stat they do get updated.
UPDATE STATISTICS [dbo].[Table]([_WA_Sys_0000000A_6B7099F3_TEST])