When to Stop Tuning a Query

Milos Radivojevic

In this session, you will see me tuning a very simple but surprisingly slow Transact-SQL query. You’ll see different tuning techniques and the challenges associated with them. I will walk you through how to tune it and how to decide when to stop.

In the beginning, I will apply several query hints to overrule some optimizer decisions to mitigate the performance issue.

I will then rewrite the query and use tricks to lead the optimizer to better estimations and plan decisions. Additionally, you’ll see how upgrading to SQL Server 2019 breaks this workaround.

Finally, I will provide reasons why SQL Server initially decided to use an improper execution plan for this simple query and fix the root cause. I’ll highlight the difference between workarounds with hints and optimizer tricks, and proper solutions based on root cause elimination.