Extended event session on SQL Server 2014 to captu

Question

I have an application which generates certain reports while values are entered and generate button is clicked on an application.

I know to which database this application is connecting, but don’t know which stored procedure is getting called. The database is having 100s of stored procedures.

So I need to track which stored procedure is called and what arguments are passed while clicking each button on the application.

Guys, please could you help me to create an extended event session which captures every stored procedure call with the arguments passed - in a specific database?

Thanks

JJ

Answer 1

below is the way to capture stored proc details using extended events

1.Go to management ->Extended Events --> sessions-->Right click and say new session wizard and give a new session name

2.In choose template field,dont choose default template and click next..

3.In next screen you will be presented with below screen,search for batch and select sql batch started and completed events as shown in screenshot.. and click next

Note: Extended events so much info ,if you change the channel to debug,you will be presented with many more events like spill to tempdb,cpu..

4.Next screen presents you with options to choose fields of interest,here I choose text,connection id,client name..

5.in the next screen ,select flters based on your choice,i choose databasename ,you can choose batch text as well for a single proc

6.In this screen,you have the options to choose option of storing data,i choose opion1 since ,I may need data sets collected for long period of time.

7.final screen shows you summary and gives you an option to script out what you have done so far .And also start the event session check box in finalscreen as shown in screen below

Now I ran my stored proc in ssms and when finally when I want to see data,i stopped event session .

through File ->new >merge extended events option ,I have the option to choose multiple data sets and it will be shown llike below

I can see my stored proc and fields of interest as highlighted..