Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 216–220 (page: 45)

Updated On: 10-Feb-2026

You have an Azure subscription that contains two Azure SQL managed instances named SQLMI1 and SQLMI2. SQLMI1 hosts a database named DB1.

You initiate a copy operation of DB1 to SQLMI2.

You need to monitor the performance of the copy operation.

Which dynamic management view should you use?

  1. sys.dm_continuous_copy_status
  2. sys.dm_database_replica_states
  3. sys.dm_hadr_physical_seeding_stats
  4. sys.dm_db_mirroring_connections

Answer(s): C

Explanation:

sys.dm_hadr_physical_seeding_stats (Transact-SQL)
On the primary replica, query sys.dm_hadr_physical_seeding_stats DMV to see the physical statistics for each seeding process that is currently running.
Note: Seeding is the process of populating a database with initial data, typically used to provide sample or default records for testing and development purposes. You can use this to create "reproducible environments" for local development, staging, and production.
Incorrect:
* sys.dm_continuous_copy_status
To get information about all the replica databases of a given source database.
* sys.dm_database_replica_states (Azure SQL Database)
Returns state information for each database that participates in primary and secondary replicas. On a secondary replica, returns one row for every secondary database on the instance. On the primary replica, returns one row for the primary database, and an additional row for each secondary database.
* Database Mirroring - sys.dm_db_mirroring_connections
Returns a row for each connection established for database mirroring.


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm- database-replica-states-azure-sql-database https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr- physical-seeding-stats



You have an Azure subscription that contains a SQL Server on Linux Azure Virtual Machines instance named VM1. VM1 contains a database named DB1.

You need to create operational performance baselines for DB1 and VM1.

What should you use?

  1. InfluxDB, Collectd, and Grafana
  2. Azure Monitor Log Analytics
  3. Azure Event Hubs
  4. top, grep, and sed

Answer(s): B

Explanation:

Azure Virtual Machines, Log Analytics virtual machine extension for Linux Azure Monitor Logs provides monitoring, alerting, and alert remediation capabilities across cloud and on- premises assets. The Log Analytics virtual machine extension for Linux is published and supported by Microsoft. The extension installs the Log Analytics agent on Azure virtual machines, and enrolls virtual machines into an existing Log Analytics workspace.
--
Monitor Azure SQL Database
When you have critical applications and business processes that rely on Azure resources, you need to monitor and get alerts for your system. The Azure Monitor service collects and aggregates metrics and logs from every component of your system. Azure Monitor provides you with a view of availability, performance, and resilience, and notifies you of issues.
You can optionally route metric and activity log data to the Azure Monitor logs store. You can then use Log Analytics to query the data and correlate it with other log data.
Incorrect:
Not A, not D: System monitoring with Grafana, InfluxDB et Collectd System monitoring is important to understand the global performance of a machine. For instance, unix systems provide command lines tools like top, free or df.
Which gives simple and accurate functionalities but it lacks of a global vision, like the one that that we can have in a dashboard.
Let's start with the presentation of the components of the stack.



Collectd is a daemon process that runs on the system and collects information like CPU, memory, disk usage, network data, etc. It can send these data to some data store.
The data store that we have chosen is Influxdb. The main reason is because influxdb is a time series database designed to store and analyse time-series data, likes the one we can get from our system.
The final part is Grafana, which is a data query and presenting tool, that let you build beautiful graph chart based on your defined data source. And of course InfluxDB is on the officially supported data source.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-sql-database-azure-monitor https://learn.microsoft.com/en-us/azure/virtual-machines/extensions/oms-linux https://medium.com/@cristianpb/system-monitoring-with-grafana-influxdb-et-collectd-7827969e1fe



HOTSPOT (Drag and Drop is not supported)

You have an Azure subscription that contains a SQL Server instance on a virtual machine named Server1.

You execute the following T-SQL statement on Server1.



For each of the following statements, select Yes if the statement is true, otherwise select No.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: Yes
Yes - An alert will be generated when a fatal error occurs.

The severity level is 20, which indicates a fatal error.

Note: sp_add_alert (Transact-SQL)
Adds an alert.

Parameters include:

[ @severity = ] severity
The severity level (from 1 through 25) that defines the alert. @severity is int, with a default of 0. Any SQL Server message stored in the sysmessages table sent to the Windows application log with the indicated severity causes the alert to be sent. If @message_id is used to define the alert, @severity must be 0.

--
Levels of severity
The following table lists and describes the severity levels of the errors raised by the SQL Server Database Engine.

* 20-24
Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.

* Etc.

Box 2: No
No - A SQL Server Agent job will run when an alert is generated.

Need to use sp_start_job.
sp_add_notification just sets up a notification for an alert.

Note: sp_start_job (Transact-SQL)
Instructs SQL Server Agent to execute a job immediately.

Box 3: Yes
Yes - An email will be sent to DBA-Team@contoso.com when an alert is generated.

DBA-Team@contoso.com is the email notification recipient.

Note: sp_add_operator (Transact-SQL)
Creates an operator (notification recipient) for use with alerts and jobs.


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-operator-transact- sql https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-start-job-transact-sql https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-notification- transact-sql



HOTSPOT (Drag and Drop is not supported)

You have a logical server for Azure SQL Database named Server1 that has an elastic pool named Pool1.

You have a database watcher named Watcher1.

You run the following KQL query.

let logicelServer = @"Server1";
let elacticPoolName = @"Pool1";
let replicaType = "Primary";
let duration = 20h;
sqldb_elastic_pool_resource_utilization
| where sample_time_utc > ago(duration)
| where logical_server_nane =~ logicalServer
| where replica_type =~ replicaType
| project sample_time_utc,
avg_cpu_percent,
avg_instance_cpu_percent,
avg_data_io_percent,
avg_log_write_percent,
max_worker_percent
| sort by sample_time_utc desc
| render timechart;

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: No
No - The query returns the execution time of individual queries.

Box 2: Yes
Yes - The query returns the resource consumption metrics from the last day.

To monitor resource utilization in an Azure SQL Elastic Pool, you can utilize the Azure SQL Database watcher service. Database watcher collects monitoring data at periodic intervals using T-SQL queries, and the data is streamed to Azure Data Explorer and Real-Time Analytics in Microsoft Fabric for near real-time analysis.

Box 3: Yes
Yes - The query visualizes the result set and include a legend for each series.

Kusto, render operator
The render operator must be the last operator in the query, and can only be used with queries that produce a single tabular data stream result. The render operator doesn't modify data. It injects an annotation ("Visualization") into the result's extended properties. The annotation contains the information provided by the operator in the query. The interpretation of the visualization information is done by the user agent. Different agents, such as Kusto.Explorer or Azure Data Explorer web UI, may support different visualizations.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-data https://learn.microsoft.com/en-us/kusto/query/render-operator



HOTSPOT (Drag and Drop is not supported)

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

You have the Query Performance Insight analysis shown in the following exhibit.



For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: No
No - The peak DTU consumption is 4 percent.

In the exhibit we see: TOP 5 queries by CPU, Metrics aggregation: AVG The graphic display average query CPU usage, not the peak DTU consumption.

Box 2: Yes
Yes - The peek consumption of the query that has ID 3964 is 4 percent.

This is the red line.

Box 3: No
No - The query that has ID 21195 has a duration of 00:00:01.180 every time it runs.

It is the average runtime.

Note: The bottom grid shows aggregated information for the visible queries:

Query ID, which is a unique identifier for the query in the database. CPU per query during an observable interval, which depends on the aggregation function. *-> Duration per query, which also depends on the aggregation function.
Total number of executions for a specific query.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use



Viewing page 45 of 85
Viewing questions 221 - 225 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!