Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 81:

  You have four Azure subscriptions. Each subscription contains multiple Azure SQL databases.

  You need to update the column and index statistics for the databases.

  What should you use?

  A. an Azure Automation runbook

  B. a SQL Agent job

  C. Azure SQL Analytics

  D. automatic tuning in Azure SQL Database

  Correct Answer: A

  Reference: https://www.sqlshack.com/automate-azure-sql-database-indexes-and-statistics-maintenance/

• Question 82:

  You have an instance of SQL Server on Azure Virtual Machines that has a database named DB1.

  You plan to implement Azure SQL Data Sync for DB1.

  Which isolation level should you configure?

  A. SERIALIZABLE

  B. SNAPSHOT

  C. READ UNCOMMITTED

  D. READ COMMITTED

  Correct Answer: B

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database

• Question 83:

  You have an Azure SQL database named db1 on a server named server1.

  The Intelligent Insights diagnostics log identifies queries that cause performance issues due to tempDB contention.

  You need to resolve the performance issues.

  What should you do?

  A. Implement memory-optimized tables.

  B. Run the DBCC FLUSHPROCINDBcommand.

  C. Replace the sequential index keys with nonsequential keys.

  D. Run the DBCC DBREINDEXcommand.

  Correct Answer: A

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-troubleshoot-performance#tempdb-contention

• Question 84:

  You have an on-premises multi-tier application named App1 that includes a web tier, an application tier, and a Microsoft SQL Server tier. All the tiers run on Hyper-V virtual machines.

  Your new disaster recovery plan requires that all business-critical applications can be recovered to Azure.

  You need to recommend a solution to fail over the database tier of App1 to Azure. The solution must provide the ability to test failover to Azure without affecting the current environment.

  What should you include in the recommendation?

  A. Azure Backup

  B. Azure Information Protection

  C. Windows Server Failover Cluster

  D. Azure Site Recovery

  Correct Answer: D

  Reference: https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-test-failover-to-azure

• Question 85:

  You have an Azure SQL managed instance named SQL1 and two Azure web apps named App1 and App2.

  You need to limit the number of IOPs that App2 queries generate on SQL1.

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

  NOTE: Each correct selection is worth one point.

  A. Enable query optimizer fixes.

  B. Enable Resource Governor.

  C. Enable parameter sniffing.

  D. Create a workload group.

  E. Configure In-memory OLTP.

  F. Run the Database Engine Tuning Advisor.

  G. Reduce the Max Degree of Parallelism value.

  Correct Answer: BD

• Question 86:

  You have an Azure SQL managed instance.

  You need to gather the last execution of a query plan and its runtime statistics. The solution must minimize the impact on currently running queries.

  What should you do?

  A. Generate an estimated execution plan.

  B. Generate an actual execution plan.

  C. Run sys.dm_exec_query_plan_stats.

  D. Generate Live Query Statistics.

  Correct Answer: C

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-stats-transact-sql?view=sql-server-ver15

• Question 87:

  You have an Azure SQL Database managed instance.

  The instance starts experiencing performance issues.

  You need to identify which query is causing the issue and retrieve the execution plan for the query. The solution must minimize administrative effort.

  What should you use?

  A. the Azure portal

  B. Extended Events

  C. Query Store

  D. dynamic management views

  Correct Answer: C

• Question 88:

  You have an Azure SQL database named DB1.

  You need to display the estimated execution plan of a query by using the query editor in the Azure portal.

  What should you do first?

  A. Run the SET SHOWPLAN_ALL Transact-SQL statement.

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

  C. Run the SET FORCEPLANTransact-SQL statement.

  D. Enable Query Store for DB1.

  Correct Answer: A

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql?view=sql-server-ver15

• Question 89:

  You have an Azure SQL managed instance named SQLMI1 that hosts 10 databases.

  You need to implement alerts by using Azure Monitor. The solution must meet the following requirements:

  1.

  Minimize costs.

  2.

  Aggregate Intelligent Insights telemetry from each database. What should you do?

  A. From the Diagnostic settings of each database, select Send to Log Analytics.

  B. From the Diagnostic settings of each database, select Stream to an event hub.

  C. From the Diagnostic settings of SQLMI1, select Send to Log Analytics.

  D. From the Diagnostic settings of SQLMI1, select Stream to an event hub.

  Correct Answer: A

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal#configure-the-streaming-export-of-diagnostic-telemetry

• Question 90:

  You have an Azure SQL managed instance that hosts multiple databases.

  You need to configure alerts for each database based on the diagnostics telemetry of the database.

  What should you use?

  A. Azure SQL Analytics alerts based on metrics

  B. SQL Health Check alerts based on diagnostics logs

  C. SQL Health Check alerts based on metrics

  D. Azure SQL Analytics alerts based on diagnostics logs

  Correct Answer: D

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal#configure-the-streaming-export-of-diagnostic-telemetry