Contact  |  Site Map  |  Privacy      Call for more info
 

 

SQL Deadlock Detector allows you to easily filter and identify problematic locks and deadlocks that occurred during a specific period of time.

 

Step 1 of 3: By default, when you first connect to a monitored SQL Server, the event monitor displays locks and deadlocks that occurred and were recorded during the current day.

 

Step 2 of 3: Click on the Date Filter toolbar button (labeled “Today”) at the top of the event monitor then select Custom from the dropdown list.

 

Step 3 of 3:To identify problematic locks and deadlocks that occurred during a specific period of time, in the Date Filter dialog, specify the starting and ending dates (and times) you wish to display recorded events for.

 

 

This is useful when you need to determine the total number and the type of events (locks, deadlocks or both) encountered at any given day or time. It also helps you to analyze locking and deadlock problem behavior and patterns.

For example, if your database experiences heavy load and activity on Mondays, Wednesdays and Fridays, you can easily identify and examine locks and deadlocks that occurred in those days.

 

7/23/2007, Monday

 

 

Event Type

Date and Time

No. of Blocked Processes

Max. Wait Time (ms)

Deadlock

7/23/2007 3:59:34 PM

2

 

Lock

7/23/2007 3:59:51 PM

2

6021

Lock

7/23/2007 4:00:15 PM

3

12604

Deadlock

7/23/2007 4:00:36 PM

2

 

 

2 Locks, 2 Deadlocks

 

7/25/2007, Wednesday

 

 

Event Type

Date and Time

No. of Blocked Processes

Max. Wait Time (ms)

Deadlock

7/25/2007 6:23:20 PM

2

 

Lock

7/25/2007 6:23:50 PM

3

12105

 

1 Lock, 1 Deadlock

 

 

7/27/2007, Friday

 

 

 

Event Type

Date and Time

No. of Blocked Processes

Max. Wait Time (ms)

Lock

7/27/2007 3:54:46 PM

3

12948

Lock

7/27/2007 3:54:57 PM

2

6084

Lock

7/27/2007 3:55:59 PM

3

5350

Deadlock

7/27/2007 3:56:02 PM

3

 

Lock

7/27/2007 3:56:23 PM

3

5818

Deadlock

7/27/2007 3:56:27 PM

3

 

Lock

7/27/2007 4:21:56 PM

2

6099

Lock

7/27/2007 4:22:13 PM

3

12979

 

6 Locks, 2 Deadlocks

In the sample results, we can see some interesting information. First, you will notice that all locks and deadlocks occur in the late afternoon, sometime between 4:00 PM and 6:30 PM. This indicates a constant pattern of activity during that time (e.g. possibly end-of-day report generation by different departments). One way to mitigate the problem without modifying your code is to separately schedule these activities.

Second, you will notice that the series of locks and deadlocks occur at times that are very close to each other. This may indicate high contention by different processes in separate events over common objects (tables or views). Query optimization for contending code and index tuning for contended objects will help alleviate locking and deadlock problems.