I recently solved a huge memory grant issue by rewriting a query. Implementing a supporting index wasn't enough to keep SQL Server from turning itself off.