How can I use SQL Profiler to capture a single stored procedure

Question

Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this procedure so that I can load test it using realistic parameter data.

I've tried the following in a new SQL Profiler trace:

Events Selection > Column Filters > Text Data > LIKE: 'exec sprocName%'
Events Selection > Column Filters > Text Data > LIKE: 'exec sprocName'

Neither of above configurations capture my procedure. I've also tried to remove the procedure name with the following configs:

Events Selection > Column Filters > Text Data > LIKE: 'exec%'
Events Selection > Column Filters > Text Data > LIKE: 'exec'

Above configs do not capture any procedures.

Finally, I tried to execute the same profile without any column filters and I can confirm that it captures all SQL queries sent to SQL Server.

I've considered a workaround of capturing ALL stored procedure execs and post-filtering to my sproc of interest, using SQL queries or Excel Power Query. However, the high frequency of sproc executions in the environment I want to model, make this unfeasible.

Answer 1

I would suggest to use Extended Events instead of Profiler as the Extended Events has less overhead and more events to capture compare to Profiler.

In your case, create a new session in Extended Events by selecting rpc_completed event. For detailed steps..

Answer 2

In your Trace Properties, I would recommend using the Tuning template.

Under the Event Selections tab in the Trace Properties, choose the RPC:Completed event under Stored Procedures.

Within the event filters tab, select Show All Columns.

Using the Column Filters:

1. DatabaseID Equals the DB_ID() of the database where the stored procedure exists.
2. ObjectName Like the exact name of your stored procedure without the schema.
3. ObjectType Equals 8272

This should capture what you are looking for.

More info regarding the ObjectType filter can be found here.

More information regarding the RPC:Completed event class can be found here.

Depending on what version of SQL Server you are using, I would also consider taking a look at Extended Events. SQL Profiler will be removed in future versions of SQL Server.