Exam 70-761 topic 1 question 148 discussion

Actual exam question from Microsoft's 70-761

Question #: 148
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a hash index on the primary key column.
Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
Reference:
https://msdn.microsoft.com/en-us/library/dn133190.aspx

by Backy at Sept. 23, 2020, 1:17 p.m.

Comments

Submit Cancel

Billybob0604

4 years, 5 months ago

A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index. When you search for a value in a non-clustered index, and your query needs more fields than are part of the index leaf node (all the index fields, plus any possible INCLUDE columns), then SQL Server needs to go retrieve the actual data page(s) - that's what's called a bookmark lookup. In some cases, that's really the only way to go - only if your query would require just one more field (not a whole bunch of 'em), it might be a good idea to INCLUDE that field in the non-clustered index. In that case, the leaf-level node of the non-clustered index would contain all fields needed to satisfy your query (a "covering" index), and thus a bookmark lookup wouldn't be necessary anymore.

upvoted 1 times

...

Backy

4 years, 9 months ago

How is it supposed to work? If the execution plan contains bookmark lookups then it is not a Memory-optimized table. Then "You create a hash index on the primary key column" but a hash index can be created only on a Memory-optimized table. Catch-22 The question has to give a hint that you take some steps to convert the table to a Memory-optimized table in the first place. Once you create a primary key on it you get an index automatically, by default it is non-hash but it will be enough to prevent any bookmark lookups. You do not need a hash index

upvoted 1 times

...