DP-300 Practice Question: Monitor, configure, and optimize database resources
You are the database administrator for a large e-commerce application hosted on Azure SQL Database in the Business Critical tier (8 vCores). The database has a table named Orders with 500 million rows. The table is partitioned by OrderDate using a monthly partition function. The application frequently runs queries that aggregate sales by month for the current year. Users report that these queries are slow. You examine the query execution plans and notice that the queries are scanning all partitions, not just the relevant ones. You need to improve query performance without changing the application code. Which action should you take?
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
✓
Re-create the partition function with a different boundary type (e.g., RIGHT instead of LEFT) to align with query patterns.
The issue is that partition elimination is not occurring, meaning the query scans all partitions instead of only the relevant ones for the current year. Since the table is partitioned by OrderDate using a monthly partition function, the alignment of partition boundaries with the query predicate is critical. If the current partition function uses LEFT boundaries, the boundary values represent the start of each partition, but queries for the current year might include dates that cross boundaries in a way that prevents elimination. Re-creating the partition function with RIGHT boundaries ensures that each boundary value is the first day of the next partition, which often aligns better with range queries like 'WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-12-31''. This can enable partition elimination without changing application code. Option A (update statistics) does not affect partition elimination. Option B (columnstore index) improves compression and query speed but does not force partition elimination. Option D (indexed view) requires code changes and does not directly cause partition elimination. Therefore, option C is correct.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Update statistics for the Orders table.
Why it's wrong here
Does not affect partition elimination.
- ✗
Create a columnstore index on the Orders table.
Why it's wrong here
May improve scan performance but not partition elimination.
- ✓
Re-create the partition function with a different boundary type (e.g., RIGHT instead of LEFT) to align with query patterns.
Why this is correct
Changing boundary type can improve partition elimination for date range queries.
- ✗
Create an indexed view that pre-aggregates sales by month.
Why it's wrong here
Does not address partition elimination.
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.
Key term
Azure SQL Indexes
Structures in Azure SQL Database that speed up data retrieval by providing quick access paths to rows, similar to a book index.
About these practice questions
This DP-300 question is part of Courseiva's 906-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.