Jump to content
Jacob Ipsen

SQL sys.dm_tran_locks request

Recommended Posts


we are chasing an problem in our SQL setup and in that quest we come across some heavy requests called often by the pulseway client
the querylooks llike this

This comes in at 1 place and 3 and 4 place in top most CPU consumin queries
We are running an "large" SQL db (600GB) with 850 users online... so a lot queries are running alle the time...


Any ideas ?  

Use [master];

SELECT TOP 100 L.request_session_id AS SPID,

       DB_NAME(L.resource_database_id) AS DatabaseName,

       O.Name AS LockedObjectName,

       P.object_id AS LockedObjectId,

       L.resource_type AS LockedResource,

       L.request_mode AS LockType,

       ST.text AS SqlStatementText,

       ES.login_name AS LoginName,

       ES.host_name AS HostName,

       TST.is_user_transaction as IsUserTransaction,

       AT.name as TransactionName,

       CN.auth_scheme as AuthenticationMethod

FROM sys.dm_tran_locks L

JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id

JOIN sys.objects O ON O.object_id = P.object_id

JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id

JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id

JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id

JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id

CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

WHERE resource_database_id = db_id()

ORDER BY L.request_session_id;

Share this post

Link to post
Share on other sites

Hi Jacob,

That query is used by Pulseway to detect deadlocks. If you find it impairing the performance of your SQL Server then please turn off the notification check from the Pulseway Manager app in the Notifications -> Server Modules -> SQL Server.


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By NadeCatcher
      I've been trying to turn on my computer when I'm away from the house using both mobile data and another WiFi connection. When I'm on the same network the wake up command works but if I'm using mobile data I'll get the message "Wake up command failed" and when I'm on another network it says "wake up command sent" but nothing happens. I have the necessary port forwarded (UDP 9) and through the Pulseway android app port monitor I can see it works. Any help would be greatly appreciated.
    • By General_Snuss
      I changed phones recently and the new app says:
      'This device is pending approval. Login to the WebApp and approve it from the account devices page.'
      If I got to https://my.pulseway.com/main/account/devices there is no device to approve.
      Am I doing something wrong?
    • By Brandan
      Here is a Batch script to empty the SoftwareDistribution Folder.  It stops any installer processes in the background, temporarily disables the Windows Update Service, deletes everything in the Software Distribution folder then sets the Windows update service back to automatic and starts the service.
      @echo off
      CD C:\
      taskkill /F /im setup.exe
      taskkill /F /im ose00000.exe
      taskkill /F /im msiexec.exe
      sc config wuauserv start= disabled
      net stop wuauserv
      PowerShell.exe Remove-Item C:\Windows\SoftwareDistribution\* -Force -Recurse
      sc config wuauserv start= demand
      net start wuauserv
    • By Louwrens
      Please expand the list of programs on 3rd Party Patch management.
      Some suggestions:
      Adobe Flash
      Amazon Corretto (Since Oracle Java is not free anymore)
      Google Gsync (GSuite)
      K-lite Codec
      WhatsApp Desktop Client
  • Create New...