DP-300 Question #342: Real Exam Question with Answer & Explanation

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

Options

• taskEnsure that any enhancements made to the Query Optimizer through patches are available to db1 and db2 on sql12345678.
• prerequisitesVirtual machine access, SQL Server Management Studio, and Azure portal access.