I was testing on Stackoverflow database to find out possible cases wherein SQL Server doesn't recommend index in the execution plan however if we introduce one, it would help in great way! Did it quite easily for Group by, Order by Clause and for aggregate function(count function - smallest copy of table). I wrote a random query wherein I knew that introducing supportive index will certainly help however missing index recommendation will be only on join condition and not on the order by clause. Query is as below: select top 100 Location from Users U join Badges B on B.UserId = U.Id order by Location desc Below indexes were introduced to improve performance: create index Location on Users(Location) go create index UsersId on Badges(UserId) go Indexes used by optimizer as expected for above query: Logical reads and time stats are as below: Now, I wanted to test the performance with only index on Users table at Location column and no Index on Badges (UserId) Table, here performance becomes terrible(takes almost 7 minutes): Logical reads and Time stats are as below: Index at Users tables are very much used, as evident from execution plan and logical reads however doing clustered index scan and Table Spool (Lazy Spool) causes most of the issue. *All above tests are conducted on SQL Server 2019 in SQL Server 2016 compatibility mode(130).* If someone could please advise on underlying issue, would be of great help. One more thing to note here, when there is no non-clustered supportive index on either of these two table, same query finishes in 9 seconds. Below is the execution plan: Logical Read and Time Stats: For testing purpose, I changed compatibility level to 2019(150) and to my surprise - same previous query which had index only on Users (Location) table and not on Badges table, finished in 2 seconds which was taking 7 minutes in SQL Server 2016 compatibility(130) mode: Logical Stats and Time Stats: In 2019 compatibility mode, all the operators before Parallelism are in *batch mode*. Any input in this regard would help me in understanding this behavior.

I have a query: select * from Aview where field=20 order by id desc This returns 2700 rows from the view in about 1 second. Adding 'top 20' to the query makes MSSQL return in 43 seconds!! This has been a HARD to reproduce issue, and doing a rebuild of statistics fixes the issue for a couple of days, but then it come back. I've been working with SQL for decades and I've never once seen a situation where adding a 'top' causes the time to increase. Looking at the execution plan, it seems to be doing a lazy spool of 960 MILLION rows if you do the top 20, but not if you don't.