SQL sys.dm_tran_locks request

[Jacob Ipsen]

Hey 

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;

[Paul]

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.

-Paul