THE SQL Server Blog Spot on the Web

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

Linchi Shea

Checking out SQL Server via empirical data points

SQL Trace challenge: a simple requirement

SQL Trace (or SQL Profiler) is no doubt an excellent tool. But its filtering capability is rather primitive, and is very poorly documented. Here is a request that is simple and seems to be rather reasonable.

Create a trace to filter for the following:

1. All the update/delete statements, and
2. All the select/insert statements whose CPU column value is greater than 1000 or whose Duration value is greater than 1000

Now, I'm having a tough time creating a trace to meet this simple requirement. Perhaps, I'm missing something obvious, and I'd be very happy if you prove that's the case.

Published Thursday, April 29, 2010 12:48 AM by Linchi Shea
Filed under: ,

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

 

Uri Dimant said:

Hi Linchi

Must be everything in ONE trace?

April 29, 2010 12:21 AM
 

Uri Dimant said:

Hi Linchi

Must be everything in ONE trace?

April 29, 2010 12:21 AM
 

Linchi Shea said:

Hi Uri;

However you can produce it. But you can't trace everything and filter later offline. That would defeat the purpose of filtering.

April 29, 2010 7:12 AM
 

Jack Corbett said:

Are you using stored procedures or are all the SQL Statements ad hoc?

Definitely and interesting problem.

April 29, 2010 7:57 AM
 

Chuck Rummel said:

Hi Linchi,

If I understand your point correctly it is that Profiler does not handle OR conditions very well, especially using mixed criteria, with which I definitely agree.  For lack of a better way of doing it, to satisfy your requirements I would likely start with 3 traces, one for upd/del, one for cpu>1000, one for duration>1000, perhaps this is what Uri was hinting at.  If someone has a better way to accomplish this I'd love to learn how too.

April 29, 2010 8:51 AM
 

Rob Volk said:

I can't figure out a way to use sp_trace_setfilter to do this, but this article may help:

http://blogs.msdn.com/psssql/archive/2009/01/08/filtering-trace-data-without-loading-in-profiler-or-database-table.aspx

I don't think it can hook into an existing trace however, it would have to be instantiated via .Net.  There's another article with some more information:

http://msdn.microsoft.com/en-us/library/ms345134(SQL.90).aspx

April 29, 2010 8:57 AM
 

Linchi Shea said:

The problem with multiple traces is that you would end up with a lot of duplicates. So for instance, if a statement is UPDATE and its CPU cost is greater than 1000, you would have recorded it twice or three times.

April 29, 2010 8:59 AM
 

Linchi Shea said:

Jack;

This is totally open and all cases should be considered.

April 29, 2010 9:01 AM
 

Linchi Shea said:

Uri;

Must you say it three times? :-)

April 29, 2010 9:03 AM
 

Adam Machanic said:

Linchi,

I have some ideas and I think this is possible from a filter POV with two traces, and maybe even with a single trace (although setting it up is totally unintuitive). But the problem is identifying the "type" of the operation. How are you going to figure out whether you're dealing with an update, insert, delete, etc?

April 29, 2010 9:51 AM
 

Linchi Shea said:

> How are you going to figure out whether you're dealing with an update, insert, delete, etc?

Adam;

That is part of the problem. Even if we just want to distinguish between read-only queries and DMLs, you can't use whether Writes > 0 to decide because Writes are for physical writes. It's difficult to use TextData as well because you would need a full parser and that would not be practical.

So, I'd be happy if we make it even simpler, and just get the AND's and OR's reliably correct.

April 29, 2010 10:12 AM
 

Adam Machanic said:

Well, forget that. My idea was to do two or more groups logically OR'd, since SQL Trace puts implicit "parens" around filters on the same column. But that's not doable, because apparently you're not allowed to separate filters on the same column ID... Here's what I was thinking:

---

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

declare @logical_op int

declare @comparison_op int

set @logical_op = 0

set @comparison_op = 6

exec sp_trace_setfilter @TraceID, 1, @logical_op, @comparison_op, N'%this_is_a_select%'

set @logical_op = 1

set @comparison_op = 6

exec sp_trace_setfilter @TraceID, 1, @logical_op, @comparison_op, N'%this_is_an_insert%'

set @logical_op = 0

set @comparison_op = 2

set @bigintfilter = 1000

exec sp_trace_setfilter @TraceID, 13, @logical_op, @comparison_op, @bigintfilter

set @logical_op = 1

set @comparison_op = 6

exec sp_trace_setfilter @TraceID, 1, @logical_op, @comparison_op, N'%this_is_a_select%'

set @logical_op = 1

set @comparison_op = 6

exec sp_trace_setfilter @TraceID, 1, @logical_op, @comparison_op, N'%this_is_an_insert%'

set @logical_op = 0

set @comparison_op = 2

set @bigintfilter = 1000

exec sp_trace_setfilter @TraceID, 18, @logical_op, @comparison_op, @bigintfilter

---

April 29, 2010 10:12 AM
 

Jack Corbett said:

I think I am getting close.  In meetings all day so I can't do much with it during the day.  I have the Update, Delete, and the Select with the duration and cpu filters, but having trouble getting the Insert to use the duration and cpu filters.

Here's what the filters I have look like:

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%update%' -- first one filters or

exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%delete%' -- this is anded with first one

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%select%' -- now another OR

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%insert%' -- And'ed to the select

exec sp_trace_setfilter @TraceID, 13, 1, 4, @bigintfilter -- Duration - and'ed to the select/insert

exec sp_trace_setfilter @TraceID, 18, 1, 4, @intfilter -- CPU - and'ed to the select/insert/duration

I think I'm close to what Adam is doing.

April 29, 2010 10:17 AM
 

Adam Machanic said:

Two more comments:

A) Writing this by hand reminded me just how much I hate SQL Trace. These stored procedures are a nightmare (see all of the variables I had to define to deal with the type issues)

B) This kind of filter is much more possible in Extended Events, but...

C) ... the ability to tell whether an op is an INSERT, UPDATE, etc, is hidden in the SQL Audit Xevent objects. Might have to file a "suggestion" on that sometime soon.

April 29, 2010 10:22 AM
 

noeldr said:

BTW

'%update%' should have an space at the end always

and the like operator should include

both cases 'update %' and '% update %'  

same goes for "select" and "insert" ... I have done this in the past and you get a somewhat good approach by doing that.

April 29, 2010 11:56 AM
 

Linchi Shea said:

noeldr;

Are you talking about filtering on the TextData column? What happens with the following SQL statement?

UPDATE abc

  SET c = 2

WHERE D IN (SELECT c FROM xyz)

April 29, 2010 2:27 PM
 

Adam Machanic said:

Also, the space isn't a guarantee of anything:

UPDATE

abc

SET c = 2

... okay, so maybe that's not a common format but it could happen and slip right through the filter. Point is, text parsing is a real PITA. Even the Data Dude parser doesn't get it 100% right (although it does get VERY close).

April 29, 2010 2:48 PM
 

noeldr said:

@Linchi, Yes I am talking about parsing the TextData column

Your statement will be trapped by the 'update %' as an "update" statement!

@Adam,

True you can always craft something uncommon I was simply pointing out that without the space columns named with "update", "insert" or "delete" in them can simply spoil the parsing results.

Hopefully, we all agree that these are just attempts to workaround limitations (significant in my opinion) in the tool.

April 30, 2010 4:24 PM
 

jchang said:

I doubt this is the answer you are looking for, but I have resorted to writing my own C# SMO program to do custom filtering. I think Writes > 0 is an indication of Insert, Update or Delete, and possibly temp table as well.

May 3, 2010 12:20 PM
 

Linchi Shea said:

Joe;

"Writes > 0" is not a reliable indicator of DMLs because the Writes values are for physcial writes only, and you can have many DMLs with zero Writes.

noeldr;

Okay, you'll count that as an UPDATE. But would you also double count it as a SELECT?

May 4, 2010 10:30 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

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