DP-300 Azure SQL Database service tiers Practice Question
You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the Standard tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?
⚠ Common exam trap
The trap is assuming read-only replicas are available in all Azure SQL Database tiers. They are only supported in Premium, Business Critical, and Hyperscale tiers, not in Standard (DTU) tiers.
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
✓
Increase the service tier to S3 during the end-of-month period
Increasing the service tier to S3 during the end-of-month period provides more DTUs and IO resources, which directly addresses the performance degradation without a permanent cost increase. The cost is only incurred during the reporting period, making it a cost-effective short-term solution. Option D is incorrect because read-only replicas are not supported in the Standard (DTU) S2 tier; they are only available in Premium, Business Critical, and Hyperscale tiers. Therefore, this option is not feasible. Options B and C may help but are not as effective or risk affecting write 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 the service tier to S3 during the end-of-month period
Why this is correct
Increasing to S3 during reporting period provides more resources, improving performance without a permanent cost increase. This is the most feasible option given the tier limitation.
- ✗
Add nonclustered indexes to the tables used in reports
Why it's wrong here
Adding nonclustered indexes can improve query performance but may not be sufficient for large aggregation reports that require significant IO. It does not address the resource bottleneck directly.
- ✗
Convert the tables to clustered columnstore indexes
Why it's wrong here
Clustered columnstore indexes are optimized for large aggregations but can degrade OLTP write performance. Converting all tables is a major change and not recommended for a CRM system.
- ✗
Create a read-only replica and direct reports to it
Why it's wrong here
Read-only replicas are not available in the Standard S2 tier. They require Premium or higher, so this option is not feasible in the current environment.
Quick reference
AWS S3 Storage Class Comparison
| Storage Class | Min Duration | Retrieval | Use Case |
|---|---|---|---|
| S3 Standard | None | Immediate | Frequently accessed data |
| S3 Standard-IA | 30 days | Immediate | Infrequent access, rapid retrieval |
| S3 One Zone-IA | 30 days | Immediate | Non-critical infrequent data |
| S3 Intelligent-Tiering | None | Immediate–hours | Unknown or changing access patterns |
| S3 Glacier Instant | 90 days | Milliseconds | Archive with instant retrieval |
| S3 Glacier Flexible | 90 days | Minutes–hours | Archive, flexible retrieval |
| S3 Glacier Deep Archive | 180 days | Hours | Long-term compliance archive |
Go deeper
Related to this question
Learn chapter
Implementing High Availability for Azure SQL Databases
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
This DP-300 question is part of Courseiva's 574-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.