Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 176–180 (page: 37)

Updated On: 10-Feb-2026

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 SQL Server 2019 on an Azure virtual machine.

You are troubleshooting performance issues for a query in a SQL Server instance.

To gather more information, you query sys.dm_exec_requests and discover that the wait type is

PAGELATCH_UP and the wait_resource is 2:3:905856.

You need to improve system performance.

Solution: You reduce the use of table variables and temporary tables.

Does this meet the goal?

  1. Yes
  2. No

Answer(s): A

Explanation:

The following operations use tempdb extensively:
* Repetitive create-and-drop operation of temporary tables (local or global).
* Table variables that use tempdb for storage.
* Etc.


Reference:

https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation- contention



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

You need to modify the MAXDOP settings for db1.

What should you do?

  1. Connect to db1 and run the sp_configure command.
  2. Connect to the master database of server1 and run the sp_configure command.
  3. Configure the extended properties of db1.
  4. Modify the database scoped configuration of db1.

Answer(s): D

Explanation:

If you determine that a MAXDOP setting different from the default is optimal for your Azure SQL Database workload, you can use the ALTER DATABASE SCOPED CONFIGURATION T-SQL statement.


Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/configure-max-degree-of-parallelism



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 SQL Server 2019 on an Azure virtual machine.

You are troubleshooting performance issues for a query in a SQL Server instance.

To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856.

You need to improve system performance.

Solution: You create additional tempdb files.

Does this meet the goal?

  1. Yes
  2. No

Answer(s): A

Explanation:

To improve the concurrency of tempdb, try the following methods:
* Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures.
* Etc.
Note: Symptoms
On a server that is running Microsoft SQL Server, you notice severe blocking when the server is experiencing a heavy load. Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks] indicates that these requests or tasks are waiting for tempdb resources. Additionally, the wait type is PAGELATCH_UP, and the wait resource points to pages in Tempdb.


Reference:

https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation- contention



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?

  1. Attach two mirrored 4-TB SSDs.
  2. Attach a stripe set that contains four 1-TB SSDs.
  3. Attach a RAID-5 array that contains five 1-TB SSDs.
  4. Attach a single 4-TB SS

Answer(s): B

Explanation:

For more throughput, you can add additional data disks and use disk striping.


Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/storage-configuration? tabs=windows2016



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?

  1. Run the DBCC SQLPERF command.
  2. Run the DBCC DBREINDEX command.
  3. Modify the automatic tuning settings for db1.
  4. Modify the Query Store settings for db1.

Answer(s): C

Explanation:

Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database, and it automatically improves their performance.
Automatic tuning for Azure SQL Database uses the CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN database advisor recommendations to optimize your database performance.


Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview



Viewing page 37 of 85
Viewing questions 181 - 185 out of 470 questions


Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:

AI Tutor AI Tutor 👋 I’m here to help!