Practice Free DP-300 Exam Online Questions

HOTSPOT

You have an Azure SQL database named db1.

You need to retrieve the resource usage of db1 from the last week.

How should you complete the statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: sys.resource_stats

sys.resource_stats returns CPU usage and storage data for an Azure SQL Database. It has database_name and start_time columns.

Box 2: DateAdd

The following example returns all databases that are averaging at least 80% of compute utilization over the last one week.

DECLARE @s datetime;

DECLARE @e datetime;

SET @s= DateAdd(d,-7,GetUTCDate());

SET @e= GETUTCDATE();

SELECT database_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization

FROM sys.resource_stats

WHERE start_time BETWEEN @s AND @e

GROUP BY database_name

HAVING AVG(avg_cpu_percent) >= 80

Incorrect Answers:

sys.dm_exec_requests:

sys.dm_exec_requests returns information about each request that is executing in SQL Server. It does not have a column named database_name.

sys.dm_db_resource_stats:

sys.dm_db_resource_stats does not have any start_time column.

Note: sys.dm_db_resource_stats returns CPU, I/O, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. Historical data is maintained for approximately one hour.

Sys.dm_user_db_resource_governance returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool. It does not have any start_time column.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database

You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in fiveminute intervals and report only events that arrive during the interval.

The output will be sent to a Delta Lake table.

Which output mode should you use?

Reveal Solution Hide Solution

SIMULATION

Task 8

You plan to perform performance testing of db1.

You need prevent db1 from reverting to the last known good query plan.

Reveal Solution Hide Solution

DRAG DROP

You have SQL Server on an Azure virtual machine that contains a database named DB1. DB1 is 30 TB and has a 1-GB daily rate of change.

You back up the database by using a Microsoft SQL Server Agent job that runs Transact-SQL commands. You perform a weekly full backup on Sunday, daily differential backups at 01:00, and transaction log backups every five minutes.

The database fails on Wednesday at 10:00.

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

Reveal Solution Hide Solution

Correct Answer:

HOTSPOT

You have an Azure subscription that contains a logical SQL server. The server hosts two databases named db1 and db2 and an Azure AD service principal named app1.

You need to ensure that app1 can access db1. The solution must use the principle of least privilege.

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial?view=azuresql

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?

Reveal Solution Hide Solution

HOTSPOT

You have an on-premises Microsoft SQL Server 2022 database named DB1.

You plan to create a database audit specification based on the following Transact-SQL script.

For each of the following statements, select Yes if the statement is true, otherwise select No. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

You have an Azure subscription that contains the following resources:

• 10 Azure SQL databases

• Five Azure SQL managed instances

• Five instances of SQL Server on Azure Virtual Machines

You need to implement a centralized monitoring solution for all the Azure SQL resources. The solution must minimize administrative effort.

What should you include in the solution?

Reveal Solution Hide Solution

You have an Azure SQL database named sqldb1.

You need to minimize the possibility of Query Store transitioning to a read-only state.

What should you do?

Reveal Solution Hide Solution

You need to migrate an on-premises Microsoft SQL Server database to Azure SQL Database. The solution must minimize downtime.

What should you do?

Reveal Solution Hide Solution