Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 151:

  You have an Azure SQL database.

  Users report that the executions of a stored procedure are slower than usual. You suspect that a regressed query is causing the performance issue.

  You need to view the query execution plan to verify whether a regressed query is causing the issue. The solution must minimize effort.

  What should you use?

  A. Performance Recommendations in the Azure portal

  B. Extended Events in Microsoft SQL Server Management Studio (SSMS)

  C. Query Store in Microsoft SQL Server Management Studio (SSMS)

  D. Query Performance Insight in the Azure portal

  Correct Answer: C

  Use the Query Store Page in SQL Server Management Studio.

  Query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

  Since the Query Store retains multiple execution plans per query, it can enforce policies to direct the Query Processor to use a specific execution plan for a query. This is referred to as plan forcing. Plan forcing in Query Store is provided by

  using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

  Reference:

  https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using- the-query-store

• Question 152:

  You have SQL Server on an Azure virtual machine that contains a database named DB1.

  You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as shown in the following exhibit:

  

  What should you do to ensure that the query uses the execution plan which executes in the least amount of time?

  A. Force the query execution plan for plan 1221065.

  B. Run the DBCC FREEPROCCACHE command.

  C. Force the query execution plan for plan 1220917.

  D. Disable parameter sniffing.

  Correct Answer: A

  As per exhibit, the execution plan 1221065 has lower execution time compared to plan 1220917.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios

• Question 153:

  You have an Azure SQL database named DB1. You run a query while connected to DB1.

  You review the actual execution plan for the query, and you add an index to a table referenced by the query.

  You need to compare the previous actual execution plan for the query to the Live Query Statistics.

  What should you do first in Microsoft SQL Server Management Studio (SSMS)?

  A. For DB1, set QUERY_CAPTURE_MODE of Query Store to All.

  B. Run the SET SHOWPLAN_ALL Transact-SQL statement.

  C. Save the actual execution plan.

  D. Enable Query Store for DB1.

  Correct Answer: C

  The Plan Comparison menu option allows side-by-side comparison of two different execution plans, for easier identification of similarities and changes that explain the different behaviors for all the reasons stated above. This option can compare between:

  Two previously saved execution plan files (.sqlplan extension). One active execution plan and one previously saved query execution plan. Two selected query plans in Query Store.

• Question 154:

  You have an Azure SQL database.

  You discover that the plan cache is full of compiled plans that were used only once.

  You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.

  

  You need relieve the memory pressure. What should you configure?

  A. LEGACY_CARDINALITY_ESTIMATION

  B. QUERY_OPTIMIZER_HOTFIXES

  C. OPTIMIZE_FOR_AD_HOC_WORKLOADS

  D. ACCELERATED_PLAN_FORCING

  Correct Answer: C

  OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

  Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. The default is OFF. Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a

  compiled plan stub will be stored in cache when a batch is compiled for the first time. Plan stubs have a smaller memory footprint compared to the size of the full compiled plan.

  Incorrect Answers:

  A: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY } Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database.

  B: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

  Enables or disables query optimization hotfixes regardless of the compatibility level of the database. The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM).

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

• Question 155:

  You deploy a database to an Azure SQL Database managed instance.

  You need to prevent read queries from blocking queries that are trying to write to the database.

  Which database option should set?

  A. PARAMETERIZATION to FORCED

  B. PARAMETERIZATION to SIMPLE

  C. Delayed Durability to Forced

  D. READ_COMMITTED_SNAPSHOT to ON

  Correct Answer: D

  In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  1.

  The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

  2.

  The SNAPSHOT isolation level.

  If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

  Incorrect Answers:

  A: When the PARAMETERIZATION database option is set to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries. This means that any literal values that are contained in a query are substituted with parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not.

  B: You can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED. This process is referred to as forced parameterization.

  C: Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system.

  Some of the cases in which you could benefit from using delayed transaction durability are:

  1.

  You can tolerate some data loss.

  2.

  You are experiencing a bottleneck on transaction log writes.

  3.

  Your workloads have a high contention rate.

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

• Question 156:

  A data engineer creates a table to store employee information for a new application. All employee names are in the US English alphabet. All addresses are locations in the United States. The data engineer uses the following statement to create the table.

  

  You need to recommend changes to the data types to reduce storage and improve performance. Which two actions should you recommend? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

  A. Change Salary to the money data type.

  B. Change PhoneNumber to the float data type.

  C. Change LastHireDate to the datetime2(7) data type.

  D. Change PhoneNumber to the bigint data type.

  E. Change LastHireDate to the date data type.

  Correct Answer: AE

• Question 157:

  You have an Azure SQL database.

  You identify a long running query.

  You need to identify which operation in the query is causing the performance issue.

  What should you use to display the query execution plan in Microsoft SQL Server Management Studio (SSMS)?

  A. Live Query Statistics

  B. an estimated execution plan

  C. an actual execution plan

  D. Client Statistics

  Correct Answer: A

  https://www.mssqltips.com/sqlservertip/3685/live-query-statistics-in-sql-server-2016/

• Question 158:

  You have a version-8.0 Azure Database for MySQL database.

  You need to identify which database queries consume the most resources.

  Which tool should you use?

  A. Query Store

  B. Metrics

  C. Query Performance Insight

  D. Alerts

  Correct Answer: A

  The Query Store feature in Azure Database for MySQL provides a way to track query performance over time. Query Store simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries. Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. It separates data by time windows so that you can see database usage patterns. Data for all users, databases, and queries is stored in the mysql schema database in the Azure Database for MySQL instance.

  Reference: https://docs.microsoft.com/en-us/azure/mysql/concepts-query-store

• Question 159:

  You have SQL Server on an Azure virtual machine that contains a database named DB1.

  You have an application that queries DB1 to generate a sales report.

  You need to see the parameter values from the last time the query was executed.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Enable Last_Query_Plan_Stats in the master database

  B. Enable Lightweight_Query_Profiling in DB1

  C. Enable Last_Query_Plan_Stats in DB1

  D. Enable Lightweight_Query_Profiling in the master database

  E. Enable PARAMETER_SNIFFING in DB1

  Correct Answer: BC

  Last_Query_Plan_Stats allows you to enable or disable collection of the last query plan statistics (equivalent to an actual execution plan) in sys.dm_exec_query_plan_stats.

  Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

  Incorrect Answers:

  A: Enable it for DB1, not for the master database.

  E: Parameter sensitivity, also known as "parameter sniffing", refers to a process whereby SQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

  Parameter values are sniffed during compilation or recompilation for the following types of batches: Stored procedures Queries submitted via sp_executesql Prepared queries

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure

• Question 160:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an Azure Data Lake Storage account that contains a staging zone.

  You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.

  Solution: You schedule an Azure Databricks job that executes an R notebook, and then inserts the data into the data warehouse.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Must use an Azure Data Factory, not an Azure Databricks job.

  Reference: https://docs.microsoft.com/en-US/azure/data-factory/transform-data