Saturday, June 9, 2018

SQL Currently running queries

Execute below query to find currently running queries in SQL.

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

Happening

Upgrade from AX 2012 to Latest Dynamics 365 Finance and Operation

Below are the steps defined by sequence. 1. Create new Upgrade project in Dynamics LCS. 2. Create VSTS Project and connect it with L...

Trending now