SQL Server 2014 上的扩展事件会话以捕获存储过程调用和传递的参数 [英] Extended event session on SQL Server 2014 to capture stored procedure calls and arguments passed

问题描述

我有一个应用程序，它会在输入值并在应用程序上单击生成按钮时生成某些报告.

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?

谢谢

JJ

推荐答案

下面是使用扩展事件捕获存储过程细节的方法

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

1.转到管理->扩展事件-->会话-->右键单击并说新会话向导并指定新会话名称

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

2.在选择模板字段中，不要选择默认模板，点击下一步..

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

3.在下一个屏幕中，您将看到以下屏幕，搜索批处理并选择 sql 批处理启动和完成的事件，如屏幕截图所示.. 然后单击下一步

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

注意:扩展事件这么多信息，如果您将频道更改为调试，您将看到更多事件，例如溢出到 tempdb、cpu..

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.下一个屏幕为您提供选择感兴趣的字段的选项，这里我选择文本、连接 ID、客户名称..

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

5.在下一个屏幕中，根据您的选择选择过滤器，我选择数据库名称，您也可以为单个进程选择批处理文本

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.在此屏幕中，您可以选择存储数据的选项，我选择 opion1 因为，我可能需要长时间收集数据集.

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 显示您的摘要，并为您提供一个选项来编写您到目前为止所做的事情.并且在 finalscreen 中启动事件会话复选框，如下面的屏幕所示

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

现在我在 ssms 中运行我的存储过程，最后当我想查看数据时，我停止了事件会话.

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

这篇关于SQL Server 2014 上的扩展事件会话以捕获存储过程调用和传递的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！