Many moons ago, I wrote about how to enable the context info for Dynamics AX. Having the context info enabled is a pretty important thing when dealing with Dynamics AX. The amount of time you can save yourself and the DAX Admins is astounding – if you have the context info enabled. If you are curious, read more about that here.
It is not an uncommon thing to need to know who is causing performance problems within AX. In addition, you will probably want to know what they are doing. But since that information is obfuscated due to the default setup of AX, you can find yourself beating your head against the desk in frustration.
Once you have the context info enabled, however, how are you going to trap that information? How are you going to figure out what queries are related to the context info passed from AX to the database? In addition, how are you going to parse that context info into something useful? Hint: some of those answers can be found in the aforementioned article – here.
Context
While the previous article goes into a good amount of detail in answering some of those questions, it does not divulge what to do to capture the context info. Capturing the context info is rather easy to do – if you choose to use Extended Events (as I prefer to do). And since this is the giving season, why not just give the session details and then let you play with the shiny new trace for a few days?
USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'DAX_CONTEXTINFO' )
DROP EVENT SESSION DAX_CONTEXTINFO
ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [DAX_CONTEXTINFO]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
( SET collect_statement = ( 1 )
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence,
sqlserver.database_id, sqlserver.database_name,
sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id,
sqlserver.context_info, sqlserver.client_connection_id )
WHERE ( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%select @CONTEXT_INFO =%')))
ADD TARGET package0.event_file
( SET filename = N'C:DatabaseXEDAX_CONTEXTINFO.xel', max_file_size = ( 10 ), max_rollover_files = ( 100 ))
WITH ( MAX_MEMORY = 512000 KB
, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 5 SECONDS
, MAX_EVENT_SIZE = 0 KB
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = OFF
, STARTUP_STATE = OFF );
GO
ALTER EVENT SESSION [DAX_CONTEXTINFO]
ON SERVER STATE = START;All in all, this is not a tremendously difficult session. The session is looking for statement completed events. When there is a qualifying event that fires, then I attach the additional payload data from the several actions (e.g. sql_text, database_id, server_principal_name and context_info) to the event payload so I can query it later.
Will I really need all of those actions? Not necessarily but the data can be useful here or there – especially if you are trying to familiarize yourself with a new DAX environment.
The Wrap
There are many many uses for Extended Events. In this article I show a quick session setup that can be useful in the troubleshooting of various different problems (most probably performance related) with Dynamics AX.
If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.
If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.
