Wednesday, September 19, 2012

AX 2012 - Identifying a user session in SQL Server


In previous versions of Microsoft Dynamics AX, the Online users form contained a column that provided a session process ID (SPID) for each client session. This column was useful for debugging blocked sessions. Specifically, the SPID column was used to find a user session that corresponded to a specific SPID. In Microsoft Dynamics AX 2012, the SPID column is no longer available in the Online users form.
In Microsoft Dynamics AX 2012, information about the user session can be included in the connection context for Microsoft SQL Server. because the inclusion of this information has a small effect on performance, the functionality is not turned on by default.
So, if you want to add again SPID column in the Online users form, you have to :
  1. Click Start, click Run, type regedit in the Open box, and then click OK.
  2. Locate the following registry key:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Dynamics Server\6.0\01\Original (installed configuration)
    The Original (installed configuration) key is the key name for the current server configuration, if the configuration has not been changed from the default. If your system uses a configuration other than the configuration that was originally installed, locate the configuration that is currently active.
  3. Right-click the key, point to New, and then point to String value. Name the new value connectioncontext, and set the value to 1.
  4. Close the registry editor.
  5. Start the Services snap-in, and then restart the Application Object Server (AOS) service.

Find session information

  1. Open Microsoft SQL Server Management Studio.
  2. Run the following query to view all sessions that are related to Microsoft Dynamics AX:
    select cast(context_info as varchar(128)) as ci,* from sys.dm_exec_sessions where program_name like '%Dynamics%'
    The first two items in the ci (connection information) column are the Microsoft Dynamics AX user ID and the session ID.
  3. In the Online users form, you can now identify each session.

Troubleshooting database performance [AX 2012]

 Finding User Sessions from SPID in Dynamics AX 2012


No comments: