DP-300 Practice Test 43 — 15 Questions

Question 1

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

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

Accepted Answer

The version of the cardinality estimator differs between environments.. The cardinality estimator (CE) version can differ between on-premises SQL Server and Azure SQL Database, even with identical indexes and statistics. The CE version is determined by the database compatibility level, and Azure SQL Database may have a different default compatibility level than on-prem, leading to different query plans and performance. Option B is incorrect because DTU vs vCore is a purchasing model that does not inherently slow queries; it affects resource limits but not plan choices. Option C is incorrect because automatic tuning in Azure SQL Database typically helps performance, and index fragmentation levels would be similar with identical indexes. Option D is incorrect because intelligent query processing features are available on both platforms and generally beneficial, not detrimental.

Answer

Azure SQL Database uses a different resource governance model (DTU vs vCore).

Answer

The index fragmentation level is higher on Azure SQL Database due to automatic tuning.

Answer

Azure SQL Database has intelligent query processing features that are not available on-premises.

Question 2

You are planning to deploy Azure SQL Database for a new application. The application requires a predictable performance with reserved resources and the ability to scale up during peak season. You want to minimize costs. Which purchasing model should you choose?

Accepted Answer

vCore-based purchasing model with reserved capacity. The vCore-based purchasing model with reserved capacity is correct because it allows you to reserve compute resources for a one- or three-year term, providing a significant discount (up to 40-60%) compared to pay-as-you-go pricing. This model also supports predictable performance with dedicated resources and the ability to scale up (e.g., increase vCores or storage) during peak seasons, while the reserved capacity commitment minimizes costs for the baseline workload.

Answer

DTU-based purchasing model

Answer

Hyperscale service tier

Answer

Serverless compute tier

Question 3

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

Accepted Answer

The service tier S3 is not sufficient for the workload.. The exhibit indicates the database is using the S3 service tier, which provides limited DTUs (100 DTU). During peak hours, this low tier is insufficient for the workload, leading to resource contention and performance degradation. Option A is incorrect because zone redundancy is related to high availability and failover, not peak-hour performance. Option C is incorrect because read scale is used for offloading read-only workloads, not for mitigating primary performance issues. Option D is incorrect because auto-pause delay (3600 seconds) affects serverless databases in idle periods, not active peak-hour usage.

Answer

Zone redundancy is disabled, causing failover delays.

Answer

Read scale is disabled, increasing load on primary.

Answer

The database is auto-pausing frequently due to autoPauseDelay.

Question 4

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

Accepted Answer

The database is experiencing memory pressure due to concurrent queries exceeding available memory.. RESOURCE_SEMAPHORE waits indicate that queries are waiting for memory grants to execute. In Azure SQL Database General Purpose tier with 100 DTUs, memory is shared between the buffer pool and query execution. During peak hours, concurrent queries can exhaust the available memory, forcing queries to wait for memory grants. This is a classic sign of memory pressure, not CPU or IO throttling.

Answer

There is a blocking chain due to unoptimized queries.

Answer

The disk IOPS limit is being reached, causing queuing.

Answer

The DTU limit is being reached, causing CPU throttling.

Question 5

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

Accepted Answer

avg_data_io_percent. Options C and E are correct. avg_data_io_percent measures data IO usage relative to the limit, and avg_log_write_percent measures log IO usage—both indicate disk IO bottlenecks. Option A (avg_cpu_percent) is CPU usage. Option B (max_size_percent) is storage space. Option D (avg_memory_usage_percent) is memory.

Answer

avg_cpu_percent

Answer

max_size_percent

Answer

avg_memory_usage_percent

Question 6

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which change should you make?

Accepted Answer

Enable serverless with auto-pause enabled.. Enabling serverless with auto-pause allows SalesDB to automatically pause during off-hours (6 PM to 8 AM) when not in use, significantly reducing compute costs for a database used only during business hours. Option D is incorrect because reducing vCores alone does not stop charges during idle time, while auto-pause eliminates compute costs entirely when paused. Option A is incorrect because storage reduction does not address the core issue of paying for idle compute. Option B is incorrect because Hyperscale is designed for high-performance, large-scale workloads and is not cost-optimized for a schedule-based usage pattern.

Answer

Reduce storage to 512 GB.

Answer

Change tier to Hyperscale.

Answer

Reduce capacity to 2 vCores.

Question 7

You are automating the creation of an Azure SQL database. You need to ensure that the deployment is idempotent using Azure Resource Manager (ARM) templates. Which deployment mode should you use?

Accepted Answer

Incremental. 'Incremental' mode processes the template as an incremental update to the resource group, making it idempotent. Only resources specified in the template are added or modified; existing resources not in the template are left unchanged. 'Complete' mode deletes resources not in the template, which is not idempotent. 'Validate' mode only validates without deploying, and 'Automatic' is not a valid ARM deployment mode.

Answer

Complete

Answer

Automatic

Answer

Validate

Question 8

You have an Azure SQL Database that must be automatically restarted every night to clear the procedure cache. You plan to use elastic jobs in Azure SQL Database. What should you create first?

Accepted Answer

An elastic job agent. An elastic job agent is the top-level resource that orchestrates jobs. Before creating any jobs, you must create the elastic job agent. The job database (A) stores job definitions but is created as part of agent setup or after. Target groups (C) and job credentials (D) are created after the agent exists.

Answer

A job database

Answer

A target group

Answer

A job credential

Question 9

Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.

Accepted Answer

Select the Azure SQL Database, then navigate to Automatic tuning settings, then enable the desired options, then save changes.. Automatic tuning is configured via the portal by selecting the database, navigating to automatic tuning, enabling options, and saving.

Answer

Navigate to Automatic tuning settings, then select the Azure SQL Database, then enable the desired options, then save changes.

Answer

Select the Azure SQL Database, then enable the desired options, then navigate to Automatic tuning settings, then save changes.

Answer

Enable the desired options, then select the Azure SQL Database, then navigate to Automatic tuning settings, then save changes.

Question 10

You are configuring a new Azure SQL Database for a multi-tenant SaaS application. You need to ensure that each tenant can only access their own rows. Which THREE features can be used to achieve this?

Accepted Answer

Row-Level Security (RLS) with a predicate function.. Row-Level Security (RLS) allows you to control access to rows in a database table based on the characteristics of the user executing a query. By creating a security policy with a predicate function that filters rows by tenant ID (e.g., using SESSION_CONTEXT or USER_NAME()), you can ensure each tenant only sees their own data without changing the application's query logic.

Answer

Always Encrypted with deterministic encryption.

Answer

Dynamic Data Masking for the tenant ID column.

Question 11

You are a database administrator for a large financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases, FinanceDB, is 2.5 TB in size and runs a critical application that requires high availability with automatic failover. The application uses database mail and SQL Agent jobs. During migration, you need to minimize downtime. Which deployment option should you choose?

Accepted Answer

Deploy an Azure SQL Managed Instance in a failover group with a secondary in another region. Azure SQL Managed Instance supports database mail and SQL Agent jobs natively, and deploying it in a failover group with a secondary in another region provides automatic failover and high availability while minimizing downtime during migration. The 2.5 TB size is within Managed Instance limits, and the failover group enables a controlled, continuous-sync replication to the secondary, allowing a swift cutover with minimal data loss.

Answer

Deploy an Azure SQL Database Hyperscale with geo-replication

Answer

Deploy an Azure SQL Managed Instance with zone-redundant configuration

Answer

Deploy a single Azure SQL Database with Active Geo-Replication

Question 12

You manage an Azure SQL Database that uses automatic tuning. The database has the FORCE_LAST_GOOD_PLAN option enabled. A critical query suddenly starts performing poorly after a plan change. What is the expected behavior?

Accepted Answer

The database will automatically revert to the last good plan and log the event in sys.dm_db_tuning_recommendations.. When FORCE_LAST_GOOD_PLAN is enabled and automatic tuning detects a plan regression, Azure SQL Database automatically reverts to the last known good plan. The event is logged in sys.dm_db_tuning_recommendations for audit and analysis. This behavior is part of the automatic plan correction feature, which does not require manual intervention.

Answer

The query will continue with the poor plan until you manually force a plan.

Answer

You will receive a recommendation to revert the plan change.

Answer

The database will automatically revert to the last good plan without notification.

Question 13

Which TWO tools can be used to automate the deployment of database schema changes to Azure SQL Database as part of a CI/CD pipeline? (Choose two.)

Accepted Answer

GitHub Actions with the Azure SQL Database deployment action. GitHub Actions with the Azure SQL Database deployment action (Option B) and Azure DevOps release pipeline with the SQL Server database project (DACPAC) deployment task (Option C) are both valid tools for automating schema deployments to Azure SQL Database in a CI/CD pipeline. Option A (Azure Data Studio SQLCMD mode) is manual and not designed for CI/CD integration. Option D (SQL Server Import and Export Wizard) is a data migration tool, not for schema changes. Option E (SSMS) is a management tool without native CI/CD automation.

Answer

Azure Data Studio with SQLCMD mode

Answer

SQL Server Import and Export Wizard

Answer

SQL Server Management Studio (SSMS)

Question 14

You need to secure an Azure SQL Database. The requirements are: (1) encrypt sensitive columns such as credit card numbers, (2) prevent unauthorized users from seeing the full credit card number even if they have access to the database, and (3) allow authorized applications to decrypt the data. Which two features should you implement? (Choose TWO.)

Accepted Answer

Always Encrypted. Always Encrypted is correct because it encrypts sensitive columns like credit card numbers at the client-side, ensuring the data remains encrypted at rest, in transit, and during processing. This prevents unauthorized users, including database administrators, from viewing the plaintext data, while authorized applications with the column encryption key can decrypt it transparently. Column-level security is also correct because it allows administrators to restrict access to specific columns (e.g., credit card numbers) by granting permissions only to authorized users or applications. This ensures that even if a user has access to the database, they cannot query the sensitive columns without explicit permission. Together, Always Encrypted provides encryption and Column-level security provides fine-grained access control, meeting all requirements.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Row-level security

Question 15

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Accepted Answer

In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.. Diagnostic settings in the Azure portal allow streaming of platform metrics (including 'AllMetrics' for CPU percentage, data IO, log IO) to a Log Analytics workspace cost-effectively. Option A is incorrect because Azure SQL Insights is a paid, advanced monitoring solution that is not necessary for basic metric streaming. Option B is incorrect because Query Store captures query performance data, not resource metrics, and cannot export directly to Log Analytics. Option D is incorrect because manually inserting from sys.dm_db_resource_stats via a T-SQL job is inefficient, not built-in, and lacks the integration of diagnostic settings.

Answer

Enable Azure SQL Insights (preview) for the database.

Answer

Enable Query Store and configure it to export to Log Analytics.

Answer

Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.