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 ?
Sql-server – SQL Server Profiler behavior
profilersql serversql-server-2008
Related Question
- Sql-server – How to restrict SQL server profiler to show the symmetric key password while executing query from C# code
- Sql-server – meaning of ## in SQL Server Profiler trace data
- SQL Profiler – Capturing sp_executesql Statements
- Sql-server – Is this considered a “light load” trace to run on SQL Server Profiler
- SQL Profiler – Can SQL Profiler Filter Data Columns for One Event
- Sql-server – How to use SQL Profiler to capture a single stored procedure
Best Answer
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.