During periods of high load, SQL Server can experience an excessive locking behavior. This results in slow system response in Clarity.
The following queries were provided by Microsoft for isolating issues with blocking database processes:
SELECT sp.spid,sp.sql_handle,(select text from sys.sysprocesses as p --- get sql for blockercross apply sys.dm_exec_sql_text(p.sql_handle)where p.spid = sp.spid) AS sql_statementFROM SYS.sysprocesses spWHERE sp.spid IN (SELECT blockedFROM SYS.sysprocesses WHERE spid IN(SELECT blockedFROM SYS.sysprocesses WHERE blocked <> 0))
select t1.resource_type,db_name(resource_database_id) as [database],t1.resource_associated_entity_id as [blk object],t1.request_mode,t1.request_session_id -- spid of waiter,(select text from sys.dm_exec_requests as r --- get sql for waitercross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as waiter_text,t2.blocking_session_id -- spid of blocker,(select text from sys.sysprocesses as p --- get sql for blockercross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as blocker_textfromsys.dm_tran_locks as t1,sys.dm_os_waiting_tasks as t2wheret1.lock_owner_address = t2.resource_addressgo
These queries will return no results in a dormant database.