Speed Up Cross Apply Without Index Hint

I have a very small table with 12 rows in it that can be created with the following statement: CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY, ScoreGoal float NOT NULL ); I have another table with ≈100M rows in it that can be created with the following statments: CREATE TABLE dbo.SlowCrossApply(RecordKey nvarchar(12) NOT NULL, Score1 decimal(3, 2) NOT NULL, Score2 decimal(3, 2) NOT NULL, Score3 decimal(3, 2) NOT NULL, Score4 decimal(3, 2) NOT NULL, Score5 decimal(3, 2) NOT NULL, Score6 decimal(3, 2) NOT NULL, FromToday bit NOT NULL ); ALTER TABLE dbo.SlowCrossApply ADD CONSTRAINT i01PK PRIMARY KEY CLUSTERED(RecordKey ASC) WITH(FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE ); CREATE NONCLUSTERED INDEX i02TodayRecords ON dbo.SlowCrossApply(FromToday) INCLUDE (Score1, Score2, Score3, Score4, Score5, Score6) WHERE FromToday = 1 WITH(FILLFACTOR = 100, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE ); i02TodayRecords has ≈1M rows in it. When I run the following query—I struggled formatting it to both look clean and prevent a horizontal scrollbar—it takes over 5 minutes to finish: SELECT b.RecordKey, COALESCE(NULLIF(ROUND(((0.95 * (ROW_NUMBER() OVER(PARTITION BY a.Prefix ORDER BY b.Score6 ASC ) - 1 ) ) / COALESCE(NULLIF(COUNT(*) OVER(PARTITION BY a.Prefix) - 1, 0 ), 1 ) ) + 0.005, 2 ), 0.96 ), 0.95 ) AS NewScore FROM (SELECT LEFT(s.RecordKey, 2) AS Prefix, CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS int) AS Quant FROM dbo.SlowCrossApply AS s CROSS JOIN dbo.SmallTable AS sm WHERE s.FromToday = 1 AND sm.ScoreMonth = MONTH(GETDATE()) GROUP BY LEFT(s.RecordKey, 2), sm.ScoreGoal ) AS a CROSS APPLY (SELECT TOP(a.Quant) s2.RecordKey, s2.Score6 FROM dbo.SlowCrossApply AS s2 WHERE s2.FromToday = 1 AND s2.Score6 > 0 AND LEFT(s2.RecordKey, 2) = a.Prefix ORDER BY s2.Score6 DESC ) AS b; The outer subquery returns only 10 rows; and if I supply a hint to use i02TodayRecords or put the results of the outer subquery in a table variable, it takes less than 1 second. The final result returns just over 8000 rows. The execution plan shows that 64% of the cost is due to an eager index spool on the clustered index in the Cross Apply portion. I know the index hint works (at least for now), but I'm hoping to avoid using one. Ideally, I wouldn't go the table variable route either. Is there something I can do to get the query optimizer to "know" to utilize i02TodayRecords? I realize there is *a lot* more information that is probably important, and I'll do my best to supply said information if requested. Some potentially useful information: the indexes have less than 1% fragmentation. The statistics for both indexes have been updated via a FULLSCAN, and the database is set to have simple parameterization and parameter sniffing—unfortunately, I can't changes those settings. In regards to the latter, the query optimizer did *not* replace any values with parameters unlike other simple queries I have run where I was forced to use a hint to utilize a particular filtered index.