DP-300 Plan and implement data platform resources Practice Test 10 — 15 Questions

Question 1

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

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

Accepted Answer

sys.dm_tran_locks and sys.dm_os_waiting_tasks. To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_query_stats

Answer

sys.dm_tran_active_snapshot_database_transactions

Question 2

Refer to the exhibit. The exhibit shows a partial ARM template for deploying an Azure SQL Database server. You run the deployment but get an error stating the server cannot be created because of a missing firewall rule. The template has public network access disabled and no firewall rules defined. What is the most likely cause?

Accepted Answer

The Entra ID admin SID is invalid (all zeros).. The error occurs because the Entra ID admin SID is set to all zeros ('00000000-0000-0000-0000-000000000000'), which is an invalid GUID. Azure SQL Database requires a valid, non-zero SID (object ID) for the Entra ID admin to establish authentication. When public network access is disabled, the server can still be created without firewall rules, but the invalid SID prevents the deployment from succeeding.

Answer

The firewallRules array must include at least one rule when publicNetworkAccess is Disabled.

Answer

The tenantId is missing in the Entra ID admin configuration.

Answer

The Entra ID admin type should be 'Group' not 'User'.

Question 3

You are migrating an on-premises SQL Server database with 5 TB of data to Azure SQL Database. The database has multiple large tables and indexes. You need to minimize downtime during migration. Which service should you use?

Accepted Answer

Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct choice because it minimizes downtime by continuously synchronizing ongoing changes from the on-premises SQL Server to Azure SQL Database while the initial bulk data copy is in progress. This allows you to cut over to the target with only a brief pause, which is critical for a 5 TB database where offline methods would cause extended unavailability.

Answer

Export to BACPAC and import using SqlPackage

Answer

Backup and restore using URL with log shipping

Answer

Transactional replication from on-premises to Azure SQL Database

Question 4

You have an Azure SQL Database with sensitive customer data. You need to mask the credit card numbers so that only users with the 'Unmask' permission can see the full number. Non-privileged users should see only the last four digits. Which feature should you implement?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct feature because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits by default, and grant the UNMASK permission to privileged users so they see the full value. This directly meets the requirement of role-based partial masking without altering the stored data.

Answer

Column-level security

Answer

Row-level security

Answer

Always Encrypted

Question 5

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

Accepted Answer

Execute sys.sp_query_store_force_plan with the query_id and plan_id.. `sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Answer

Disable and re-enable Query Store to reset the plans.

Answer

Execute sys.sp_query_store_set_hints to add a query hint.

Answer

Use sys.dm_db_tuning_recommendations to apply a plan.

Question 6

Your company is deploying a new application that uses an Azure SQL Database. The security policy requires that all connections use Microsoft Entra ID authentication and that no SQL authentication users are created. Which server-level setting should you enforce?

Accepted Answer

Enable 'Azure AD-only authentication' on the Azure SQL logical server.. Enabling 'Azure AD-only authentication' on the Azure SQL logical server enforces that all connections must use Microsoft Entra ID authentication and blocks any SQL authentication attempts, even if SQL logins exist. This setting directly meets the security policy requirement that no SQL authentication users are created and all connections use Entra ID.

Answer

Set the database to use contained database authentication.

Answer

Configure a server-level firewall rule to allow only Entra ID IPs.

Answer

Set the Entra ID admin for the server and disable SQL authentication.

Question 7

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_performance_counters

Question 8

You have an Azure SQL Database with a table that stores historical data. To improve query performance, you create a nonclustered columnstore index on the table. However, after a large data load, you notice that query performance is worse than before. What is the most likely reason?

Accepted Answer

The data was loaded in small batches, causing rowgroups to be smaller than optimal.. When data is loaded in small batches into a columnstore index, each batch may form its own rowgroup. Rowgroups with fewer than 102,400 rows are considered 'delta rowgroups' and are not compressed into the columnstore format, leading to poor compression and suboptimal query performance. The large data load likely consisted of many small batches, resulting in numerous small rowgroups that degrade scan performance.

Answer

The columnstore index is fragmented and needs to be rebuilt.

Answer

The table has too many columns included in the index.

Answer

The index was created with compression disabled.

Question 9

Which TWO actions are required to implement transparent data encryption (TDE) with customer-managed keys for an Azure SQL Database?

Accepted Answer

Create an Azure Key Vault and a key.. TDE with customer-managed keys requires an Azure Key Vault to store the key, which provides centralized key management and control. The key in the vault is used to protect the database encryption key (DEK), ensuring that the customer retains ownership and control over the encryption material.

Answer

Set the backup encryption level to 'Encrypted'.

Answer

Create a server certificate in the database.

Answer

Configure column encryption keys in the database.

Question 10

Which THREE factors should you consider when choosing between Azure SQL Database single database and Azure SQL Managed Instance for a new application?

Accepted Answer

Need for cross-database queries and transactions.. Azure SQL Database single database does not support cross-database queries and transactions, whereas Azure SQL Managed Instance provides full SQL Server instance-level features, including cross-database queries and transactions via linked servers or within the same instance. This makes Managed Instance the appropriate choice when the application requires querying or updating multiple databases in a single transaction.

Answer

Requirement for elastic pool scalability.

Answer

Need for automated backups with point-in-time restore.

Question 11

Which TWO features can help you monitor and troubleshoot a query performance issue in Azure SQL Database without making changes to the application code?

Accepted Answer

Enabling Automatic Tuning to force plan corrections.. Automatic Tuning in Azure SQL Database can automatically identify and force plan corrections for regressed query execution plans without requiring any application code changes. It uses the Query Store to detect plan regressions and applies the last known good plan, improving query performance transparently. Option E is correct because Query Store captures query execution statistics and plan history, allowing you to review regressed queries and manually force a better plan without modifying the application.

Answer

Using sp_setapprole to set application roles.

Answer

Implementing Always Encrypted for sensitive columns.

Answer

Using Azure Data Migration Assistant for performance assessment.

Question 12

You are a database administrator for a large e-commerce company. The company has an Azure SQL Database named 'OrdersDB' in the General Purpose service tier, with a 2 TB database. The database has a table named 'OrderDetails' that contains over 1 billion rows. The table is partitioned by date using a partition function that splits data by month. You notice that queries that filter on a specific date range are performing poorly, and the query plan shows a clustered index scan on the partition column. The table has a clustered columnstore index to support analytics. You need to improve query performance for date-range queries without affecting the analytics workload. What should you do?

Accepted Answer

Create a nonclustered index on the partition key column (OrderDate) and include other columns as needed.. Creating a nonclustered index on the partition key (OrderDate) allows SQL Server to perform an index seek for date-range queries, avoiding the full clustered columnstore index scan. This index does not interfere with the existing columnstore index, which remains optimal for analytics workloads. The nonclustered index can include additional columns as included columns to cover the query without touching the base table.

Answer

Create a new partition scheme with a different partition function.

Answer

Rebuild the clustered columnstore index with a different compression setting.

Answer

Change the clustered index to a rowstore B-tree index on the partition key.

Question 13

Your company is migrating a mission-critical application from SQL Server 2019 on-premises to Azure SQL Managed Instance. The database is 1.5 TB and has 50 databases that need to be migrated. The application uses SQL Server Agent jobs, Service Broker, and cross-database queries. The security requirements mandate that the migration must use Microsoft Entra ID authentication, and all data must be encrypted at rest using customer-managed keys in Azure Key Vault. The migration window is limited to 4 hours of downtime. You need to choose the migration method and configure the target environment. What should you do?

Accepted Answer

Use Azure Database Migration Service online migration, configure Managed Instance with customer-managed TDE, and enable Entra ID authentication.. Azure Database Migration Service (DMS) online migration supports minimal downtime for large databases (1.5 TB) and 50 databases, while maintaining transactional consistency for cross-database queries and Service Broker. Configuring the Managed Instance with customer-managed TDE (Transparent Data Encryption) using Azure Key Vault meets the encryption-at-rest requirement, and enabling Microsoft Entra ID authentication satisfies the security mandate. The online migration mode allows continuous synchronization during the migration window, with a final cutover within the 4-hour downtime limit.

Answer

Use Azure Database Migration Service offline migration with automatic cutover.

Answer

Deploy a new Managed Instance, configure TDE with customer-managed keys, and use transactional replication to synchronize data.

Answer

Use Azure backup to URL and restore to Managed Instance, then configure TDE with customer-managed keys.

Question 14

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

Accepted Answer

Always Encrypted with deterministic encryption. Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.

Answer

Always Encrypted with randomized encryption

Answer

Transparent Data Encryption

Answer

Dynamic Data Masking

Question 15

You are designing a new Azure SQL Database for an e-commerce application. The database will contain sensitive customer data and must be isolated from other tenants. Which deployment option provides the highest level of resource isolation?

Accepted Answer

Managed Instance. Azure SQL Managed Instance provides the highest level of resource isolation among the listed options. It runs as an isolated instance with its own dedicated set of CPU, memory, and storage resources, and it is deployed into a dedicated virtual network, ensuring network isolation as well. This makes it ideal for scenarios requiring strict tenant separation, such as an e-commerce application handling sensitive customer data. In contrast, a single database in Azure SQL Database shares some underlying resources at the logical server level and does not offer the same degree of network isolation.

Answer

Elastic pool

Answer

Hyperscale

Answer

Single database