Exam 70-762 topic 1 question 113 discussion

Actual exam question from Microsoft's 70-762

Question #: 113
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 Performance statistics 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.error_reported event added.

Show Suggested Answer

Suggested Answer: A 🗳️
The Performance Statistics event class can be used to monitor the performance of queries, stored procedures, and triggers that are executing. Each of the six event subclasses indicates an event in the lifetime of queries, stored procedures, and triggers within the system. Using the combination of these event subclasses and the associated sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats dynamic management views, you can reconstitute the performance history of any given query, stored procedure, or trigger.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/performance-statistics-event-class?view=sql-server-2017

by Heisenberg008 at Feb. 25, 2020, 4:39 a.m.

Comments

Submit Cancel

Heisenberg008

Highly Voted 5 years, 3 months ago

B might be Correct.

upvoted 5 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.

upvoted 2 times

kevinliu3

5 years, 1 month ago

You don't answer why B is wrong?

upvoted 5 times

Nickname17

5 years, 1 month ago

Suppose you have a very long query and the missing column statistics event class states that all columns should have the necessary statistics. What does the missing column statistics event class tell you about the cause of the long running query? Right, nothing!

upvoted 1 times

delgadillo

5 years ago

So, B is correct ?

upvoted 1 times

Jay2

5 years ago

Yes, B is correct

upvoted 1 times

...

Michael93

4 years, 8 months ago

JohnFan posts lots of info no conclusion, always

upvoted 16 times

...

SoupDJ

Most Recent 4 years, 7 months ago

I agree - B. There could be many reasons for the poor performance, and Performance Statistics seems like a good general way to begin investigating. However, the question asks - "Identify information useful for the optimizer" - that's a little more specific.

upvoted 1 times

...

stm22

4 years, 10 months ago

B: 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-column-statistics-event-class?view=sql-server-ver15

upvoted 3 times

...