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 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
    • By Andy Raybould
      Could someone assist in helping me to set up an alert when a network connection is dropped.
      This is happening at one of my client sites after changing to a new ISP. The ISP says the router/switch is fine but connections are dropping.
      I would like to be able to log when a connection drops (ideally knowing if the internet connection has dropped or just the internal network connection)
      I'm not sure a simple ping notification is good enouh in Pulseway to do this as I need to know if the connection has dropped for a few mseconds as well as larger outages.
      I guess the Event Viewer holds the answers but would appreciate it if anyone has done this before if they could share their thoughts.
      Thank you, Andy
  • Create New...