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.

• Aaron Bertrand: Please, use Extended Events, not Profiler. From this post: "SQL Server Profiler is a tool to be avoided on busy production servers, as shown by the tenfold increase in duration and significant reduction in throughput for the replay."

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..

• Adam: While I appreciate this is the better advice (and I shall be following it), it does not answer the specific question of how to do it in SQL Profiler. Thanks though.
• Shekar Kola: Thanks @Adam, I believe rpc_completed is the answer as it also available in profiler (if you still wish to use profiler)

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.

• Adam: This worked for me. I had to check Show All Columns in the Trace Properties > Events Selection in order to see the ObjectName filter. I changed SP:StmtCompleted to RPC:Completed to get my desired TextData capture of exec sprocName @someparam...etc. If you update your answer with these fixes then I will mark it as the correct answer.