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 Parallax Abstraction
      Hey all.
      I've enabled 2FA on my Pulseway account but I'm having an issue with it constantly prompting for 2FA every time I try to use Remote Control on a system I manage from the Pulseway Dashboard application. I have the setting enabled in the WebApp to refuce the frequency of the prompts but it doesn't seem to have made much of a difference. I seem to be getting prompted every time. I'd honestly rather get prompted once when I launch the Dashboard application then every single time I want to remote to a system.
      Is there a way to stop these constant 2FA prompts without disabling it entirely? Thanks!
    • By Parallax Abstraction
      Remote Control really needs the ability to both trigger a reboot and to automatically reconnect when the system you're connected to completes the restart. Having it immediately disconnect when a reboot is triggered (usually if it's running as a service, it should be able to hold the session open for things like the first phase of installing Windows updates) and having to manually try over and over again to see if the machine has completed its reboot is really frustrating when you're trying to solve an issue remotely that requires a lot of reboots or even when you're just trying to do a number of updates to a system.
    • By AC_Martin_J
      I'm currently running Pulseway OnPrem (7.0.0 build 120 release 283), and I'm using Pulseway Remote Control (6.6.3). 
      The issue I have experienced is that the remote control notification doesn't always show up on the client when I attempt to remote control a session. We have 150+ computers in our environment and sometimes it works fine, and sometimes it doesn't, and the different behavior can even occur on the same machine from time to time.
      Pulseway is configured to automatically allow the remote session if no one accept (or deny) the request within a minute, and I have noticed that even if the notification doesn't show up, the connection will still be established when the timer runs out. 
      To make things clearer, here are two different scenarios. (Scenario 2 is the incorrect behavior)
      Scenario 1:
      Select computer -> Select active session -> Client notification "do you allow x to remote control" -> User press Allow -> Remote session established 
      Scenario 2:
      Select computer -> Select active session -> Client notification not shown -> 1 minute timeout -> Remote session established 
      Are you guys aware of this? Has anyone else experienced the same behavior?
    • By DuarteV
      Hi, having trouble discovering SNMP on Windows 8.1 pc, geting error: Rquest Timeout, have tried with Firewall off and the same error, the device already works with another requester, can anyone help?

  • Create New...