The Error and Warning event classes can be used to monitor many of the errors and warnings that are raised by Microsoft® SQL Server™ and components such as OLE DB.
| Event class | Description |
|---|---|
| ErrorLog | Error events have been logged in the SQL Server error log. |
| EventLog | Events have been logged in the Microsoft Windows NT® application log. |
| Exception | Exception has occurred in SQL Server. |
| Execution Warnings | Any warnings that occurred during the execution of a SQL Server statement or stored procedure. |
| Hash Warning | Hashing operation may have incurred a problem. |
| Missing Column Statistics | Column statistics that could have been useful for the optimizer are not available. |
| Missing Join Predicate | Query is being executed that has no join predicate. This could result in a long-running query. |
| OLEDB Errors | OLE DB error has occurred. |
| Sort Warnings | 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). |
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 otherwise expected. For more information about creating column statistics, see Statistical Information.
The Execution Warnings event class can be monitored to determine if and how long queries had to wait for resources before proceeding. This is important for determining if there are any contention issues in the system that can affect performance and therefore need investigating. Use the Locks event classes to determine the objects affected.
The Sort Warnings event class can be used to monitor query performance. If a query involving a sort operation generates a Sort Warnings event class with an Event Sub Class 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.
The Hash Warning event class can be used to monitor when a hash recursion or hash bail has occurred during a hashing operation. Hash recursion occurs when the build input does not fit into memory, resulting in the input being split into multiple partitions, which are processed separately. If any of these partitions still do not fit into memory, they are further split into sub-partitions, which are then processed separately. This process continues until each partition fits into memory or the maximum recursion level is reached (displayed in the Integer Data data column), thus causing hash bail to occur.
Hash bail occurs when a hashing operation reaches its maximum recursion depth and reverts to an alternate plan to process its remaining partitioned data. Hash bail usually occurs due to skewed data, trace flags, or bit counting. To eliminate or reduce the chance of hash bail occurring, verify that statistics exist on the columns being joined or grouped. For more information, see Statistical Information.
If hash bail continues to occur each time the query is executed, consider using an optimizer hint to force a different algorithm to be used by the query optimizer and then compare the performance of the query. For more information about Join Hints, see FROM.
These are the event-specific data columns for the Error and Warning event category.
| Event class | Data column | Description |
|---|---|---|
| ErrorLog | Severity | Error severity. |
| Event Sub Class | Error number. | |
| Text | Text of the error message. | |
| EventLog | Binary Data | Supplied binary data, if available. |
| Severity | Error severity. | |
| Event Sub Class | Error number, if available. | |
| Text | Text of the error message, if available. | |
| Exception | Integer Data | Error number. |
| Severity | Error severity. | |
| Event Sub Class | Server state. | |
| Execution Warnings | Event Sub Class | Can have these values:
1 = Query wait. The query must wait for resources before it can execute. 2 = Query time-out. The query timed out while waiting for resources it required to execute. |
| Integer Data | The time (in seconds) the query had to wait before continuing or timing out. | |
| Hash Warning | Event Sub Class | The type of hash operation. Can have these values:
0 = Hash recursion. 1 = Hash bail. |
| Integer Data | Recursion level (Hash recursion only). | |
| Object ID | Hash partition node ID. | |
| Missing Column Statistics | Text | List of the columns with missing statistics. |
| OLEDB Errors | Text | Error message. |
| Sort Warnings | Event Sub Class | Can have these values:
1 = Single pass. When the sort table was written to disk, only a single additional pass over the data to be sorted was required to obtain sorted output. 2 = Multiple pass. When the sort table was written to disk, multiple passes over the data to be sorted were required to obtain sorted output. |
| Error Messages | Monitoring the Error Logs |