Eae pessoal, tudo bom com vocês ?
Nesse post estou disponibilizando um script bem útil para identificar o que está sendo executado no servidor.
Ele mostra de uma forma bem detalhada. informações sobre as sessões que estão em execução.
SELECT des.session_id ,des.STATUS AS status ,des.login_name ,des.[HOST_NAME] AS host_name ,der.blocking_session_id ,DB_NAME(der.database_id) AS database_name ,der.command ,des.cpu_time ,des.reads ,des.writes ,DEC.last_write ,des.[program_name] ,emg.requested_memory_kb ,emg.granted_memory_kb ,emg.used_memory_kb ,der.wait_type ,der.wait_time ,der.last_wait_type ,der.wait_resource ,CASE des.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS transaction_isolation_level ,OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME ,dest.TEXT AS full_query_text ,SUBSTRING(dest.TEXT, der.statement_start_offset / 2, ( CASE WHEN der.statement_end_offset = - 1 THEN DATALENGTH(dest.TEXT) ELSE der.statement_end_offset END - der.statement_start_offset ) / 2) AS [executing_statement] ,deqp.query_plan FROM sys.dm_exec_sessions des LEFT JOIN sys.dm_exec_requests der ON des.session_id = der.session_id LEFT JOIN sys.dm_exec_connections DEC ON des.session_id = DEC.session_id LEFT JOIN sys.dm_exec_query_memory_grants emg ON des.session_id = emg.session_id CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp WHERE des.session_id <> @@SPID ORDER BY des.session_id