How do I get parameter values for SQL Server query in SQL Server Profiler
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.
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.
I think you need the RPC:Completed event:
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:
SQL: BatchCompleted SQL: BatchStarting Deadlock graph Lock:Deadlock Lock:Deadlock chain
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.
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.
SP:StmtStarting DELETE FROM users WHERE id = @id
RPC:Starting exec sp_execute 12345