top of page

Optimizing a SQL query


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.

 

1. About SQL Server Profiler: You can use SQL Server Profiler as a client-side option. The overhead of running server-side traces is much less than the overhead of using SQL Server Profiler, but the overhead is still significant. Therefore, take care when using SQL Trace in a production environment regardless of the approach you take and disable tracing as soon as possible.

2. SQL Server Trace: Although SQL Trace is designated as a deprecated feature and will be removed from a future release of SQL Server, it remains an available option in SQL Server 2016. You can define server-side traces by using system stored procedures and then run these traces on demand or on a scheduled basis.

3. Extended Events: Using Extended Events is a lightweight approach to capturing query plans. You can review the query plans captured by this Extended Event using the graphical interface in SQL Server Management Studio. 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.


bottom of page