In my previous post I outlined the steps required to Capture The Parameters of A Stored Procedure Call Using SQL Server Profiler. Though some consider this to be an outdated tool within SQL Server, I find it can be quite useful in certain circumstances. In the example covered in the article I had an ad hoc request to monitor a specific stored procedure in order to capture the parameters being passed each time the procedure was called. Since the trace only needed to run for about 5 minutes, leveraging SQL Profiler was an easy option. However, as noted at the end of the article, if long term monitoring is necessary, Extended Events is a better solution.
The steps outlined below will guide you through the process of creating an Extended Event to capture the parameters being passed to a specific stored procedure. In an effort to better illustrate the differences between SQL Server Profiler and Extended Events I will create a nearly identical solution to the one outlined in the aforementioned article.
In order to create an Extended Event you will first need to connect to the applicable SQL Server.
Once connected expand the Management node as shown below. Next expand the Extended Events node, right-click and select New Session…
Once the New Session window is displayed you’ll first need to provide a Session name. In the example below I am calling it USP_POINVOIC_INS Parameter Capture. I have also checked the boxes next to Start the event session immediately after session creation as well as Watch live data on screen as it is captured. Though this step is not necessary when initially configuring the event session, I find it useful as once the event has been fully configured you can immediately begin monitoring.
Once the General page has been configured, select the Events page from the left pane.
Here you will specify the Event library which will be used. In many cases when configuring an Extended Event you may reference multiple libraries. However, in this case only a single library needs to be added.
In the Event library text box, search for rpc completed. Once found, select it and click the right arrow to move it over to the Selected events box.
Once the event has been moved to the Selected events window, either double-click the event or click the Configure button directly above the selected event.
Select the Filter(Predicate) tab under Event configuration options.
Under Field select object name. This will reference the database object, a stored procedure, that you want to have the event monitor. Make sure the Operator is set to =. Finally, under Value, enter the name of the stored procedure.
Once the event filter has been configured click OK to create the event. If you selected the check boxes on the initial screen the event should start and the Live Data view should appear as a SQL Management Studio pane.
The following screenshot shows the Live Data view after having the Extended Event run for about 10 minutes. As you can see, multiple events were captured. If you click on any of the events you can then review the details of each event.
Referencing the Statement field towards the bottom of the event Details will show you the full procedure execution call including the parameters that were passed when the procedure executed. Additionally, if you right-click any of the fields in the Details view you can add that column to the Live Data feed for easier viewing.