Exam 70-762 topic 1 question 35 discussion

Actual exam question from Microsoft's 70-762

Question #: 35
Topic #: 1

You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues:
✑ At each level of the query plan, a low total number of rows are processed.
✑ The query uses many operations. This results in a high overall cost for the query.
You need to identify the information that will be useful for the optimizer.
What should you do?

A. Start a SQL Server Profiler trace for the event class Auto Stats in the Performance event category.
B. Create one Extended Events session with the sqlserver.missing_column_statistics event added.
C. Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.
D. Create one Extended Events session with the sqlserver.missing_join_predicate event added.

Show Suggested Answer

Suggested Answer: D 🗳️
The Missing Join Predicate event class indicates that a query is being executed that has no join predicate. This could result in a long-running query.

by Heisenberg008 at Feb. 24, 2020, 4:16 a.m.

Comments

Submit Cancel

HassanKhan2

Highly Voted 5 years, 4 months ago

I would say 'B' is the correct answer

upvoted 14 times

als2kool

5 years, 4 months ago

I believe that D the original answer is correct. It says a low number of rows are returned but a high number of operations are being performed. It's more likely that something is wrong with the query than stats.

upvoted 1 times

JohnFan

5 years, 3 months ago

The Missing Join Predicate event class indicates that a query is being executed that has no join predicate. Not useful for the optimizer

upvoted 1 times

...

Postarion

Highly Voted 5 years, 3 months ago

B is Correct. A is wrong: The Auto Stats event class indicates that an automatic updating of index and column statistics has occurred. Auto Stats also fires when statistics are being loaded for use by the optimizer. C is wrong: Soft Warnings are not useful for the optimizer D is wrong: The Missing Join Predicate event class indicates that a query is being executed that has no join predicate. Not useful for the optimizer

upvoted 14 times

...

MML

Most Recent 4 years, 11 months ago

When you have a missing join predicate you expect a big number of row due to the cross join, so I don't think is D. In my opinion correct answer is B.

upvoted 8 times

...

mrn0107

5 years ago

The Missing Column Statistics event class indicates that column statistics that could have been useful for the optimizer are not available. By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query. This can cause the optimizer to choose a less efficient query plan than expected.

upvoted 1 times

...

Nickname17

5 years, 1 month ago

Hi, there is no Soft Warnings, there’s sort warnings

upvoted 1 times

...

JohnFan

5 years, 3 months ago

The Missing Join Predicate event class indicates that a query is being executed that has no join predicate. This could result in a long-running query. https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/missing-join-predicate-event-class?view=sql-server-ver15 The Missing Column Statistics event class indicates that column statistics that could have been useful for the optimizer are not available. By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query. This can cause the optimizer to choose a less efficient query plan than expected. https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/missing-join-predicate-event-class?view=sql-server-ver15

upvoted 4 times

...