DP-300 Practice Question: Monitor, configure, and optimize database resources
You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?
⚠ Common exam trap
DP-300 often tests the difference between actions that improve performance generally (query hints, stored procedures, memory) and actions that specifically reduce logical reads (indexes, statistics).
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 a nonclustered index on the columns used in WHERE clause
Option B is correct because a nonclustered index on the WHERE-clause columns gives the optimizer a narrow access path, so it can seek directly to matching rows instead of scanning the base table or clustered index, which lowers the number of 8 KB pages read and therefore logical reads. Option E is correct because updating statistics gives the optimizer accurate cardinality estimates, enabling better plan choices such as index seeks and appropriate join strategies, which reduces the pages touched per execution. Option A is not correct because forcing an index with a hint does not by itself reduce logical reads and can even increase them if the optimizer's original plan was better. Option C is not correct because wrapping the query in a stored procedure mainly aids plan reuse and parameterization, not the number of pages read per execution. Option D is not correct because increasing max memory affects buffer pool caching and physical I/O, not the logical read count, which is measured independently of whether pages come from memory or disk.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add query hints to force index usage
Why it's wrong here
Forcing an index via a query hint can raise logical reads if the optimiser's chosen plan was already cheaper for that predicate. It tempts because hints override poor cardinality estimates, and would be correct when the optimiser demonstrably picks a worse plan, not as a blanket read-reduction tactic.
- ✓
Create a nonclustered index on the columns used in WHERE clause
Why this is correct
A nonclustered index stores the WHERE-clause key values in a separate B-tree structure, letting the engine seek directly to matching rows instead of scanning every page. This sharply reduces the pages touched per execution, directly lowering logical reads — the metric the stem asks you to minimise.
- ✗
Rewrite the query as a stored procedure
Why it's wrong here
Wrapping the query in a stored procedure changes neither the access path nor the pages touched, so logical reads stay the same. It tempts because procedures cache execution plans and reduce compilation and network traffic, which would be the right goal when tuning CPU or round-trips rather than read volume.
- ✗
Increase the database max memory setting
Why it's wrong here
Max memory governs buffer pool size, not the number of pages a query requests, so logical reads are unchanged. It tempts because more memory can eliminate physical reads through caching, and would be the right lever when the bottleneck is disk I/O rather than the logical read count itself.
- ✓
Update statistics on the tables involved
Why this is correct
Updating statistics gives the query optimiser accurate cardinality estimates, so it can choose a more selective plan with fewer page accesses. This directly reduces logical reads when stale statistics cause scans instead of seeks, satisfying the stem's requirement to lower reads without changing the query itself.
Go deeper
Related to this question
Learn chapter
Optimizing Database Query and Index Performance
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 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 and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
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.