Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 201–205 (page: 42)

Updated On: 10-Feb-2026

HOTSPOT (Drag and Drop is not supported)

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

You need to monitor the performance of SQLMI1 and identify which database uses the most memory and the most disk I/O.

Which objects should you query? To answer, select the appropriate options in the answer area.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: sys.dm_os_performance_counters sys.dm_os_performance_counters returns a row per performance counter maintained by the server.

These include:
* SQLServer:Memory Manager
Provides information about SQL Server memory usage, such as the total number of lock structures currently allocated.

Box 2: sys.dm_io_virtual_file_stats sys.dm_io_virtual_file_stats returns I/O statistics for data and log files.

Examples

A . Return statistics for a log file
Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database The following example returns statistics for the log file in the AdventureWorks2019 database.
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012'), 2); GO
B . Return statistics for file in tempdb
Applies to: Azure Synapse Analytics
SELECT * FROM sys.dm_pdw_nodes_io_virtual_file_stats
WHERE database_name = 'tempdb' AND file_id = 2;
Incorrect:
* sys.dm_os_buffer_descriptors returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type. In SQL Server, this dynamic management view also returns information about the data pages in the buffer pool extension file.
* sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans in SQL Server.
The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself.


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/use-sql-server-objects https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io- virtual-file-stats-transact-sql



HOTSPOT (Drag and Drop is not supported)

You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines. The virtual machine hosts a database named DB1.

You need to monitor DB1 by using Extended Events. The solution must meet the following requirements:

Capture raw event data and store the data in Azure Storage.

Minimize the performance impact of capturing extended events.

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

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: event_file
SQL Server Extended Events tips
Example:
We can obtain information about the active event sessions, with the help of the sys.dm_xe_sessions dynamic management view. At the same time, this view returns information about the dropped event and buffer counts. To understand this concept more clearly, we will create two different extended event sessions. The first one is retention mode is Multiple event loss and the second one is Single event loss. These events capture the locks and SQL statement executions.

CREATE EVENT SESSION [CaptureQuery_MultipleEventLoss] ON SERVER ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released,
ADD EVENT sqlserver.lock_request_priority_state,
ADD EVENT sqlserver.query_pre_execution_showplan,
ADD EVENT sqlserver.sql_statement_completed,
ADD EVENT sqlserver.sql_statement_starting
ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents \CaptureQuery_MultipleEventLoss.xel')

SQL Server reserves buffer memory for the event sessions to store the event data temporarily and then dispatch them to the target storage. In some cases, the extended event sessions generate data incredibly fast. However, the collected event data can not be dispatched to the event storage as fast as the event data generation speed. To overcome these types of cases, the EVENT_RETENTION_MODE setting allows tolerance to be set for data loss when the extended event is under pressure. So that, the event session impact on the database engine performance is minimized.

ALLOW_SINGLE_EVENT_LOSS: This option is the default option and allows single events to be dropped and lost from the session when the event Session memory buffer is full and can not be sent to the storage target.

ALLOW_MULTIPLE_EVENT_LOSS: This option affects the performance minimally and allows the entire memory buffer containing multiple events to be dropped and lost when the memory buffers are full.

NO_EVENT_LOSS: When we use this option, we can ensure that no event data can be lost but it causes to degrade database engine performance.

Example continued:
ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents \CaptureQuery_MultipleEventLoss.xel')
WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0
KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
Box 2: ALLOW_MULTIPLE_EVENT_LOSS
SQL Server Extended Events EVENT_RETENTION_MODE option
We want to minimize the performance impact of capturing extended events.


Reference:

https://www.sqlshack.com/sql-server-extended-events-tips/



You have an Azure SQL database named DB1.

You need to query the fragmentation information of data and indexes for the tables in DB1.

Which command should you use?

  1. sys.dm_db_index_usage_stats
  2. DBCC CHECKALLOC
  3. DBCC SHOWCONTIG
  4. sys.dm_db_index_physical_stats

Answer(s): D

Explanation:

sys.dm_db_index_physical_stats (Transact-SQL)
Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db- index-physical-stats-transact-sql



You have an Azure SQL managed instance named SQLMI1 that has the following settings:

vCores: 4

Service tier: General Purpose

Hardware generation: Standard-series (Gen5)

You discover that memory pressure on SQLMI1 is high.

You need to reduce the memory pressure on SQLMI1. The solution must minimize costs.

What should to do?

  1. Enable the Query Store.
  2. Change vCores to 8.
  3. Change Hardware generation to Premium-series.
  4. Change Service tier to Business Critical.

Answer(s): B

Explanation:

The amount of memory available to Managed Instance is proportional to the number of cores assigned. As an example, in the Gen5 architecture you have 5.1GB of memory per vCore, meaning that 8-core instance will have 41GB memory. Periodically you should check if this amount of memory is the right match for your workload.


Reference:

https://techcommunity.microsoft.com/t5/azure-sql-blog/do-you-need-more-memory-on-azure-sql-managed- instance/ba-p/563444



You have five instances of SQL Server on Azure Virtual Machines.

You need to monitor Microsoft SQL Server performance for all the instances by consolidating metrics into a single graphic display. The solution must minimize administrative effort.

What should you use?

  1. Azure Monitor
  2. Log Analytics
  3. SQL Insights
  4. Azure SQL Analytics

Answer(s): A



Viewing page 42 of 85
Viewing questions 206 - 210 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!