22-25 April 2026 
When to stop tuning a query
2022TL; DR
In this session, I will walk you through how to tune a very simple but surprisingly slow Transact-SQL query and how to decide when to stop this process.
Session Details
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.
At 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. Here, it will be highlighted the difference between workarounds with hints and optimizer tricks, and proper solutions based on root cause elimination.
Feedback link: https://sqlb.it/?7024
At 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. Here, it will be highlighted the difference between workarounds with hints and optimizer tricks, and proper solutions based on root cause elimination.
Feedback link: https://sqlb.it/?7024
3 things you'll get out of this session
Speakers
Milos Radivojevic's previous sessions
When to stop tuning a query
In this session, I'll demonstrate various tuning techniques and the challenges associated with each. I will guide you through the process of tuning of a single TSQL query and help you understand when to know it's optimized enough.
Dealing with Very Large Tables in SQL Server
What challenges do you face when dealing with tables that are over a terabyte in size? How do you index them? What do their statistics look like? How do you update them, and how do you fight against index fragmentation? How does all of this affect the estimation of the amount of data and how to tune queries involving very large tables?
Parameter Sniffing Problem with Stored Procedures
Your stored procedure is slow in the application, but works perfect in SSMS! In this presentation it will be clarified why this happens and we will offer several solutions for this problem and discuss which solution is applicable in which scenario.
Transact SQL Performance Tips
In this session we will advice how to avoid common developer mistakes and show how bad designed queries reduce the SQL Server optimizer's capabilities for choosing an optimal execution plan.
TSQL Performance Tips
SQL Server optimizer doesn't use and index seek for execution of your query although the query is high selective? What is better, when and why: LIKE vs: SUBSTRING, IN vs. EXISTS, SUBQUERY vs. JOIN. Why you should not use the UPPER or LOWER functions?
Parameter Sniffing Problem with Stored Procedures
In this presentation it will be clarified when and why parameter sniffing is a problem in SQL Server stored procedures. Of course we will offer several solutions for this problem and discuss which solution is applicable in which scenario.
TSQL Performance Recommendations
In this section we will show how to avoid performance problems caused by poor query design (functions in WHERE clause, data type conversions…) and explain how local variables and parameters affect the generation of execution plan.