Which sessionid has the most locks in a SQL Server instance

Quick post today. Was playing around with something on Friday night, thinking how to answer this question quickly whilst looking a live performance problem. I have a huge selection of ready to go DMV based queries, aimed at targetting all types of performance problems, but this was one I didn't have. The answer of course is beautifully simple through the magic of DMVs. Do you remember when these didn't exist? Sometimes it's easy to forget :-) The answer and a good one to have in your troubleshooting code toolbox:

select

request_session_id, count(*)

from

sys.dm_tran_locks

group

by request_session_id

Comments

• Anonymous
  February 02, 2009
  PingBack from http://www.anith.com/?p=4610

• Anonymous
  February 11, 2009
  Last week I wrote about the initial diagnosis of this problem, and now here's the follow up. So if you