Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 109 discussion

Actual exam question from Microsoft's 70-762

Question #: 109
Topic #: 1

HOTSPOT -
You use Query Store to optimize a query in a database.
The query has two execution plans:
✑ Plan 2 is shown in the Plan 2 Execution Plan exhibit. (Click the Plan 2 Execution Plan tab.)
✑ Plan 10 is shown in the Plan 10 Execution Plan exhibit. (Click the Plan 10 Execution Plan tab.)
You create an index at 22:24 based on the missing index suggestion in Plan 2.
The average duration statistics for the query is shown in the Tracked Queries exhibit. (Click the Exhibit button.)
You need to analyze the operators in the two execution plans.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Plan 2 Execution Plan -

Plan 10 Execution Plan -

Tracked Queries -

Hot Area:

Show Suggested Answer

Suggested Answer:

by Zikato at Feb. 10, 2020, 6:06 p.m.

Comments

Submit Cancel

Kuratko

Highly Voted 5 years, 4 months ago

NO (same or less -> smaller data & less data pages in NCI); YES (Icons says "we are scans"; labels says: "we are seeks" -> seeks are *mostly* effec.) YES ("same data" in shortest duration)

upvoted 24 times

JohnFan

5 years, 3 months ago

For The average duration statistics for the query, read this https://books.google.co.nz/books?id=xMK3DwAAQBAJ&pg=PA45&lpg=PA45&dq=The+average+duration+statistics+for+the+query&source=bl&ots=IQpz6ETIfl&sig=ACfU3U2S-5eiVov3tI9_gt6X3CDbjIVgPw&hl=en&sa=X&ved=2ahUKEwiX8dCSi6PoAhXP7HMBHZBLBQIQ6AEwCnoECFwQAQ#v=onepage&q=The%20average%20duration%20statistics%20for%20the%20query&f=false

upvoted 1 times

...

raja1234567890

5 years ago

YES YES NO A system's throughput defines its overall capability to process data. DBMS throughput is measured in queries per second, transactions per second, or average response times. DBMS throughput is closely related to the processing capacity of the underlying systems (disk I/O, CPU speed, memory bandwidth, and so on), so it is important to know the throughput capacity of your hardware when setting DBMS throughput goals. Higher value represents high throughput which planId 2 has.

upvoted 1 times

raja1234567890

5 years ago

I mean NO YES NO

upvoted 4 times

MML

4 years, 11 months ago

I agree

upvoted 2 times

Kuratko

4 years, 9 months ago

new vote: 1.NO 2.NO (question: "clustered index scan", both execution plans has only "NCI", so you cannot compare them - right?) 3.NO (my bad, sorry)

upvoted 1 times

...

geekeek1

4 years, 5 months ago

Why did you this answer get so many upvotes? Is this the correct answer? Can someone explain plz

upvoted 1 times

...

mamarach

Highly Voted 4 years, 11 months ago

I'm confused!

upvoted 16 times

geekeek1

4 years, 5 months ago

Me too bro

upvoted 2 times

arun597

4 years, 5 months ago

any idea?

upvoted 1 times

...

Luzix

Most Recent 4 years, 7 months ago

The Index seek icons correspond to Index Scan. See this page: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175913(v=sql.105)?redirectedfrom=MSDN

upvoted 2 times

...

stm22

4 years, 11 months ago

hash match plan 2 = 19 hash match plan 10 = 66 plan 10 processed more data = YES plan 2 index scans = 81% plan 10 index scans = 31% plan 2 less efficient = YES the higher the position of the Execution Plan inside the graph the higher the average query execution duration was when using the Execution Plan. throughput will increase with plan 10 = YES

upvoted 6 times

Cococo

4 years, 9 months ago

Last one is not about the duration, it is about throughput and throughput (CPU, IO, LOG) will increase only if they switch to a memory-optimized solution.

upvoted 3 times

...

Zikato

5 years, 4 months ago

I see no clustered index scan there. Also the hash match operator didn't process morerows. By making the index seek more effective the percentage moved to other operator - number of rows is the same.

upvoted 11 times

Bartek

5 years, 4 months ago

Yes, in addition I think query throughpull II increase in plan 10. Just we can see it on Query Store diagram

upvoted 1 times

JohnFan

5 years, 3 months ago

A Database Throughput Unit (DTU) is a single metric to represent CPU, memory, and IO thresholds. Depending on the type of workload you run, you can achieve five to 20 times performance gains with higher throughput and lower latency after migrating an existing disk-based table to a memory-optimized table. Performance is often measured in terms of throughput rate or the number of rows loaded per second. The use of memory-optimized tables is well-suited for the ingestion of high-volume inserts because it prevents the bottlenecks commonly resulting from locking and eliminates logging. Consequently, the throughput rate (number of rows loaded per second) can substantially increase.

upvoted 2 times

...

Hoglet

4 years, 6 months ago

A seek followed by a scan is still called a Seek. That we have a Hash Match here indicates that SQL Server is expecting to process a lot of rows, i.e. from a Seek then Scan

upvoted 1 times

...