I've got a problem where I'm seeing some very strange lock waits, with the process at the top of the lock wait tree in the 'sleeping' 'AWAITING COMMAND' state. I can see the machine name and application, and I'm pretty sure it's a client-side transaction that's blocked on the client-side after locking some records that's causing the problem, but I haven't been able to find through code examination what it could be doing. I've already ruled out GC, because the same application is responding to other requests (we have a request to dump the status of all pending requests, each with a unique ID, and that returned just fine while the lock remained). By the time we are notified of this issue, request are backed up to the tune of several thousand per SQL client machine, so sifting through all these requests is very painful without writing custom code just for that.
I'd like to add some info to the SQL connection information to track which request is the one at the top, not just which machine and application (and I think this may prove useful in diagnosing future issues as well). I could append something to the application name in the connection string, but I think that would likely do bad things to the connection pooling.
I read this post: Can I Set The Value of App_Name() AFTER Login?
and this one: http://www.sqlservercentral.com/articles/T-SQL/2765/ which seem to indicate that I might be able to use
SET CONTEXT_INFO to get data into
sys.dm_exec_requests.context_info and then I would be able to use that data to identify the exact request the bad transaction is coming from.
We're not using
SET CONTEXT_INFO for anything else in the application (and the SQL servers are dedicated to this application).
Is there some reason I would not want to do request identification this way, or some better way to track requests through to the SQL layer?