如何在 SQL Server Profiler 中获取 SQL Server 查询的参数值
我正在尝试分析 SQL Server 2008 探查器中的死锁。我知道如何找到有问题的 SQL 查询，但收集的查询不包含参数值。
换句话说，我可以看到类似这样的内容：
DELETE FROM users WHERE id = @id
但我想看到的是：
DELETE FROM users WHERE id = 12345
我想我需要在探查器中收集一些其他事件或列，但我不知道是哪些。我目前正在使用“TSQL_LOCKS”模板。
任何提示将不胜感激。
谢谢，
阿德里安
免责声明：我以前问过类似的问题，但我想它太具体了，这就是为什么我没有得到回复。我正在开始另一次尝试。
I'm trying to analyze a deadlock in SQL Server 2008 profiler. I know how to find the offending sql queries, but the collected queries do not include parameter values.
I other words I can see something like this:
DELETE FROM users WHERE id = @id
But what I would like to see is this:
DELETE FROM users WHERE id = 12345
I guess there are some additional events or Columns I need to collect in the profiler, but I don't know which. I am currently using the "TSQL_LOCKS" template.
Any hints would be greatly appreciated.
Thanks,
Adrian
Disclaimer: I've asked a similar question before, but I guess it was too specific, which is why I got no replies. I'm starting another attempt with this one.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我认为您需要 RPC:Completed 事件：
http://msdn.microsoft.com /en-us/library/ms175543.aspx
I think you need the RPC:Completed event:
http://msdn.microsoft.com/en-us/library/ms175543.aspx
Profiler 将包含 RPC:Completed/RPC：启动事件。但您已经收到回复告诉您这一点。
我想补充的是，分析死锁图几乎不需要知道参数运行时值。首先，因为如果“用户”涉及死锁，则死锁图本身将泄露冲突的@id（如果冲突是在某个键上）。其次，更重要的是，对于死锁场景来说，所涉及的确切密钥无关。不像死锁发生，因为删除用户 id 123 但在删除用户 321 时不会发生。
如果您决定首先询问 SO，我认为最好是发布实际的死锁图并让社区看看吧。这里有很多内容可以仅通过死锁图 XML 来回答相当多的问题。
The Profiler will contain the parameter values in the RPC:Completed/RPC:Starting events. But you already got replies telling you this.
What I want to add is that is very little need to know the parameter run-time values in order to analyze a deadlock graph. First, because if 'users' is involved in the deadlock, the deadlock graph itself will give away what @id is the conflict, if the conflict is on a key. Second, more importantly, for a deadlock scenario is irrelevant the exact keys that are involved. Is not like a deadlock happens because one deletes user with id 123 but will not happen when it deletes user 321.
If you decided to ask on SO in the first place, I think the best would be to post the actual deadlock graph and let the community have a look at it. There are many here that can answer quite a few questions just from the deadlock graph XML.
在选中所有复选框的情况下，使用以下事件启动跟踪：
发生死锁后，停止跟踪，然后单击死锁图事件类。
这应该可以让您很好地了解出了什么问题。
Start a trace with the following events having all checkboxes checked:
After the deadlock occurs, stop the trace, then click on the deadlock graph event class.
This should give you a good idea of what's going wrong.
如果您正在使用存储过程（看起来像您使用的那样）或 Hibernate/NHibernate，您可能需要打开存储过程启动事件 (SP:StmtStarting) 和 RPC:Starting 事件。这将在查询后在其自己的行中显示参数。
类似于：
SP:StmtStarting DELETE FROM users WHERE id = @id
RPC:Starting exec sp_execute 12345
If you're using a stored procedure (which it looks like you are) or Hibernate/NHibernate you might need to turn on the Stored Procedures starting event (SP:StmtStarting) and RPC:Starting event. This will show the parameters in it's own line after the query.
Something like:
SP:StmtStarting DELETE FROM users WHERE id = @id
RPC:Starting exec sp_execute 12345