Linking AX Sessions with SQL SPIDs

It is useful to be able to link the DAX session numbers with the SQL SPID, especially if you want to diagnose who is causing locks, or long running queries. This is one method to get the information.

Before you can link the two pieces of information you need to enable an extra mode on the AOS server(s). On each AOS you want to review the DAX sessions against the SQL SPID you need to complete the following setup.

1. Open the registry editor.

2. Navigate to the following location: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Dynamics Server\6.0\<instance>\<configuration>

3. Create a new string registry value called ‘connectioncontext’, setting the value to 1

4. Restart the AOS.

This will enable SQL to be able to access the DAX session information from SQL.

To view the information open SQL server management studios and run the following query:

cast(rtrim(ltrim(substring(ci,CHARINDEX(‘ ‘, ci, 2),CHARINDEX(‘ ‘, ci, CHARINDEX(‘ ‘, ci, 2) + 1) – CHARINDEX(‘ ‘, ci, CHARINDEX(‘ ‘, ci, 2))))) as bigint) as [AX Session],
rtrim(ltrim(substring(ci,1,CHARINDEX(‘ ‘, ci, 2)))) as [AX User],
session_Id as [SQL SPID]
cast(es.context_info as varchar(128)) as ci,
es.session_Id,db_name(sp.dbid) as [Database]
sys.dm_exec_sessions es inner join
sys.sysprocesses sp on es.session_id = sp.spid
es.program_name like ‘%Dynamics%’
and cast(es.context_info as varchar(128)) <> ”
and PATINDEX(‘%ODBC%’,cast(es.context_info as varchar(128))) > 0
) SessionData

Order by [Database], [AX Session], [AX User], [SQL SPID]

This SQL will return the following information:

Database, AX Session, AX User and SQL SPID.

NOTE: This information is in real-time.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s