top of page

Performance, error and warning events

Missing_Column_Statistics, Missing_Join_Predicate, and Sort Warnings belongs to the Errors and Warnings event category, which contains general error and warning events.

 

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.

 

A related concept is Missing_Join_Predicate. 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.

Predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.

 

The Sort Warnings event class indicates that sort operations do not fit into memory. This does not include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).

If a query involving a sort operation generates a Sort Warnings event class with an EventSubClass data column value of 2, the performance of the query can be affected because multiple passes over the data are required to sort the data. Investigate the query further to determine if the sort operation can be eliminated.

 

Auto Stats and Performance Statistic belong to Performance event category

 

Auto Stats Event Class Indicates that an automatic updating of index and column statistics has occurred.

Auto Stats belongs to the Performance event category, which monitor Showplan event classes and event classes that are produced from the execution of SQL data manipulation language (DML) operators.

 

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.

 

sqlserver.error_reported: This event gets fired every time when an error happens on the SQL Server.

 

A dialogue

 

Hi Gerard,

We use Microsoft SQL Server Profile to evaluate a query. The Profiler report indicates the following issues:

(1) At each level of the query plan, a low total number of rows are processed.

(2) The query uses many operations. This results in a high overall cost for the query.

We need to identify the information that will be useful for the optimizer. Here are some options: • 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. • E. Start a SQL Server Profiler trace for the event class Performance statistics in the Performance event category. • F. Create one Extended Events session with the sqlserver.error_reported event added.

The popular arguments are among D and E. That is, creating one Extended Events session with the sqlserver.missing_join_predicate event added, or starting a SQL Server Profiler trace for the event class Performance statistics in the Performance event category. However, I want to go for B. Create one Extended Events session with the sqlserver.missing_column_statistics event added. What do you think? Regards, John John, having no knowledge at all of the SQL Server Profiler my answer is just an indication of what I think sensible. I think we can dismiss A, C and F immediately as correct answer for obvious reasons. Right? That leaves us with: B. only collects data for columns of which the statistics are missing. If statistics for all columns are present, this solution will not give you any clue as to what could be wrong with the query. (this answer is often indicated on Google as the correct answer) D. actually the same as B, but now for the JOIN predicate. So, if all JOIN's are present, then this solution will not give you any clue as to what could be wrong with the query. E. Performance Statistics seem a good indication of the performance of a query to me. It's objective: "The Performance Statistics event class can be used to monitor the performance of queries, stored procedures, and triggers that are executing." (https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/performance-statistics-event-class?view=sql-server-ver15) So, all in all I would go for answer E. Let me know what you think. Gerard Hi Gerard, I hope everything is going well with you during the time of lockdown. Continue our discussion :), I agree with your opinion about option E. I have read a bit more regarding options B and D. For B, I think it also makes some sense. 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, we 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 For D, I think it solves the problem of long-running queries, not the issues stated in the question. 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 Therefore, I think we can get rid of D, but not so certain about B. Please let me know how do you think? Regards, John Hi John, I think we can get rid of option B for the same reason as that we can get rid of option D. Suppose you have a (too) long running query and the Missing Column Statistics event class states that all columns have the necessary statistics. What does the Missing Column Statistics event class tell you about the cause of the too long running query? Right, nothing! Just like the Missing Join Predicate event class is useless in a too long running query where all JOIN's are present. Cheers, Gerard

 

Two good posts:

" What exactly does “No Join Predicate” mean in SQL Server? " => https://dba.stackexchange.com/questions/35082/what-exactly-does-no-join-predicate-mean-in-sql-server

bottom of page