THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did you know? -- Deadlock Graph Event Isn't Generated When Filtering on DatabaseID

As many of you know, I am a big fan of SQL Server Profiler and all the wonderful things you can do with tracing. I was very excited about some of the enhancements to the SQL Server 2005 tracing functionality. However, over the last couple of days, I discovered that two of my favorite new features do not play nicely together. Let me explain…

 

When creating a trace you have the option of filtering out data values that aren’t interesting to you. This is a good thing, as trace files can get very large on a busy server. A common filter is on the duration data column, to limit the rows returned to those that have a duration larger than some cutoff value, for example, 2 seconds. This filter would indicate that you’re not interested in any events that occur in less than 2 seconds. You should be aware that not all events return information in every data column. The duration data for example, is NOT returned for events such as TSQL:StmtStarting and SP:Starting. There is no duration when something starts, so the duration data column shows a NULL. In SQL Server 2000, if you filtered on duration, NULLs would never be excluded and in addition to seeing all events with a duration greater than 2 seconds, you would also see all events that had no duration listed. This behavior made it very difficult to keep trace files manageable on very busy systems.

 

SQL Server 2005 enhanced the trace functionality by allowing you to specify that you do NOT want to include NULLs in the captured trace. For each filter you define, you can check a box that indicates that NULL values are to be excluded. This is a wonderful enhancement and it makes many tracing situations much more manageable.

 

The second enhancement is the ability of the Profiler to capture deadlock information using an event called Deadlock Graph. When a deadlock occurs, the Deadlock Graph event will display a representation of the deadlock in graphical format, showing which resources are locked by what processes, and what resources have been requested but not granted. It will also show you what each process involved in the deadlock was doing at the time of the deadlock. This is also a wonderful feature.

 

However, a problem occurs if you are filtering on database ID. The Deadlock Graph event does not populate the DatabaseID or DatabaseName data columns, although other deadlock events will populate those columns. So if you’re filtering on DatabaseID to try to keep your trace size manageable, the Deadlock Graph events will not be captured. You have to remove the filter on DatabaseID to see these events, but that of course means that you will get all the other trace data from all databases.

 

SQL Server MVP Erland Sommarskog also noticed that if you include filter for Duration or NTUsername, neither of which is populated by DeadlockGraph event, the graph WILL be included (as long you don't filter for database ID as well.) So obviously, the explanation is not as simple as saying you don’t get deadlock graphs because the DatabaseID column is not populated. We decided this behavior was weird enough to be considered a bug, and Erland posted it on Connect, and it was acknowledged as a bug by Microsoft.

 

While waiting for Microsoft to fix this, one workaround would be to have two separate traces, one that captures ONLY Deadlock Graph events and one that captures everything else with the appropriate filters. 

 

So be careful!

 

-- Kalen

 

 

Published Tuesday, November 28, 2006 9:48 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gavan said:

Thanks for the blog post.

This is exactly the problem I was having. Trying to trace a deadlock in SQL Profiler, filtered on DBID, and wondering where my deadlock log was!

February 8, 2008 10:47 PM
 

tom said:

Thanks Kalen, I was lost!

March 14, 2008 9:04 AM
 

dan said:

Thanks! I found your article when I was not seeing deadlock graphs using dbname filter!

August 12, 2008 8:45 AM
 

Greg said:

Thanks too for the info. Solved my problem.

November 12, 2008 10:08 PM
 

Iddo said:

Thanks! I googled this post exactly at the right time

December 4, 2008 8:14 AM
 

Noel said:

Thanks very much.

solved my problem.

February 6, 2009 12:41 PM
 

Keith said:

Thanks a lot Kalen! This post was super-helpful for us!

February 11, 2009 3:59 PM
 

Stan Segers said:

Hi Kalen,

Do you happen to know under which FeecbackID the bug is filed, or when/if it was solved?

February 27, 2009 6:37 AM
 

Kalen Delaney said:

February 28, 2009 12:30 PM
 

Debbie said:

Does anyone know if this was this fixed in ss2005 SP3? We are seeing this issue with SP2 but I am not encountering it with SP3. I followed the link and  they said it would be fixed in 2008. But I wonder if this was the plan before Sp3 was planned?

April 22, 2009 1:17 PM
 

Kalen Delaney : Did You Know? How to tell if a bug has been fixed? said:

April 22, 2009 2:31 PM
 

Kalen Delaney : Did You Know? How to tell if a bug has been fixed? said:

April 22, 2009 2:34 PM
 

Debbie said:

Thank you!

April 22, 2009 4:11 PM
 

Geoffrey said:

Hi Kalen,

I've a very strange deadlock problem. I'm having a table with a clustered index (identity) and some other columns, where another column contains a grouping (so multiple PK are contained within one groupid).

Example:

CREATE TABLE [dbo].[TestLock](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[GroupID] [bigint] NOT NULL,

[StatusID] [smallint] NOT NULL CONSTRAINT [DF_TestLock_StatusID]  DEFAULT ((0)),

CONSTRAINT [PK_TestLock] PRIMARY KEY CLUSTERED

([ID] ASC)

) ON [PRIMARY]

Session1:

begin transaction

insert into testlock(groupid, statusid) values (1,0)

Session2:

begin transaction

insert into testlock(groupid, statusid) values (2,0)

Then in Session1:

update testlock set statusid = 90 where groupid = 1

-> Session1 is waiting on a U KEY lock on the row inserted by session2. I do not understand this. Why is this?

If I commit first Session2, then session1 does not take an U key lock. It's just like session1 locks all records that will be in the same page and not yet committed? (we're using sql 2005 sp 4034, read_committed_with_snapshot isolation model)

Your input should be fantastic.

Tx

Geoffrey

December 8, 2009 10:55 AM
 

ilann said:

this was very helpful!

thank you for posting that

December 22, 2009 9:59 AM
 

Nate said:

Thanks for this info! It is extremely helpful!

December 22, 2009 4:28 PM
 

Ranjith said:

Thanks for your post ....was really useful !!!

December 8, 2011 8:23 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement