Q1. Which configuration in SQL Server 2016 enables all query optimizer hotfixes from previous versions?

•  Enabling Priority Boost
•  Enabling Query Store
•  Database compatibility level 130
•  Running the Database Tuning Advisor

[Show Answer] Database compatibility level 130. Setting the database compatibility level to 130 enables all pre-SQL Server 2016 query optimizer hotfixes.

Q2. Fill in the blank

ALTER DATABASE _________  CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

[Show Answer] SCOPED

Q3. If you enable Query Optimizer Hotfixes at the database scope and want to test how a query compiles with it OFF, what could you do?

•  Use the legacy cardinality estimator
•  Enable Trace Flag 4199 globally instead
•  Disable Trace Flag 4199 for your session
•  Compile the query in the context of a database where Query Optimizer Hotfixes is off, using three-part naming

[Show Answer] Compile the query in the context of a database where Query Optimizer Hotfixes is off, using three-part naming. Since this is enabled at the database scope, you can USE a different database where the hotfixes aren’t enabled, then use three part naming and compile an execution plan.

Q4. If you enable Trace Flag 4199 globally and want to test how a query compiles with it DISABLED, what could you do?

•  Disable Trace Flag 4199 for your session
•  Disable the trace flag globally
•  Compile the query in the context of a database where Query Optimizer Hotfixes is off, using three-part naming
•  Use the legacy cardinality estimator

[Show Answer] Disable the trace flag globally. You can disable the trace flag globally and test it. This has an obvious downside of the trace flag being disabled for everyone while you test, but it’s an option. While you can enable a trace flag for just your session, you can’t DISABLE a trace flag for an individual session if it has been enabled globally.

Back to: Query Tuning with Hints & Optimizer Hotfixes (2 hours 15 minutes) > Query optimizer hotfixes