MSSQL: Looking into DB lock

Being a DBA. it is inevitable that users will come to your desk to ask why certain reports/queries running slower than usual or if there are anything blocking them.

Simulating a table lock for 10 minutes.

 use VCDB_2016  
 BEGIN TRAN   
 SELECT 1 FROM VPX_VERSION WITH (TABLOCKX)  
 WAITFOR DELAY '00:10:00'   
 ROLLBACK TRAN    
 GO  

Checking lock status.

 SELECT * FROM sys.dm_exec_requests where status not in ('sleeping','background');  

Dive deeper to take a look at the session id. The concerned session ID here is 52. There is Background, Running, Runnable, Sleeping and Suspended, The Sleeping and Background shouldn't be a concern. Suspended and perhaps Runnable should be looked at. 

 exec sp_who2  

Over here, the sp_who2 providing a lot of valuable information. The status is SUSPENDED and no blocker from other transaction (BlkBy Column). 

I usually prefer to take a peek at the SQL text as well. It is always useful to see which object, what is it doing and initiated from who. The sql_handle was gathered from the dm_exec_requests dynamic view. In this example, having a wait for 10 minutes should sound suspicious. The SQL Handle was gathered on the earlier query.

 select * from sys.dm_exec_sql_text(0x0200000049AABC156F2142A5AB98C8719717A8B61590F7CF0000000000000000000000000000000000000000)  

The following example showing what exactly the slow/suspended query is doing and from which object. Knowing what's the problem is half of the problem resolved.

In some cases, Suspended simply just slow and lacking resources and users simply just need to wait it out and let it completes. If the user needs to run a report and simply killing it every time, the needed report will never complete. 

Kill the session if it needs to be. For example, the offending session here is session ID 52.

kill 52