• WARNING: depending on the events that you have selected this may cause the SQL Server to come to a grinding halt even if you have a powerful server and only a moderate number of users. This could include the options to Show all events and Show all columns in your trace.
• Running a SQL Profiler trace on the server where the SQL Server service is running will have a nominal impact on the server environment if only the required events are selected and/or a specific sadoss/ClientProcessID is being used in the filter. Users should not notice this impact when the trace is performed properly.
• If there are performance concerns, run SQL Profiler from a SQL Client installed on another workstation and point to the SQL Server instance to be traced. This reduces the footprint of the application.
• Do use the toggle bookmarks option from the Edit menu to mark areas of the trace between events. You can quickly move through the trace file results by using the applied bookmarks.
• This article addresses tracing of procedural tasks in X3. Additional events would need to be selected for performance related issues. Contact your SQL database administrator or refer to SQL Server documentation for details on tracing performance attributes.

Sometimes it is helpful to see what queries X3 is pulling when attempting to retrieve information for a particular function. 
     This article will give steps on how to run a basic SQL Profiler with a user filter, and how to take a query result and reformat it so it can be ran in a Query window in the Management Studio. 
     This article is not designed to give comprehensive training in SQL Profiler, nor how to interpret the SQL statements themselves.

Microsoft SQL Server Profiler is a Microsoft product. For training or support with this product please contact the appropriate vendor. For possible Sage X3 troubleshooting and interpretation of results please contact your Sage Business partner.

Identify the single user's sadoss process ID

1. Open Development, Utilities, System monitor, Users
2. Locate the row of the user whose actions you want to profile with the Function PSADX.
3. Select this row.
4. In the Active Processes pane, locate the row with sadoss in the process column.
5. Note the Process no.
6. This number can be used to filter the SQL Profiler to only record activities of a single user.

Set up X3 in a state just before the action you wish to profile

• In order to retrieve the smallest amount of information to sift through, have the user poised just before the action which needs to be analyzed. For example, if a drop-down / lookup field is not showing the records expected, be on the appropriate window and ready to click on the field.

Set up SQL Profiler Trace

1. Open the Microsoft SQL Management Studio and connect to the X3 instance.
2. Select Tools, SQL Server Profiler
3. Login with sa or equivalent
4. Type a name in the Trace name: box, if desired
5. Select the Events Selection tab
6. Select the check boxes Show all events and Show all columns
7. In the Events grid do the following:
   1. Right-click on Errors and Warnings and choose Select Event Category
   2. Scroll down to the Stored Procedures section
   3. On RPC:Completed, click the check box twice so there is a black check mark (indicating all events for that row are selected)
   4. Select SP:StmtCompleted
   5. Clear all other event rows and categories. For example, Security Audit and Sessions are not generally tracked and will add overhead to the trace. Whether these are selected by default is based on the profiler template selected on the General tab.
   6. NOTE: if troubleshooting Crystal Reports, you need to select the TSQL group as well.
   7. NOTE: If troubleshooting performance you may want to add ShowPlan XML in the Performance section. WARNING: This will add significant cost to process, the filter in the next step should always be used.
8. Preferably, add the user's sadoss ID
   1. Click Column Filters...
   2. Select ClientProcessID from left pane
   3. Click on Equals in right pane
   4. Type the sadoss process number in the box
9. Click OK

Execute the trace

1. In the SQL Profiler, click Run
2. In X3, have the user perform the function which needs to be traced.
3. In the SQL Profiler, click the Pause button

How to make a query result able to run in the Management Studio

The SQL query will normally be contained in a RPC:Completed EventClass.
Note: If you have already run the query it could also show up in the a cached event

Here is an example and how to interpret its components in order to run it in SQL

Set @p2=180154943
declare @p5 int
set @p5=2
declare @p6 int
set @p6=1
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output, @p2 output,N ' @P1 nvarchar(256),@P2 nvarchar(256),@P3 int,@P4 int,@P5 nvarchar(34)', N' SELECT SOH_.ROWID, SOH_.SOHCAT_0, SOH_.DLVSTA_0, SOH_.DLVSTA_0, SOH_.REP_0 FROM SEED.SORDER SOH_ JOIN SEED.AFCTFCY AFF_ ON ((AFF_.FCY_0 = SOH_.SALFCY_0) AND (AFF_.PRFCOD_0 = @P1) AND (AFF_.FNC_0 = @P2)) LEFT OUT JOIN SEED.TABMODELIV TMD_ ON ((TMD_.MDL_0 = SOH_.MDL_0)) LEFT OUTER JOIN SEED.BPADDRESS BPA_ ON ((BPA_.BPATYP_0 = @P3) AND BPA_.BPANUM_0 = SOH_.BPCORD_0 AND (BPA_.BPAADD_0 = SOH_.BPAADD_0)) WHERE SOH_.SOHCAT_0 < @P4 AND SOH_.SOHNUM_0 >= @P5
ORDER BY SOH_.SOHNUM_0 DESC
OPTION (FAST 100)',@p5 output,@p6 output,@p7 output, N'ADMIN',N'FUNCDUCDM',1,2,N' '
select @p1, @p2, @p5, @p6, @p7

The sections needed to run this query in the Management Studio are in pink, blue and green.

This query, re-written to run in a SQL Server Management Studio would look like this:

DECLARE @P1 nvarchar(256),@P2 nvarchar(256),@P3 int,@P4 int,@P5 nvarchar(34)
SELECT @P1=N'ADMIN', @P2=N'FUNCDUCDM', @P3=1, @P4=2, @P5=N' '
SELECT SOH_.ROWID, SOH_.SOHCAT_0, SOH_.DLVSTA_0, SOH_.DLVSTA_0, SOH_.REP_0
FROM SEED.SORDER SOH_
JOIN SEED.AFCTFCY AFF_
ON ((AFF_.FCY_0 = SOH_.SALFCY_0)
AND (AFF_.PRFCOD_0 = @P1)
AND (AFF_.FNC_0 = @P2))
LEFT OUTER JOIN SEED.TABMODELIV TMD_
ON ((TMD_.MDL_0 = SOH_.MDL_0))
LEFT OUTER JOIN SEED.BPADDRESS BPA_
ON ((BPA_.BPATYP_0 = @P3)
AND BPA_.BPANUM_0 = SOH_.BPCORD_0
AND (BPA_.BPAADD_0 = SOH_.BPAADD_0))
WHERE SOH_.SOHCAT_0 < @P4 And SOH_.SOHNUM_0 >= @P5
ORDER BY SOH_.SOHNUM_0 DESC
OPTION (FAST 100)

DocLink: How to troubleshoot slow performance for Sage X3