DP-300 Practice Question: Monitor, configure, and optimize database resources
You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create nonclustered columnstore indexes on large reporting tables.
Creating nonclustered columnstore indexes (option B) improves aggregation and reporting query performance by using columnar storage and batch processing. Configuring a read-scale replica (option C) offloads reporting queries to a secondary replica, reducing contention on the primary for OLTP operations. Enabling result set caching (option E) caches query results in the Premium/Business Critical tiers, reducing repeated reads for static reporting data. Option A (increasing MAXDOP to 8) can lead to parallel query contention and negatively impact OLTP performance, especially on smaller instances. Option D (disabling automatic tuning) removes beneficial plan corrections and is not recommended for improving performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase MAXDOP for the database to 8.
Why it's wrong here
Higher MAXDOP can cause parallelism issues for OLTP queries.
- ✓
Create nonclustered columnstore indexes on large reporting tables.
Why this is correct
Columnstore indexes significantly improve aggregation and scan performance.
- ✓
Configure a read-scale replica and direct reporting queries to it.
Why this is correct
Offloads reporting to a replica, reducing contention on primary.
- ✗
Disable automatic tuning to prevent plan changes.
Why it's wrong here
Automatic tuning generally improves performance; disabling may cause regressions.
- ✓
Enable result set caching for the database.
Why this is correct
Caches query results for repeated reads, reducing resource usage.
Go deeper
Related to this question
Learn chapter
Overview of Azure Data Platform Options
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
About these practice questions
Courseiva writes every DP-300 question from scratch — 906 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.