Downtime Announcement: Please note the SAP Community Wiki will be unavailable due to a system upgrade on Thursday, September 24th between 6 and 7 AM CEST
Skip to end of metadata
Go to start of metadata

PURPOSE

Learn how to use transaction st05 to trace a program/transaction or just transaction or you just to know what is this a process executing at database level

OVERVIEW

SQL trace(ST05) provides the developer the ability to analyse database select statements. 

This is transaction st05 main screen


You cannot run two st05 at the same time. To be able to start st05 you should see "all traces are switched off"
 

We are  going to expalain how to use st05 with a practical example. Imagine that you are having performance problems  with ZM08 transaction or you just want to know what is this transaction executing at database level

 

HOW TO START/ACTIVATE ST05 TRANSACTION

To start/activate st05 trace we have to choose "select trace" that is the type of trace based on our perform requirements. Most of times we only use "SQL Trace" . Then we click "Activate Trace with Filter" button

This new window will appear

 

 

we can choose between three principal filters: "User Name", "Transaction Name" or "Program Name"

It would be possible to also filter by work process number or by table name to trace all the sentences executed against a single or group of tables. 

Most of times a combination of user name and transaction name or program name is used.

Once we have choosed the required filters we confirming with the green tick (red circle in the screeshot).  We will see then this screen where we have the date and time our trace first started (17.07.2015 09:40:06)

 


 

HOW TO STOP/DEACTIVATE ST05 TRANSACTION

Once transaction ZM08 has finished 

we deactivate the trace

And then we display the trace

By default it will show you the last scheduled trace timing in "Trace Period". Anyways if we are doing several traces in a row we can choose the date/time to display

We can also change the "Trace Type" and "Restrictions". To continue we click the green tickon left top.

If the number of records on our trace is more thatn 5000 rows a new prompt window will show up asking if you want to continue : YES

We will have something like this with all the statements executed ordered by the time sequence

We can order the output by "Runtime" column to see the expensier statements of this transaction. 

 

OTHER ST05 utilities

1) If you want to save the trace info / delete a saved trace or display it

 

to save the trace used for the example

 

2) Top menu options for each statement

 

In our test

The query plan 

If we want to find out what was the execution plan when this query was executed somethimes its possible to find it in st04> performance> History > SQL Statement History and search the date and time of this executions

 

 

3) Different options to display the info

 

Summarize Trace by SQL Statement : Combines the times for all the executions of a SQL statement . This is usually one of the best options to start our analysis. 

Display Identical Selects: Combines the times for all the executions of a SQL statement  with the same values ( e.g. values in where clause)

 

 

 

RELATED SAP NOTES/KBAs


SAP Note 155413 - Analysis of slow SQL statements
SAP Note 1426265 - ST05 Traces show only '?' and no literal values
SAP Note 2117958 - Explain of SQL plan requires elevated privileges

FAQ


FAQ will be added here soon.

 

RELATED CONTENT


N/A

 

 

Feedback

Please provide any feedback or questions regarding the content under the current page comments. If you have an issue or is looking for help, try to post your question on SAP community with Microsoft SQL Server tag.

In order to leave comments, you must be logged on with your SAP account.