Developing SQL Databases — Question 76

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?

Answer options

• 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.

Correct answer: A

Explanation

The correct answer is A because starting a SQL Server Profiler trace for the Performance statistics event class provides detailed insights about query performance and metrics that are essential for optimization. Options B, C, and D do not provide the necessary performance-related statistics that would assist in optimizing the query effectively.