I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes:
I run these 2 statements before I proceed with the actual query to update statistics
Update STATISTICS dbo.(Transaction) IX_SiloId_ChangedTime_IncludeTime WITH FULLSCAN Update STATISTICS dbo.(Transaction) IX_SiloId_Time_IncludeContent WITH FULLSCAN
This is my query:
DECLARE @Query SiloTimeQueryTableType -- (SiloId, Time) with primary key clustered on SiloId INSERT INTO @Query VALUES (1, '2020-12-31'), -- 1000 total values, though it's still the same problem with just one SELECT t.* FROM (Transaction) t INNER JOIN @Query q ON t.SiloId = q.SiloId WHERE t.Time >= q.Time
Now what happens is for whatever reason Sql Server choses
IX_SiloId_ChangedTime_IncludeTime. It then takes forever. If I use
WITH (INDEX(IX_SiloId_Time_IncludeContent)) I get the result right away.
The correct index is quite obvious here, but yet SQL Server choses the one that is not even indexed on Time.
I cannot understand this behaviour, but from what I read it is best to avoid hints for Indexes, though I made this Index with this query in mind.
So the question is: what can I do to try to figure out why SQL Server prefers the “wrong” index even though a much better one exists and I just run full statistics update?
Query plan for the forced index (here from the temp table instead of TVP to check if this changes anything as the answer suggested, the result seems to be the same):
Query plan without forced index:
https://www.brentozar.com/pastetheplan/?id=ByFshGAAP (this one is live, as it takes too long)