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

Question

SIMULATION You plan to perform performance testing of db1. You need prevent db1 from reverting to the last known good query plan. To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal. Answer: Automatic tuning options The automatic tuning options available in Azure SQL Database and Azure SQL Managed Instance are: * FORCE LAST GOOD PLAN (automatic plan correction) Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and forces queries to use the last known good plan instead of the regressed plan. * Etc. You can either manually apply tuning recommendations using the Azure portal, or you can let automatic tuning autonomously apply tuning recommendations for you. Azure defaults are set to FORCE_LAST_GOOD_PLAN enabled. We need to disable it. With transact-SQL: ALTER DATABASE SET options (Transact-SQL) Parameters include: <automatic_tuning_option> ::= { AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } ) } Solution with T-SQL 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, expand that instance, and then expand Databases. Step 2: Expand the database that you want, expand Programmability, and then expand Stored Procedures. Step 3: Right-click the stored procedure that you want to run [here: ALTER DATABASE SET] and select Execute Stored Procedure. Step 4: Set the parameters: AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { OFF } ) Step 5: Select OK to execute the stored procedure. If the stored procedure doesn't have any parameters, just select OK. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored- procedure

Options

• taskPrevent db1 from reverting to the last known good query plan.
• prerequisitesSign in to the virtual machine; access to SQL Server Management Studio and the Azure portal.