A couple of basic Sql Profiler questions

Question

(Sorry for the longish question, I'll try to be concise.)

I'm running SQL Server Profiler and I'm chasing down some performance issues. I'm relatively new to what the profiler does and I've exported the traces into a table so I can run queries against the data.

One thing I've been running up against is some seemingly odd behavior doing select queries against the TextData field of the table generated by the trace export. It may have to do with the field's data type (ntext, null). I'm selecting for particular values, but getting unexpected results. For example, if I do this:

select * from [TraceAnalyzer].dbo.TraceTable

and I'm interested in values like this:

exec [Sproc_of_interest] @parm1=992

I'd do a query like this:

select * from [TraceAnalyzer].dbo.TraceTable
where TextData like '%exec [Sproc_of_interest] @parm1=%'

but the return result is empty.

Also, if I do a query like:

select * from [TraceAnalyzer].dbo.TraceTable
where TextData like '%exec [Sproc_of_interest]%'

I get unexpected TextData values like exec sp_reset_connection

Would the square brackets in the criteria be messing things up? I've tried omitting them, but that just excludes everything. I'm not aware of escape characters in SQL select queries, but when I copy/paste the value from one of the offending records, the pasted value does not appear to contain anything that would meet the original query's criteria.

Any insights would be greatly appreciated. Thanks.

Roice

[Sproc_of_interest] is interpreted as a set of values to match in the pattern syntax. As you are using a leading wildcard anyway it is probably easier just to use CHARINDEX rather than escaping all special characters. – Batiste

Thanks Martin. That seems to be getting me what I need. Repost it as an answer and I give you the accepted mark. :-) – Roice

Answer 1

[Sproc_of_interest] in the pattern syntax is interpreted as matching one character that is in the set S,p,r,o,c,_,o,f,_,i,n,t,e,r,e,s,t.

Three possible ways of solving this are below.

1) Escape [ with square brackets

LIKE '%exec [[]Sproc_of_interest] @parm1=%'

2) Use an escape character

LIKE 'exec \[Sproc_of_interest] @parm1=' ESCAPE '\'

3) Use CHARINDEX instead of escaping anything

WHERE CHARINDEX('exec [Sproc_of_interest] @parm1=' , TextData) > 0

Batiste

You might want to clarify what "in the range c_o" means: the underscore is a wildcard for a single character, not for a range. That would be c-o. – Jankowski

@Jankowski - Good point, corrected I was indeed thinking of - – Batiste