0

I'm running this query

select sessions.session_id from
   sys.dm_tran_locks locks inner join  sys.dm_exec_sessions sessions
      on locks.request_session_id=sessions.session_id

and it consistently outputs around four thousand rows. Yet when I run

select session_id from sys.dm_exec_requests

it outputs no more than several rows. And if I try this

select sessions.session_id, requests.session_id
    from sys.dm_tran_locks locks inner join sys.dm_exec_sessions sessions
       on locks.request_session_id=sessions.session_id
       left outer join sys.dm_exec_requests requests
         on sessions.session_id=requests.session_id
order by requests.session_id

I see that none of sessions.session_id are null but notably more than half of requests.session_id are null.

What does that mean? Where do those rows in sys.dm_tran_locks belong than I cannot find a matching row in sys.dm_exec_requests?

4
  • How many records after applying extra filter of locks.resource_database_id > 4 ? Nov 11, 2014 at 15:16
  • @aasim.abdullah: Same amount as without the filer (I compute count(*) and sum(case when locks.resource_database_id > 4 then 1 else 0 end) in the same query and they are equal).
    – sharptooth
    Nov 11, 2014 at 15:26
  • You can hold locks without actively running a query... is your app using an ORM or roll-your-own optimistic concurrency? Nov 11, 2014 at 15:34
  • @AaronBertrand: Some queries originate from Ling-to-Sql, others are being run using SqlCommand class. I'm not sure but it looks like there're no tweaks to concurrency beyond the default settings.
    – sharptooth
    Nov 11, 2014 at 15:36

1 Answer 1

1

Two more additional filters are necessary for your first query.

1- You need to execute query for user databases only i.e. resource_database_id > 4
and
2- Session status "sleeping" mean, session is currently running no request, so exclude such sessions from your result. i.e. [sessions].status <>'Sleeping'

select sessions.session_id,* from
   sys.dm_tran_locks locks inner join  sys.dm_exec_sessions sessions
      on locks.request_session_id=sessions.session_id
WHERE locks.resource_database_id > 4  
AND [sessions].status  <>'Sleeping' 
2
  • Emm... How is it possible that a session is sleeping and there's a row in locks for that session?
    – sharptooth
    Nov 11, 2014 at 15:34
  • 1
    Yes, If the client has an open transaction and the client did not submit a commit or rollback command the state is sleeping / awaiting command. see this blogs.msdn.com/b/psssql/archive/2008/04/21/… Nov 11, 2014 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.