If you’ve got good indexes to support your query, and statistics to help SQL Server guess how many rows will come back, how can SQL Server still come up with terribly incorrect row estimates?
To demonstrate, I’ll use the 2018-06 version of the Stack Overflow database, but any recent version will work as long as you understand the problem with this demo query:
SELECT * FROM dbo.Users WHERE CreationDate > ‘2018-05-01’ AND Reputation > 100 ORDER BY DisplayName;
I’m asking SQL Server to find users created in the last month (because I’m dealing with the database export that finished in June