Take the 2-minute tour ×
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.

I want to monitor only sp's.

Does SQL Server Profiler listen to all events and then filters the desired data (e.g. sp's)?

Or rather the sql emits only Profiler pre-configured events ?

share|improve this question
1  
See the SQL Trace Architecture (technet.microsoft.com/en-us/library/ms187933(v=SQL.105).aspx). –  Thomas Stringer Dec 12 '12 at 16:05
    
@ThomasStringer I didnt understand from it if it emits all event from event source ! –  Royi Namir Dec 12 '12 at 16:23
    
After an event occurs, if the event class has been included in a trace definition... trace definition can contain (or not) event to listen. but does the event source EMITS IT at first place ? –  Royi Namir Dec 12 '12 at 16:32
add comment

1 Answer

up vote 2 down vote accepted

SQL Trace (what Profiler is built on) will capture all instances of any events that you decide to collect. So if you collect RPC:Completed events, you'll get an event for every RPC completed, trace will be capturing that event. If you don't select that event, trace won't be grabbing it. So it will only capture what it is told to.

You can (and should) then further filter the data returned by adding filters on various columns (say a database name or a duration for a completed event to capture only events with a duration of greater than a certain value, for example). In that case, all of the instances of that event are still captured, but the filter is then applied before the information is dumped to the mechanism you are using to trace.

See the difference? If you don't select the event, that event isn't captured or sent (Emitted as you are saying, if I comprehend you correctly), but if you do select the event - all instances are captured but only those that pass the filter criteria are sent (emitted).

I would also argue against using profiler but consider doing a Server Side trace to a file. Look at this blog post from Jonathan Kehayias to show the difference in performance impact between the methods.

share|improve this answer
    
If you don't select the event, that event isn't captured or sent that helped. So if i do not select to monitor sp's , sql server will not throw events for sp's right? ( i dont care about who is catching those event , i just care if sql throw events of sp's) –  Royi Namir Dec 12 '12 at 18:07
    
ps there is a difference in your answer between captured or sent ( it is not the same thing). Sql throws a ball(an sp has executed). I dont care who is catching the ball( trace) I just ask if sql trully throws the ball.(sending) even if there is no one to get the ball. –  Royi Namir Dec 12 '12 at 18:10
    
If you don't select the event, I don't believe it is thrown. No. Those aren't always firing that I know of behind the scenes, and it isn't actually a terribly important question for when you are capturing data. If it is always throwing them in some way behind the scenes it is how SQL is always running anyway.. but the answer as far as I am aware is - no.. They are only thrown if you tell SQL to by capturing that event, which is what I said in my answer. –  Mike Walsh Dec 12 '12 at 18:59
    
Thanks mike. i truly hope it doesn't throw if it wasn't told to...:-) –  Royi Namir Dec 12 '12 at 19:50
add comment

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.