SQL Server Profiler-Debug deadlock

SQL Server Profiler-Debug Deadlock

Tracking generic exceptions is simple, but dealing with deadlocks is difficult, with a deadlock message number of 1205 and a severity of 13. The only suggestion that the server can "rerun the transaction" is that it cannot get enough data from the server to help determine what exactly happened and why it happened, even if the error message returned by the servers is worthless.

SQL Trace has tools that provide help in isolating and debugging deadlock states, but SQL Server 2005 has brought these tools to a new level, providing a very useful graphical interface to help users solve these difficult problems. To clarify what is provided, it will show how to force a deadlock in the tempdb database, starting with the following code:

Use Tempdbgo CREATE TABLE deadlock_table (ColumnA int not NULL PRIMARY KEY) GO INSERT deadlock_tableselect 1 UNION all Selec T 2GO

By starting two separate transactions and updating rows in reverse order, you can raise a deadlock and watch how SQL tracing helps debug the damn lock.

Note: The following example assumes that the user has identified two stored procedures or queries involving the triggering of a deadlock, whether based on exception tracking or by starting the server-level trace flag 1222 (adding-t1222 to the SQL Server service's startup parameters) and from the SQL The deadlock resource is collected in the server error log. If you have identified a participating query, investigate it on an advanced instance of SQL Server that has recovered the product data. Debugging deadlocks requires collecting large amounts of data, and because some events are fired by the system SPID, it is not possible to filter the traces to collect only the relevant data. In a busy system, this trace creates a lot of loads, so it is recommended to always work offline.

First, open two query windows in SQL Server Management Studio and collect spid with @ @SPID. These spid may be used later to help analyze the collected trace data. Then start a new SQL Server Performance Analyzer session and use the TSQL template to select the "Rpc:starting" and "sql:batchstarting" events. Add the "Locks:deadlock graph", "Locks:Lock:Acquired", "Lock:Lock:Released", and "Locks:Lock:Escalation" events to the above event, These events can all be found in the lock category. The lock event will help analyze the lock order that contributed to the deadlock state, and the deadlock graph event can provide a graphical display of the error event to assist in the analysis.

Users can choose to consider adding "sp:stmtstarting" events in case one or more of the stored procedures being debugged run a large number of statements, which can lead to deadlocks. The Eventsequence column should also be added to facilitate the analysis of data after it is collected. Displays the Completion Event selection dialog box for this activity.

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/AD/wKioL1SKnCDjXqf4AAKqXEbQJYM884.jpg "border=" 0 "height=" 536 "/>

Tip: This example shows how to run statements in the correct order and how to force a deadlock at the right time when monitoring traces in SQL Server Performance Analyzer. However, many deadlocks rely on precise timing (or error), and to regenerate deadlocks you must run each query in a closed loop, which can eventually conflict in the right way. SQL Server Management Studio also has a feature that helps users to run a query in a closed loop as many times as they want. In each query window, create a batch for the query, and then use GO to track the batch, specifying the number of times you want the query to run. The following Transact-SQL code performs 1000 mystoredprocedure stored procedures:

EXEC Mystoredprocedurego 1000

If you set up an event, you can start the trace in the Performance Analyzer. Note that filters cannot be used here because deadlock graph events can be fired by any number of system spid. Because there is no filter, users may see some system lock activity. After the trace is finished, these can be ignored or filtered out.

After this trace is started, go back to the 1th Query window (spid52 in this test) and run the following batch:

BEGIN transactionupdate Deadlock_tableset ColumnA = 3WHERE ColumnA = 1GO

Next, run the following batch in the 2nd Query window (spid53 in this test):

BEGIN transactionupdate Deadlock_tableset ColumnA = 4WHERE ColumnA = 2GO

Because the locks of the above two queries are compatible, they should be returned. They each hold a lock that is not on the deadlock_table table, go back to the first query window, start the following update, and start waiting for the session release lock for the second window:

UPDATE deadlock_tableset ColumnA = 4WHERE ColumnA = 2GO

Finally, go back to the 2nd window to run the following updates and start waiting for the first window to release the lock. Because both sessions are waiting for each other to release resources, a deadlock occurs:

UPDATE deadlock_tableset ColumnA = 3WHERE ColumnA = 1GO

After a deadlock occurs, you can stop tracing and find the generated deadlock graph event, which should look like the following.

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/AD/wKioL1SKnCLzedoxAAGIp3V0AWQ869.jpg "border=" 0 "height="/>

650) this.width=650; "title=" clip_image003 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M00/56/AF/wKiom1SKm4niqi8SAANFkqJyp9o949.jpg "border=" 0 "height=" 420 "/>

Deadlock diagram events include a large amount of data to assist in debugging the condition that occurs, including object ID, index name (if any), and hobt (hash or B-tree) IDs, which can be used to filter the lock resources, and even further to filter the lock resources with the hobt_id column in the Sys.partitions view. In addition, you can roll back the last query event that is run by each SPID before a deadlock occurs to determine the actual query related to the deadlock.

If the user needs more data for the next step of debugging, there are a number of lock information available. Readers may notice that in this case, none of the lock events adjacent to the deadlock diagram event is related to the working SPID. Even when idle, the system acquires and releases quite a few locks, so if you want to look at the chain more closely, you need to load the data into a tracking table and use the Eventsequence column to reconstruct what happened in the correct order.

As with performance analysis, SQL tracing does not actually resolve the deadlock state, but it can provide more data to help determine the cause and approach the solution.

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

SQL Server Profiler-Debug deadlock