Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.
Questions
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
Q2. Fill in the blank
ALTER DATABASE _________ CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
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
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
Scroll down for the answer key :point_down:
Answer Key
- A1: 3. Database compatibility level 130. Setting the database compatibility level to 130 enables all pre-SQL Server 2016 query optimizer hotfixes.
- A2: SCOPED
- A3: 4. 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.
- A5: 2. 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.