Hotspot Question You have SQL Server on an Azure virtual machine. You review the query plan shown in the following exhibit. For each of the following statements, select yes if the statement is true. O

Question

Exhibits

Answer Area

• Physical Operation: Key LookupYes
• Logical Operation: Key LookupYes
• Estimated Execution Mode: RowYes
• Storage: RowStoreYes
• Estimated Operator Cost: 0.149639 (98%)Yes
• Estimated I/O Cost: 0.003125Yes
• Estimated Subtree Cost: 0.149639Yes
• Estimated CPU Cost: 0.0001581Yes
• Estimated Number of Executions: 48Yes
• Estimated Number of Rows: 1Yes
• Estimated Row Size: 31 BYes
• Ordered: TrueYes
• Node ID: 8Yes

Explanation

This hotspot question tests your ability to read and interpret a SQL Server query execution plan, identifying key operators, cost percentages, and performance indicators such as missing index warnings, parallelism, and scan vs. seek operations.

Approach. When analyzing a SQL Server query plan, focus on the following: (1) Operators with high relative cost percentages indicate bottlenecks - a Table Scan or Clustered Index Scan typically signals a missing or unused index. (2) A yellow warning triangle on an operator (especially SELECT) indicates a missing index recommendation that SQL Server has detected. (3) Parallelism operators (Gather Streams, Repartition Streams) indicate the query is using multiple CPU threads, meaning the degree of parallelism (DOP) is greater than 1. (4) Hash Match joins are typically more expensive and used when indexes are absent or join columns are not indexed, whereas Nested Loops are preferred for smaller, indexed datasets. Evaluate each statement against these observable plan characteristics to determine Yes or No.

Concept tested. Reading and interpreting SQL Server query execution plans, including identifying costly operators (Table Scan, Hash Match), missing index warnings, parallelism usage, and understanding the performance implications of each operator in the plan.

Reference. Microsoft Docs: 'Displaying and Saving Execution Plans' and 'SQL Server Query Processing Architecture Guide' - https://learn.microsoft.com/en-us/sql/relational-databases/performance/execution-plans

No community discussion yet for this question.