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!