FEFreeExamDumps.in

DP-300 Practice Questions — Page 14

Question 131

Open question ↗

You have SQL Server on an Azure virtual machine.

You need to add a 4-TB volume that meets the following requirements:

✑ Maximizes IOPs

✑ Uses premium solid state drives (SSDs)

What should you do?

  • A.Attach two mirrored 4-TB SSDs.
  • B.Attach a stripe set that contains four 1-TB SSDs. ✓
  • C.Attach a RAID-5 array that contains five 1-TB SSDs.
  • D.Attach a single 4-TB SSD.

Question 132

Open question ↗

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

The Intelligent Insights diagnostics log identifies that several tables are missing indexes.

You need to ensure that indexes are created for the tables.

What should you do?

  • A.Run the DBCC SQLPERF command.
  • B.Run the DBCC DBREINDEX command.
  • C.Modify the automatic tuning settings for db1. ✓
  • D.Modify the Query Store settings for db1.

Question 133

Open question ↗

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.

Question 134

Open question ↗

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 FLUSHPROCINDB command.
  • C.Replace the sequential index keys with nonsequential keys.
  • D.Run the DBCC DBREINDEX command.

Question 135

Open question ↗

You have an Azure subscription that contains an Azure SQL database.

The database fails to respond to queries in a timely manner.

You need to identify whether the issue relates to resource_semaphore waits.

How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Hot Area:

Question 135

Question 136

Open question ↗

You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.

You scale up the virtual machine to 8 vCPUs and 64 GB of memory.

You need to reduce tempdb contention without negatively affecting server performance.

What is the number of secondary data files that you should configure for tempdb?

  • A.2
  • B.4
  • C.8
  • D.64

Question 137

Open question ↗

You receive numerous alerts from Azure Monitor for an Azure SQL Database instance.

You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns for an extended period.

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

NOTE: Each correct selection is worth one point.

  • A.Set Threshold Sensitivity to High
  • B.Set the Alert logic threshold to Dynamic ✓
  • C.Set the Alert logic threshold to Static
  • D.Set Threshold Sensitivity to Low ✓
  • E.Set Force Plan to On

Question 138

Open question ↗

You have an Azure SQL database named sqldb1.

You need to minimize the amount of space by the data and log files of sqldb1.

What should you run?

  • A.DBCC SHRINKDATABASE ✓
  • B.sp_clean_db_free_space
  • C.sp_clean_db_file_free_space
  • D.DBCC SHRINKFILE

Question 139

Open question ↗

You have an Azure SQL Database server named sqlsrv1 that hosts 10 Azure SQL databases.

The databases perform slower than expected.

You need to identify whether the performance issue relates to the use of tempdb by Azure SQL databases in sqlsrv1.

What should you do?

  • A.Run Query Store-based queries
  • B.Review information provided by SQL Server Profiler-based traces
  • C.Review information provided by Query Performance Insight
  • D.Run dynamic management view-based queries

Question 140

Open question ↗

You are building an Azure virtual machine.

You allocate two 1-TiB, P30 premium storage disks to the virtual machine. Each disk provides 5,000 IOPS.

You plan to migrate an on-premises instance of Microsoft SQL Server to the virtual machine. The instance has a database that contains a 1.2-TiB data file. The database requires 10,000 IOPS.

You need to configure storage for the virtual machine to support the database.

Which three objects should you create in sequence? To answer, move the appropriate objects from the list of objects to the answer area and arrange them in the correct order.

Select and Place:

Question 140