Exam 70-761 topic 1 question 164 discussion

Actual exam question from Microsoft's 70-761

Question #: 164
Topic #: 1

HOTSPOT -
You have a database that contains the following tables: tblEmployees and tblSalesSummary. Each record contains approximately one million records.
You use Microsoft SQL Server Management Studio (SSMS) to run two queries. The Include Actual Execution Plan option is enabled.
Both queries return the same results. SSMS generates the execution plans shown in the exhibit. (Click the Exhibit button.)
You need to troubleshoot the queries.
How should you interpret the execution plans? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Hot Area:

Show Suggested Answer

Suggested Answer:

References:
https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017

by M4x at Sept. 13, 2019, 10:55 a.m.

Comments

Submit Cancel

M4x

Highly Voted 5 years, 9 months ago

Second box is wrong. Correct answer 'The index spool operation is executed many times' Cost 97% and is the inner table https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017#nested_loops

upvoted 17 times

HA2020

4 years, 7 months ago

thank you, here too: https://sqlperformance.com/2019/11/sql-performance/eager-index-spool-optimizer

upvoted 1 times

Linderen

4 years, 4 months ago

In the article above: "Eager index spools are often a sign that a useful permanent index is missing from the database schema." Maybe it's a missing index?

upvoted 1 times

...

HaykM

4 years, 6 months ago

I think no, spool operation is executed once for transforming subselect to join

upvoted 1 times

...

Linderen

Most Recent 4 years, 4 months ago

Second box, answer is "no index". Seems there's a missing non-clustered index on FK column EmployeeID.

upvoted 1 times

...