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

Question 1

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.. Option D is correct because `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 2

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.. Option C is correct because 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 3

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 4

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.. Option B is correct because 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 5

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.. Option A is correct because 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 6

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.. Option B is correct because 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 7

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.. Option B is correct because 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 8

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.. Option B is correct because 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 9

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.. Option C is correct because 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 10

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 11

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

Single database. A single database in Azure SQL Database runs on a dedicated logical server and, when configured with the appropriate service tier and compute size, provides the highest level of resource isolation because it does not share CPU, memory, or IO resources with any other database. This ensures that the sensitive customer data is isolated from other tenants, meeting strict compliance and performance requirements.

Answer

Elastic pool

Answer

Hyperscale

Answer

Managed Instance

Question 12

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. Which step is required to enable TDE in the target Managed Instance?

Accepted Answer

Export the certificate to a PFX file, import it to Azure Key Vault, and configure TDE with a key vault key. Azure SQL Managed Instance does not support importing user-provided certificates for TDE. Instead, it relies on Azure Key Vault as the external key store. Therefore, you must export the on-premises TDE certificate to a PFX file, import it into Azure Key Vault, and then configure the Managed Instance to use that key vault key for TDE. This ensures the database encryption key can be unwrapped and the database remains encrypted during and after migration.

Answer

Create a self-signed certificate in the Managed Instance and restore the database with KEEP_TDE

Answer

Use the same certificate file by uploading it to the Managed Instance via SQL Server Management Studio

Answer

Disable TDE on the source database before migration, then enable it after migration

Question 13

Your company has an Azure SQL Database configured with Active Geo-Replication between two regions. The primary database is experiencing performance degradation due to a sudden increase in write-intensive workloads. You need to minimize downtime and ensure no data loss. What should you do?

Accepted Answer

Initiate a planned failover to the secondary region. A planned failover (Option D) is correct because Active Geo-Replication supports a graceful, synchronous failover that promotes the secondary database to become the new primary without data loss. This is achieved by first replicating all pending transactions to the secondary, ensuring zero data loss, and then switching roles with minimal downtime. In contrast, a forced failover (Option B) would cause data loss because it does not wait for pending transactions to be replicated.

Answer

Enable read scale-out on the primary database

Answer

Initiate a forced failover to the secondary region

Answer

Increase the service tier of the primary database

Question 14

You are deploying an Azure SQL Database for a new application that requires consistent performance and low latency. The database will be accessed by users across multiple Azure regions. Which deployment option should you use?

Accepted Answer

Single database with Active Geo-Replication. Active Geo-Replication for a single database enables continuous data synchronization to readable secondary replicas in different Azure regions, providing low-latency read access for globally distributed users and supporting manual failover for write availability. This meets the requirement for consistent performance and low latency across multiple regions without the orchestration overhead of failover groups.

Answer

Managed Instance with failover groups

Answer

Single database in one region

Answer

Elastic pool in one region

Question 15

You need to create an Azure SQL Database that automatically scales storage up to 4 TB. Which service tier should you select?

Accepted Answer

Business Critical. Business Critical is the correct service tier because it supports up to 4 TB of storage with provisioned compute, high-performance local SSD storage, and built-in high availability. Standard tier maxes out at 1 TB, Basic at 2 GB, and Hyperscale is designed for up to 100 TB but uses a different architecture with page servers and log services, not the fixed 4 TB limit.

Answer

Standard

Answer

Hyperscale

Answer

Basic