SQL Trace challenge: a simple requirement

Hi Linchi

Must be everything in ONE trace?

Hi Linchi

Must be everything in ONE trace?

PingBack from http://topsy.com/trackback?utm_source=pingback&utm_campaign=L2&url=http://sqlblog.com/blogs/linchi_shea/archive/2010/04/29/sql-trace-challenge-a-simple-requirement.aspx

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

Definitely and interesting problem.

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.

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

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?

> 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.

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

---

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.

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.

This post was mentioned on Twitter by FAQShop: [SQLblog.com] SQL Trace challenge: a simple requirement: SQL Trace (or SQL Profiler) is no doubt an excellent tool... http://bit.ly/dp1V8x

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.

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)

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).

@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.

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.

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?