Saturday, June 9, 2018

SQL Currently running queries

Execute below query to find currently running queries.

This can be helpful to identify blocking.

SELECT
      cast(axsess.context_info AS VARCHAR(128)) AS [Connection info],
      req.session_id
   ,blocking_session_id
      ,req.status
      ,req.total_elapsed_time / 1000.0 AS total_elapsed_time
      ,req.wait_type
   ,ses.host_name     
   ,DB_NAME(req.database_id) AS DB_NAME     
      ,sqltext.text     
   ,ses.login_name 
   ,req.command
   ,req.start_time
   ,req.cpu_time 
   ,req.command   
 FROM  sys.dm_exec_requests req
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
 JOIN  sys.dm_exec_sessions ses
     ON ses.session_id = req.session_id
      join 
  ---cast(axsess.context_info AS VARCHAR(128)) AS [Connection info] , *
  sys.dm_exec_sessions axsess
 on axsess.session_id = req.session_id
 where DB_NAME(req.database_id) = db_name()
 --WHERE         
 --axsess.program_name LIKE '%Dynamics%'
 --and axsess.status = 'running' 
 --and req.wait_type IS NOT NULL
 --and req.wait_type = 'OLEDB'     

No comments:

Post a Comment

Verify active/enabled product dimension

Below code can be used to verify active or enabled specific or all product dimensions. public static void main(Args _args) { ...