Interview Question #2 (1 minute)

Part of "Query Tuning with Hints & Optimizer Hotfixes (2 hours 15 minutes)"

Question 2

Answer 2



What are query optimizer hotfixes, and how would you enable them?


Query optimizer hotfixes are fixes that the SQL Server team releases for problems in the optimizer.

A lot of times, they’re edge cases. These fixes are released often in cumulative updates by the SQL Server team, so to get the Hotfix, you have to apply the patch, and then you have to enable query optimizer hotfixes. You can enable them for just the query itself using a hint or a trace flag, or you might be able to control it by changing the database compatibility level or a database scope setting {depending on your version}.

You’ve got a lot of different options for how you can turn these on if you are impacted by the bug that the fix applies to.