SQL Server Profiler--Identify exceptions

SQL Server Profiler--Identify exceptions

In a perfect environment, all anomalies should be captured, processed, and recorded. A person regularly views these logs and creates error reports based on the exceptions that occur, so that they can be debugged in a timely manner and can avoid recurrence in the future. However, it is always possible to see that the application is constantly seeing various exceptions from the database to the user interface, and that none of these exceptions are recorded. Worse, the application searches for and digests these anomalies and occasionally returns strange data to the user who does not understand what is going on. In order to identify these situations, it is common to suggest an analysis to observe these anomalies so that they can be found and processed to avoid affecting too many users.

Tracking exceptions can be very simple, starting with the TSQL template, which includes audit login and audit logout events and existing connection events (where all events can be removed). The rest is "rpc:starting" and "sql:batchstarting", both of which are required to track the Transact-SQL that throws an exception, whether the exception is caused by SQL batch or RPC calls. In this case, tracking the startup event is more important than tracing the completion event, because some errors can cause the completion event to fail to fire a given query.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M02/56/AC/wKioL1SKi-Gwb-oaAAHqNVOodBs252.jpg "border=" 0 "height=" 537 "/>

Note, exception, and user error message events in the error and warning categories are also added to RPC and SQL events. Whenever a client forcibly disconnects, notice that the event is fired, and the best example is the client query timeout, which usually implies performance or blocking issues. Whenever an arbitrary type of exception occurs, the exception event is fired, and when a message is sent back to the additional data about the lock event in the form of an exception, the user error message event is fired, or when a series of states change, the event is also fired. If the user switches from one database to another.

We also recommend adding a eventsequence column for each selection event class, which makes it easier to query the data later. The recommended event and Column Completion Event selection dialog box for monitoring exceptions is shown below.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M00/56/AC/wKioL1SKi-Ky-SmMAAKCzfUTAQo129.jpg "border=" 0 "height=" 537 "/>

Note: SQL Server uses exceptions to send information within each phase of the query execution process. One of its warning signs is an exception event that does not have a corresponding user error message event. If such a situation occurs, the user is not required to handle the error.

After you select the appropriate event, you can edit the trace and start the event. Users may want to run this type of trace in the background and do some temporary collection. Typically, it is important to collect this data during a busy activity, which can help you find the exceptions that users may encounter. For example, capturing something special, this kind of tracking is more like casting a net and hoping to catch something, so timing is essential. The user may find an exception, and may not find it, but just because no exception is found during a collection period can not be considered as an exception, so be sure to monitor the exception frequently.

If you capture the data and transfer it to a table, finding the exception that occurred is a problem:

1. All Note Events (event class 16) and Transact-SQL or RPC events (event classes 13 and 10, respectively) on the same SPID that are disconnected.

2. All Exception events (event Class 33) and a user error message event immediately followed (event class 162), as well as Transact-SQL or RPC events on the same SPID preceding the exception.

All the forward and backward logic can be encoded with the Eventsequence column, and it is recommended to include this column in trace management. The following query uses this logic to find out all user exceptions and disconnects, related error messages, and queries that cause failures:

; With Exceptions as (SELECTT0. Spid,t0. Eventsequence,coalesce (T0. TextData, ' Attention ') as EXCEPTION,T1. TextData as Messagetextfrom tracetable t0left OUTER JOIN tracetable T1 ONT1. Eventsequence = T0. Eventsequence + 1AND T1. EventClass = 162wheret0. EventClass in (16,33) and (T0. EventClass = + OR T1. Eventsequence is not NULL)) SELECT *from exceptionscross APPLY (select TOP (1) TextData as Querytextfrom tracetable QUERIESWH Erequeries.spid = Exceptions.spidand Queries.eventsequence < Exceptions.eventsequenceand Queries.EventClass in ( 10,13) ORDER by Eventsequence DESC) p

If you have collected a large number of events, you can improve the performance of the query by creating an index on the Eventsequence column on the tracking table.

Tip: If you are aware of an exception that occurs when a stored procedure is called, but more information is needed to determine exactly in what order these events will fire the exception, you may need to tune the performance of a single query in conjunction with the same events described earlier. Changing the query event to completion to the appropriate startup class and adding exceptions and user error information events, similar to the example of tuning a single query, would also need to be run directly in SQL Server Performance Analyzer along with the filters on the working SPID.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1589292

SQL Server Profiler--Identify exceptions