Investigate performance issues in AX. https://holsopple.wordpress.com/2011/02/18/sp_axwho-that-i-use/

sp_axwho.sql

--https://holsopple.wordpress.com/2011/02/18/sp_axwho-that-i-use/

USE [DynamicsTest]
GO
/****** Object: StoredProcedure [dbo].[SP_AXWHO] Script Date: 02/18/2011 14:32:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_AXWHO] AS
— ***********************************************************************
— Copyright © Microsoft Corporation.  All rights reserved.
— This script is made available to you without any express, implied or statutory warranty,
— not even the implied warranty of merchantability or fitness for a particular purpose,
— or the warranty of title or non-infringement.
— The entire risk of the use or the results from the use of this script remains with you.
— ***********************************************************************
SET NOCOUNT ON
SELECT	REQ.SESSION_ID,
RTRIM(convert(varchar(128),REQ.context_info)) AS BATCH_INFO,
SQL = SQL.text,
QUERYPLAN.query_plan AS EXEC_PLAN,
CPU_TIME,
TOTAL_ELAPSED_TIME,
READS,
WRITES,
LOGICAL_READS,
WAIT_TIME,
WAIT_TYPE,
WAIT_RESOURCE
INTO	#REQUESTS
FROM	sys.dm_exec_requests AS REQ
OUTER APPLY	sys.dm_exec_sql_text(REQ.sql_handle)	AS SQL
OUTER APPLY	sys.dm_exec_query_plan(REQ.plan_handle)	AS QUERYPLAN
WHERE	DATABASE_ID = DB_ID()
AND REQ.SESSION_ID <> @@SPID
AND REQ.SESSION_ID > 50
SELECT
GETDATE() AS CURRENT_DATETIME,
REQ.SESSION_ID,
BATCH_INFO,
SQL = CASE
WHEN REQ.SQL LIKE ‘FETCH API_CURSOR%’ THEN CURSORSQL.text
ELSE REQ.SQL
END,
REQ.TOTAL_ELAPSED_TIME,
EXEC_PLAN = CASE
WHEN REQ.SQL LIKE ‘FETCH API_CURSOR%’ THEN CURSORPLAN.query_plan
ELSE EXEC_PLAN
END	,
CURSOR_PROPERTIES = CASE
WHEN CURSORS.PROPERTIES IS NOT NULL THEN CURSORS.PROPERTIES
ELSE ‘N/A’
END,
CPU_TIME = CASE
WHEN CURSORS.worker_time IS NOT NULL THEN CURSORS.worker_time
ELSE REQ.CPU_TIME
END,
READS = CASE
WHEN CURSORS.READS IS NOT NULL THEN CURSORS.READS
ELSE REQ.READS
END,
WRITES = CASE
WHEN CURSORS.WRITES IS NOT NULL THEN CURSORS.WRITES
ELSE REQ.WRITES
END,
REQ.LOGICAL_READS,
CURSORS.DORMANT_DURATION,
REQ.WAIT_TIME,
REQ.WAIT_TYPE,
REQ.WAIT_RESOURCE
FROM	#REQUESTS AS REQ
—	————————————————————————————-
—	Special Handling for Cursors
—	If the blocking process is a cursor, get SQL text via sys.dm_exec_cursors.sql_handle
—	and the query plan via sys.dm_exec_query_stats.plan_handle
—	————————————————————————————-
OUTER APPLY	sys.dm_exec_cursors(REQ.SESSION_ID) AS CURSORS
OUTER APPLY	sys.dm_exec_sql_text(CURSORS.sql_handle) AS CURSORSQL
LEFT JOIN	sys.dm_exec_query_stats AS CURSORSTATS ON CURSORSTATS.sql_handle = CURSORS.sql_handle
OUTER APPLY	sys.dm_exec_query_plan(CURSORSTATS.plan_handle) AS CURSORPLAN
ORDER BY req.session_id