In this session, you will watch me tune a simple, but surprisingly slow Transact-SQL query. I'll demonstrate various tuning techniques and the challenges associated with each. I will guide you through the process of tuning it and help you understand when to know it's optimized enough.
Initially, I will apply several query hints to override certain optimizer decisions, aiming to mitigate the performance issue.
Next, I will rewrite the query and use several tricks to lead the optimizer toward more accurate estimations and optimal plan decisions. Moreover, you'll see how upgrading to SQL Server 2019 can break this workaround.
Finally, I will explain why SQL Server initially opted for an improper execution plan for this seemingly straightforward query and address the root cause. I'll emphasize the distinction between temporary fixes involving hints and optimizer strategies, and genuine solutions rooted in eliminating the underlying issue.
