About locks and memory usage by the DMV

View the lock added by the SPID that connects to the current database

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT db_name (resource_database_id) as DatabaseName

, request_session_id

, Resource_type

, case

When resource_type = ' OBJECT '

THEN object_name (resource_associated_entity_id)

When Resource_type in (' KEY ', ' PAGE ', ' RID ')

THEN (SELECT object_name (object_id)

From Sys.partitions P

WHERE p.hobt_id = l.resource_associated_entity_id)

End as Resource_type_name

, Request_status

, Request_mode

From sys.dm_tran_locks l

WHERE request_session_id!=@ @spid

ORDER BY request_session_id

The result is as shown in figure:

If you look at more locks, adjust the Where condition

View idle sessions that do not close transactions

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT es.session_id, Es.login_name, Es.host_name, Est.text

, Cn.last_read, Cn.last_write, Es.program_name

From sys.dm_exec_sessions ES

INNER JOIN Sys.dm_tran_session_transactions St

On es.session_id = st.session_id

INNER JOIN sys.dm_exec_connections cn

On es.session_id = cn.session_id

CROSS APPLY sys.dm_exec_sql_text (cn.most_recent_sql_handle) EST

Left OUTER JOIN sys.dm_exec_requests er

On st.session_id = er.session_id

and er.session_id is NULL