SIMULATION You need to ensure that any enhancements made to the Query Optimizer through patches are available to db1 and db2 on sql12345678. To complete this task, sign in to the virtual machine…

Explanation: Enabling Query Optimizer Hotfixes via Database Scoped Configuration --- Overall Goal Microsoft SQL Server's Query Optimizer improves query execution plans. Over time, Microsoft ships hotfix patches that further enhance the optimizer - but these are opt-in per…

Submitted by the_admin· Mar 6, 2026Optimize query performance

Question

SIMULATION You need to ensure that any enhancements made to the Query Optimizer through patches are available to db1 and db2 on sql12345678. To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal. Answer: Stored procedure execution You can use the SQL Server Management Studio (SSMS) user interface or Transact-SQL in an SSMS query window to execute a stored procedure. Always use the latest version of SSMS. Use SQL Server Management Studio Step 1: In Object Explorer, connect to an instance of SQL Server or Azure SQL Database [here sql12345678], expand that instance, and then expand Databases. Step 2: Expand the database that you want [Here first db1], expand Programmability, and then expand Stored Procedures. Step 3: Right-click the stored procedure that you want to run [Here: ALTER DATABASE SCOPED CONFIGURATION] and select Execute Stored Procedure. Step 4: Add parameter: QUERY_OPTIMIZER_HOTFIXES = ON Step 5: Select OK to execute the stored procedure. If the stored procedure doesn't have any parameters, just select OK. Step 6: Repeated step 2 to step 5 for db2. Note: The default approach that SQL Server uses for the query optimizer enables any fixes for the latest database compatibility level for a given product release. This means any fixes for the query optimizer will be used up to the compatibility level of the database, but any hotfixes beyond that compatibility level will not be used. Microsoft provides an option to enable query optimizer hotfixes using the scoped configuration QUERY_OPTIMIZER_HOTFIXES. Use the following command to enable optimizer hotfixes: ALTER DATABASE SCOPED CONFIGURATION QUERY_OPTIMIZER_HOTFIXES = ON; This command configures the database to use all query optimizer hotfixes. For example, optimizations that were included in a recent database cumulative update will apply if the administrator has altered the scope to include query optimizer hotfixes. Using QUERY_OPTIMIZER_HOTFIXES applies at the database level, meaning the change would have to be made for each database. Reference: https://infohub.delltechnologies.com/en-US/l/performance-best-practices-4/sql-server-2019- query-optimizer-hotfixes https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration- transact-sql

Explanation

Explanation: Enabling Query Optimizer Hotfixes via Database Scoped Configuration

Overall Goal

Microsoft SQL Server's Query Optimizer improves query execution plans. Over time, Microsoft ships hotfix patches that further enhance the optimizer - but these are opt-in per database to avoid breaking existing workloads with unexpected plan changes.

The task enables QUERY_OPTIMIZER_HOTFIXES = ON at the database scope for both db1 and db2, so all cumulative update (CU) optimizer improvements are applied. This is a database-level setting, not server-level, which is why you must repeat the procedure for each database.

Why This Approach Is Correct

ALTER DATABASE SCOPED CONFIGURATION is the correct T-SQL mechanism because:

It targets a single database (scope isolation - no risk of affecting other databases on the same instance)
It persists across restarts
It does not require the database to be taken offline

The exam frames this as "executing a stored procedure" through the SSMS UI, which is the GUI wrapper around running this T-SQL statement.

Step-by-Step Reasoning

Step 1 - Sign in to the VM Required because SSMS is a client tool installed locally. You cannot administer SQL Server without a connected client. Without this, nothing else is accessible.

Step 2 - Open SSMS and connect to sql12345678 You must connect to the specific named instance. If you connect to the wrong instance, any configuration changes land on the wrong server. Always verify the instance name in Object Explorer before proceeding.

Step 3 - Expand Databases Navigation prerequisite. You must drill into the correct database node before scoped configurations are accessible. The setting is database-scoped, so you cannot apply it from the instance root level.

Step 4 - Expand db1 -> Programmability -> Stored Procedures The SSMS UI surfaces ALTER DATABASE SCOPED CONFIGURATION as an executable stored procedure under this path. Skipping this and trying to right-click at the database root won't give you the correct option.

Step 5 - Right-click ALTER DATABASE SCOPED CONFIGURATION -> Execute Stored Procedure This opens the parameter input dialog. If you skip this and just right-click something else, you won't get the right execution context - you'd be running the wrong configuration command.

Step 6 - Set parameter QUERY_OPTIMIZER_HOTFIXES = ON, click OK This is the critical configuration value. The default is OFF - meaning patches beyond the base compatibility level are excluded. Setting it ON tells SQL Server: "Apply all optimizer hotfixes from cumulative updates." If you click OK without setting the parameter, the procedure runs with OFF (default), accomplishing nothing.

Steps 7-9 - Repeat for db2 Because this is a database-scoped setting, it does not propagate automatically to other databases. If you skip this, db2 remains unpatched while db1 is patched - an inconsistent state that could cause different query behavior between the two databases.

What Goes Wrong If Steps Are Skipped or Reordered

Mistake	Consequence
Skip VM sign-in	No SSMS access; can't connect to the instance
Wrong instance in SSMS	Config applied to wrong SQL Server
Skip parameter value	Default `OFF` is submitted; hotfixes stay disabled
Only do `db1`, skip `db2`	`db2` misses optimizer patches; inconsistent behavior
Run at instance level instead of DB level	Not possible - this setting has no instance-level equivalent

Memory Tip

Think: "Scoped = per database, opt-in = must be ON."

The word scoped in ALTER DATABASE SCOPED CONFIGURATION is the clue - anything scoped must be applied to each database individually. And since Microsoft defaults to OFF to protect existing query plans, you always have to explicitly opt in with = ON.

A quick T-SQL alternative (useful to know for the exam) that achieves the same result:

-- Run in context of db1
USE db1;
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

-- Run in context of db2
USE db2;
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

This is exactly what the SSMS GUI procedure does under the hood.

Topics

#Query Optimizer#Database Scoped Configuration#Performance Optimization#SQL Server Administration

Community Discussion

No community discussion yet for this question.

Full DP-300 Practice