AZ-305 Design data storage solutions Practice Question
Which THREE of the following are best practices for designing an Azure SQL Database solution for performance and scalability?
⚠ Common exam trap
The trap here is that candidates may mistakenly think stored procedures add complexity or that disabling automatic tuning ensures consistency, when in fact both practices hinder scalability and performance in Azure SQL Database's managed environment.
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
✓
Use appropriate indexes to optimize query performance
Appropriate indexes, such as clustered and nonclustered indexes, reduce the number of data pages scanned during query execution, directly improving query performance. In Azure SQL Database, index tuning is critical for minimizing I/O and CPU overhead, especially for large tables or complex joins.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use appropriate indexes to optimize query performance
Why this is correct
Indexes are data structures that enable the query engine to locate rows using seek operations instead of full table scans. Appropriate indexes tailored to the workload's predicates, joins, and ordering can reduce I/O and CPU while improving response times. However, they must be balanced against write overhead and storage costs, so they should be designed rather than added indiscriminately.
- ✓
Use elastic pools to manage multiple databases with variable workloads
Why this is correct
Elastic pools group multiple databases with fluctuating resource demands into a shared pool, allowing the aggregate usage to be accommodated rather than paying for peak capacity in each database individually. When workloads spike asynchronously, the pooled resources are reused efficiently, making it a cost-effective scaling approach. This governance model is particularly useful in multitenant patterns where individual database usage varies.
- ✓
Implement read replicas for read-heavy workloads
Why this is correct
Read replicas are asynchronous copies of the primary database that accept read-only traffic, offloading analytical and reporting workloads from the source. In Azure SQL Database, read scale-out automatically routes connections with ApplicationIntent=ReadOnly to a replica, freeing primary resources for writes. This improves overall throughput and isolation of workloads, though replication lag must be considered for fresh reads.
- ✗
Avoid using stored procedures to reduce complexity
Why it's wrong here
Stored procedures are often advantageous because they are precompiled and their execution plans can be reused, reducing compilation overhead and network round trips. They also enable granular security by granting permissions directly on the procedures rather than the underlying tables. Avoiding them can lead to more ad-hoc SQL from clients, which increases parsing complexity and may degrade predictability.
- ✗
Disable automatic tuning to maintain consistent performance
Why it's wrong here
Automatic tuning uses built-in intelligence to identify and mitigate emerging query performance issues, such as index recommendations and plan regression corrections. Disabling it to achieve 'consistent' performance actually risks slowness when workload patterns evolve, since the system cannot adapt. It is a managed, proactive feature in Azure SQL Database that runs with minimal user intervention, so keeping it enabled is a best practice.
Go deeper
Related to this question
About these practice questions
Courseiva writes every AZ-305 question from scratch — 795 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 AZ-305 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 AZ-305 exam.