Once I had to identify the list of only the stored procedures executed against a database on production environment during certain time period in a day. There are several ways (like using dmv sys.dm_exec_query_stats) to identify the queries executed. However, my favorite tool is SQL Server Profiler. Profiler gives me the exact time of execution along with the duration for each instance. Let’s see how to use the profiler trace to trap only the stored procedures executed against a specific database.
- From the profiler trace window, go to menu Files > Properties.
- In the Trace Properties window, go to Events Selections tab.
- From the left Events panel, expand the node Stored Procedures and select the events RPC:Completed and SP:Completed. (NOTE: Use RPC:Starting if you want to capture the procedure executed from application or use SP:Starting to capture the procedure executed from inside another stored procedure or from a batch. Or of you are not sure where the procedure is executed from, then use both of them as specified in the step. The event SP:Completed and RPC:Starting will capture the stored procedures successfully completed execution. This event will give you the execution start time, end time and the duration of execution)
If you want to capture the stored procedures executed against all the databases in a SQL Server, then skip the below steps and press Run button to start the trace. If you want to capture the stored procedures executed against a specific database, then go ahead and follow the steps below.
- Select the check box Show all columns.
- Press the Columns Filters… button below.
- In the Filter pop-up window, from the left panel select DatabaseName.
- From the right panel, under Like tree node, enter the database name and press OK.
- Then, press Run button to start the trace.
You will see the trace is filtering and showing only the stored procedure executed against a databases.
