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.


aboutME

I am John Fan Zhang, a data analyst and finance researcher. I hold a PhD in finance, CFA charter and full membership of CFA New Zealand Society. I have fifteen-year experience in corporate investment and eight-year experience in advanced data analysis. My research focuses on the effect of social psychology (culture) on financial decisions. Finance research involves heaps of data analyses that lead me to the data field. I am a Microsoft Certified Solutions Expert (MCSE): Data Management and Analytics (Excel, Power BI, and SQL). Aside from Excel, Power BI and SQL, I am also familiar with econometric tools such as Stata, Eviews, and MATLAB. I use OX and Python for programming. I am an active data community event participant, volunteer, speaker, moderator, program reviewer, including PASS Marathon 2020, Global AI BootCamp Auckland 2019, SQL Saturday Auckland (2017, 2018, 2019), and Definity Conference (2018, 2019, 2020, Auckland, New Zealand).

Auckland, New Zealand

  • Google Site
  • Twitter
  • LinkedIn

©2016 BY JOHN'S DATA STORY

bottom of page