Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Every 15 seconds or so, the application I am trying to profile fires:

@@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF 
IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF  
elect top 1 accountId from account with (nolock)

Is this a regular SqlServer pattern?

And how I can configure the profiler so I do not see it?

IT is coming from the same process and user as the process,user for which I want to configure SQL profiling for.

Many thanks

share|improve this question

2 Answers

up vote 3 down vote accepted

Looks like some sort of heart-beat detector to see if the database is still online. Certainly it's going to return junk data since it has a TOP without an ORDER BY.

As far as not seeing it, I don't know that I'd suggest eliminating this. You want to know if something is being called every 15 seconds, especially since that query is probably causing scans on the data.

However, if you really want to, just add a filter on the TEXT column and include that text in the filter.

share|improve this answer
Thanks. When you say: "it's going to return junk data since it has a TOP without an ORDER BY". Can you elaborate? – dublintech Mar 12 '12 at 13:45
1  
Sure, you can't guarantee the order of data returned from any query unless you include an ORDER BY. So having TOP 1 will return A row, which row, you can't know. It may be the same row for a year, it might be a different row every day. – Grant Fritchey Mar 12 '12 at 18:06
up vote 5 down vote

If you want to omit those specific SQL statements, you could probably put them into the TextData NOT LIKE filter for the trace. Escape the underscore with [_].

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.