How can I investigate why Sql Server is chosing the “wrong” Index?

I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes:

  • IX_SiloId_ChangedTime_IncludeTime
  • IX_SiloId_Time_IncludeContent

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
(1, '2020-12-31'), -- 1000 total values, though it's still the same problem with just one

FROM    (Transaction) t
    ON t.SiloId = q.SiloId
    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):

enter image description here

Query plan without forced index:

enter image description here (this one is live, as it takes too long)