DP-700 Monitor and Optimize an Analytics Solution Practice Question
Exhibit
{
"query": "SELECT * FROM large_table",
"plan": "Table Scan",
"duration": "500s",
"tableSize": "1TB",
"columns": "*"
}Refer to the exhibit. The query is performing a full table scan on a 1TB table. What is the most effective way to optimize this query?
⚠ Common exam trap
Candidates often look for complex indexing or partitioning strategies, missing the simpler, fundamental query optimization practice of reducing I/O via column projection.
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
✓
Replace 'SELECT *' with specific column names.
Selecting only the necessary columns (column projection) is a fundamental best practice in analytical query optimization. By avoiding 'SELECT *', you reduce the amount of data read from storage and transferred over the network. In column-oriented stores like those in Fabric, this allows the engine to only read the relevant columns, drastically reducing I/O and latency for queries on large datasets.
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 cluster memory size.
Why it's wrong here
Adding more memory does not resolve the inefficient practice of reading unnecessary data from storage. Even with more memory, the engine must still perform the I/O for all columns in the table, which is the primary source of the performance overhead in this scenario.
- ✓
Replace 'SELECT *' with specific column names.
Why this is correct
Selecting only the columns needed significantly reduces the data volume processed. Since Fabric uses columnar storage, reading specific columns allows the engine to skip the data for unneeded columns, leading to much faster query completion times and less load on the underlying capacity.
- ✗
Add a clustered index to all columns.
Why it's wrong here
Adding indexes to all columns would cause massive storage overhead and slow down write operations significantly. It does not address the inefficiency of the 'SELECT *' statement and is not a practical solution for optimizing a large-scale analytical query.
- ✗
Enable query parallelism.
Why it's wrong here
Query parallelism is already managed by the Fabric engine. Forcing it or assuming it is not active does not solve the fundamental issue of data volume. You must first reduce the data being scanned before parallelism can effectively accelerate the remaining, more optimized workload.
About these practice questions
Courseiva writes every DP-700 question from scratch — 152 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 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-700 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-700 exam.