SQL Server has a statistic on an index which is estimating the number of rows incorrectly, this table (and predicates) are used from a lot of different queries. This causes these queries to perform poorly as the statistic expects the query to return 5 rows, whereas it returns 431268.
The query:
SELECT COUNT(BuildRecord.BuildRecordID)
FROM dbo.BuildRecord
WHERE BuildRecord.ContainedToDT IS NULL
AND BuildRecord.RfBuildRecordTypeID = 2
AND BuildRecord.IsEdited = 0
The statistic was updated with a FULLSCAN yesterday:
The index has <1% fragmentation.
In the BuildRecord table, the ContainedToDT is a NULLABLE DATETIMEOFFSET, RfBuildRecordTypeID is a NOT-NULL SMALLINT and IsEdited is a NOT-NULL TINYINT.
The index is as follows:
CREATE NONCLUSTERED INDEX [IM_ContainedToDT_RfBuildRecordTypeID_IsEdited]
ON [dbo].[BuildRecord] ([ContainedToDT] ASC, [RfBuildRecordTypeID] ASC, [IsEdited] ASC)
INCLUDE([InvPackCreatedID], [InvPackConsumedID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
SQL Server version is Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) Compatibility mode is SQL Server 2016 (130)
Any suggestions or thoughts would be appreciated.
