Bert Wagner has a few tips for improving query performance by reducing the number of reads:
If SQL Server thinks it only is going to read 1 row of data, but instead needs to read way more rows of data, it might choose a poor execution plan which results in more reads.
You might get a suboptimal execution plan like above for a variety of reasons, but here are the most common ones I see:
- Parameter sniffing
- Use of table variables (via Brent Ozar)
- Outdated statistics (via Kimberly L. Tripp)
If you had a query that previously ran fine but doesn’t anymore, you might be able to utilize Query Store to help identify why SQL Server started generating suboptimal plans.
Click through for a few more ideas as well.