FEFreeExamDumps.in

DP-300 Practice Questions — Page 18

Question 171

Open question ↗

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

Question 172

Open question ↗

You have an Azure SQL database. The database contains a table that uses a columnstore index and is accessed infrequently.

You enable columnstore archival compression.

What are two possible results of the configuration? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

  • A.Queries that use the index will consume more disk I/O.
  • B.Queries that use the index will retrieve fewer data pages.
  • C.The index will consume more disk space.
  • D.The index will consume more memory.
  • E.Queries that use the index will consume more CPU resources.

Question 173

Open question ↗

You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool.

You need to create a surrogate key for the table. The solution must provide the fastest query performance.

What should you use for the surrogate key?

  • A.an IDENTITY column ✓
  • B.a GUID column
  • C.a sequence object

Question 174

Open question ↗

You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytics in a star schema.

You plan to have a fact table for website visits. The table will be approximately 5 GB.

You need to recommend which distribution type and index type to use for the table. The solution must provide the fastest query performance.

What should you recommend? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Hot Area:

Question 174

Question 175

Open question ↗

You have an Azure SQL database.

You are reviewing a slow performing query as shown in the following exhibit.

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

Hot Area:

Question 175

Question 176

Open question ↗

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.

Question 177

Open question ↗

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.SQL Profiler
  • B.Extended Events
  • C.Query Store
  • D.dynamic management views

Question 178

Open question ↗

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 FORCEPLAN Transact-SQL statement.
  • D.Enable Query Store for DB1.

Question 179

Open question ↗

You have an Azure SQL database.

You have a query and the associated execution plan as shown in the following exhibit.

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

Hot Area:

Question 179

Question 180

Open question ↗

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