DP-300 Practice Test 5 — 10 Questions

Question 1

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

Accepted Answer

sys.dm_exec_query_stats. Sys.dm_exec_query_stats. This DMV returns aggregated performance statistics for cached query plans, including total_worker_time (which represents total CPU time). By filtering on last_execution_time, you can identify queries executed within the last hour. sys.dm_exec_requests (B) shows only currently executing requests, not historical data. sys.dm_db_index_usage_stats (C) tracks index seek/scan operations, not CPU usage. sys.dm_db_resource_stats (D) provides resource consumption at the database level (e.g., DTU, CPU percentage), not per query.

Answer

sys.dm_exec_requests

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_db_resource_stats

Question 2

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Enable automatic tuning for 'CREATE INDEX' in the Azure portal.. Azure SQL Database's automatic tuning can automatically implement index recommendations when 'CREATE INDEX' is enabled. Option A is incorrect because 'FORCE LAST GOOD PLAN' is for plan correction, not index creation. Option B is incorrect because Query Store is a monitoring tool, not an automatic tuning feature. Option C is incorrect because Azure Advisor only provides recommendations, not automatic implementation.

Answer

Enable automatic tuning for 'FORCE LAST GOOD PLAN' in the Azure portal.

Answer

Set up Query Store to capture the recommended index execution.

Answer

Configure Azure Advisor to email you the recommendation.

Question 3

Refer to the exhibit. You run the above PowerShell command to set the Transparent Data Encryption (TDE) protector for an Azure SQL Database server. What is the result?

Accepted Answer

The server’s TDE protector is changed to a customer-managed key from Azure Key Vault.. The command `Set-AzSqlServerTransparentDataEncryptionProtector` configures the TDE protector at the server level. By specifying the `-Type AzureKeyVault` and `-KeyId` parameters, it sets the server's TDE protector to a customer-managed key stored in Azure Key Vault. Therefore, Option D is correct. Option A is incorrect because the command does not fail due to permissions; it succeeds if permission is granted. Option B is incorrect because TDE remains enabled; only the protector key is changed. Option C is incorrect because the command updates the server's TDE protector, not a specific database.

Answer

The command fails because the service principal does not have permissions to the key vault.

Answer

Transparent Data Encryption is disabled.

Answer

The TDE protector for the database "mydb" is updated.

Question 4

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

Accepted Answer

Query sys.dm_exec_query_stats. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, including CPU time, logical reads, and duration, making it ideal for identifying queries that consume the most resources in Azure SQL Database. Option A (sys.dm_exec_requests) shows only currently executing requests, not historical data. Option B (sys.dm_os_wait_stats) provides server-level wait statistics, not per-query resource usage. Option D (sys.dm_db_resource_stats) shows database-level DTU consumption per minute, not per-query details.

Answer

Query sys.dm_exec_requests

Answer

Query sys.dm_os_wait_stats

Answer

Query sys.dm_db_resource_stats

Question 5

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

Accepted Answer

The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.. Query Store runtime statistics are sent to Azure Diagnostics only if diagnostic settings are configured to collect that category. Option A is wrong because Query Store is enabled. Option C is wrong because the query is correct. Option D is wrong because the KQL query syntax does not require 'project' before 'summarize'; placement does not affect results.

Answer

Query Store is not enabled on the databases.

Answer

The time range in the query is too narrow and excludes the last 24 hours.

Answer

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Question 6

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

Accepted Answer

Azure Monitor. Azure Monitor alerts can be configured to trigger based on metrics like storage percent, enabling automatic notification when an Azure SQL Database reaches 80% storage usage. Option A is incorrect because Azure Automation is used for runbooks and process automation, not for alerting. Option B is incorrect because Microsoft Sentinel is a SIEM solution for security analytics, not for simple metric alerts. Option C is incorrect because Azure Logic Apps can be triggered by alerts, but the alert itself must be created in Azure Monitor.

Answer

Azure Automation

Answer

Microsoft Sentinel

Answer

Azure Logic Apps

Question 7

Which THREE components are part of an Azure SQL Managed Instance automated maintenance window configuration? (Choose three.)

Accepted Answer

Start time (e.g., 2:00 AM local time). Azure SQL Managed Instance allows you to configure a maintenance window including the day of week (e.g., Sunday), start time (e.g., 2:00 AM local time), and a notification email for upcoming maintenance. Option C is incorrect because maintenance windows are instance-level, not per database. Option D is incorrect because it applies to Azure SQL Database, not Managed Instance.

Answer

Database-level maintenance schedule

Answer

Maximum number of concurrent databases

Question 8

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A (Azure SQL Analytics) is for analyzing logs, not triggering actions. Option B (Azure Log Analytics) is for log analytics, not for direct metric alerts triggering runbooks. Option C (Kusto Query Language) is a query language, not a service.

Answer

Azure SQL Analytics

Answer

Azure Log Analytics

Answer

Kusto Query Language (KQL)

Question 9

You are automating the scaling of an Azure SQL Database based on workload patterns. The database uses the DTU purchasing model. You need to ensure that scaling actions do not cause connection drops. Which scaling method should you use?

Accepted Answer

Use the Azure portal or PowerShell to change the service tier within the same DTU tier (e.g., S2 to S3) to avoid downtime.. Azure SQL Database supports online scaling without connection drops when scaling within the same DTU tier (e.g., from S2 to S3). This is because the database remains online and the operation is a metadata change. Option D is correct. Option A is incorrect: creating a new database and using point-in-time restore does not scale the existing database and causes downtime during restore. Option B is incorrect: ALTER DATABASE with ONLINE option is for file operations, not for scaling service tiers. Option C is incorrect: Elastic Database Jobs are for scheduling scripts across databases, not for scaling operations.

Answer

Create a new database at the target size and use point-in-time restore to copy data.

Answer

Scale using the ALTER DATABASE T-SQL command with the ONLINE option.

Answer

Use Elastic Database Jobs to schedule scaling during maintenance windows.

Question 10

Your company has an Azure SQL Database that stores financial data. You need to implement auditing to track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL). Which audit action group should you configure?

Accepted Answer

SCHEMA_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_CHANGE_GROUP. To track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL) in Azure SQL Database, you need to audit both data changes and schema changes. The SCHEMA_OBJECT_CHANGE_GROUP captures DDL changes like CREATE, ALTER, DROP on schema objects, while DATABASE_OBJECT_CHANGE_GROUP captures DML changes on database objects (tables, views). Together, they cover the required tracking. Option A is correct because these two action groups specifically target the stated requirements.

Answer

DATABASE_OBJECT_PERMISSION_CHANGE_GROUP and SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

Answer

DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP and SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

Answer

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP