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:

Select
[Database],
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]
from
(
select
cast(es.context_info as varchar(128)) as ci,
es.session_Id,db_name(sp.dbid) as [Database]
from
sys.dm_exec_sessions es inner join
sys.sysprocesses sp on es.session_id = sp.spid
where
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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s