## 4.2.1.1 SQL Trace
The SQL trace collects information about all SQL statements executed on the index server (tenant database) or name sever (system database) and saves it in a trace file for further analysis. The SQL trace is inactive by default.
Information collected by the SQL trace includes overall execution time of each statement, the number of records affected, potential errors (for example, unique constraint violations) that were reported, the database connection being used, and so on. The SQL trace is a good starting point for understanding executed statements and their potential effect on the overall application and system performance, as well as for identifying potential performance bottlenecks at statement level.
## SQL Trace Files
SQL trace information is saved as an executable python program (by default sqltrace_&lt;...>=.py ), which can be used to replay the traced database operations. You can also use the SQL Trace Analyzer tool to automate the analysis of the file.
## Enabling and Configuring the SQL Trace
You can enable and configure the SQL trace in the SAP HANA database explorer or SAP HANA studio. Alternatively, you can modify the parameters in the sqltrace section of the indexserver.ini (tenant database) or nameserver.ini (system database).
##  Example
Use the following statement to enable the SQL trace:
```
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('sqltrace', 'trace') = 'on' WITH RECONFIGURE
```
##  Recommendation
Do not leave the SQL trace enabled all the time as writing trace files consumes storage space on the disk and can impact database performance significantly.
## Trace Levels
You can set the level for the SQL trace by changing the value of the configuration parameter [sqltrace] level in the indexserver.ini file (tenant database) or nameserver.ini file (system database). Trace information includes details such as executed timestamp, thread ID, connection ID, and statement ID.
| Trace Level   | Description                                                |
|---------------|------------------------------------------------------------|
| NORMAL        | All statements that have finished successfully are traced. |
| Trace Level      | Description                                                                                                                             |
|------------------|-----------------------------------------------------------------------------------------------------------------------------------------|
| ERROR            | All statements that returned errors are traced.                                                                                         |
| ERROR_ROLLBACK   | All statements that are rolled back are traced.                                                                                         |
| ALL              | All statements including status of normal, error, and rollback are traced.                                                              |
| ALL_WITH_RESULTS | In addition to the trace generated with trace level ALL, the result returned by select state- ments is also included in the trace file. |
|                  |  Note An SQL trace that includes results can quickly become very large.                                                                |
|                  |  Caution An SQL trace that includes results may expose security-relevant data, for example, query result sets.                         |
## Trace Details
You can configure trace detail information by setting the parameter [sqltrace] details . Y ou can select one or more categories of information to include in the trace, for example: ' basic,resource_consumption '. Possible values are listed in the following table. Note that for resource consumption information (this is also included in the 'all' option) the following two parameters in the global.ini file [resource_tracking] section, must be set to 'on':
- enable_tracking
- memory_tracking
You may also wish to limit the maximum memory allocation per statement by setting a value for the [memorymanager] statement_memory_limit parameter in the global.ini file. Set this to 5, for example, to apply a limit of 5GB.
| Trace Details        | Description                                                                                                                                |
|----------------------|--------------------------------------------------------------------------------------------------------------------------------------------|
| basic                | Connection information and statement information (default)                                                                                 |
| all                  | Include all comments of connection and statement                                                                                           |
| user_variables       | User-defined variables in the session context                                                                                              |
| statement            | Statement information such as executed timestamp, thread ID, connection ID, statement ID, statement hash and duration                      |
| session_variables    | System-defined variables in the session context                                                                                            |
| resource_consumption | Statement resource consumption information such as local (+remote, if available) cpu- time and memory-size.                                |
| passport             | Decoded passport contents                                                                                                                  |
| connection           | Connection information such as session ID, transaction ID, client PID, client IP, user name, schema name, and session variable:value pairs |
## Trace Details
"empty"
## Description
Trace without these comments
| Additional Configuration Options                     | Additional Configuration Options                        |                                          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|------------------------------------------------------|---------------------------------------------------------|------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Option                                               | Configuration Parameter                                 | Default                                  | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Trace file name                                      | tracefile                                               | sqltrace_$ HOST_$ {PORT}_$ {COUNT:3}. py | User-specific name for the trace file If you do not enter a user-specific file name, the file name is generated according to the following default pattern: DB_<dbname>/sqltrace_$HOST_$ {PORT}_${COUNT:3}.py , where: • DB_ <dbname> is the sub-directory where the trace file is written if you are running on a ten- ant database • $HOST is the host name of the service (for example, indexserver) • $PORT is the port number of the service • $COUNT:3 is an automatically generated 3- digit number starting with 000 that incre- ments by 1 and serves as a file counter when |
| User, applica- tion, object, and statement fil- ters | user application_user application object statement_type | Empty string                             | Filters to restrict traced statements to those of particular database or application users and appli- cations, as well as to certain statement types and specific objects (tables, views, procedures). All statements matching the filter criteria are re- corded and saved to the specified trace file. For user , application_user , and                                                                                                                                                                                                                                            |
| Flush limit                                          | flush_interval                                          | 16                                       | During tracing, the messages of a connection are buffered. As soon as the flush limit number of mes- sages is buffered (or if the connection is closed), those messages are written to the trace file. When set to 0, every SQL trace statement is imme- diately written to the trace file                                                                                                                                                                                                                                                                                            |
## Using Wildcards
If you apply filters for the user , application_user , and application parameters, the use of wildcards and exceptions is also supported. The asterisk wildcard character denotes any number of characters and the exclamation mark denotes an exclusion. For example:
user=SM*,JONES,!GREEN,!BRO*
In this case all users starting with SM will be traced, JONES will be traced, user GREEN will not be traced and all users starting with BRO will not be traced. If terms in the string conflict with each other then the sequence in
which the terms occur determines the result. In the following example user SMALL will be traced in spite of the exclusion; the exclusion is ignored because it occurs after the first wildcard.
user=SM*,JONES,!SMALL,!BRO*
## Trace File Rotation
The size and number of trace files are controlled by the following parameters.
| Parameter      | Default                | Description                                                |
|----------------|------------------------|------------------------------------------------------------|
| max_files      | 1                      | Sets the maximum number of trace files                     |
| filesize_limit | 1610612736 (or 1.5 GB) | Sets the maximum size of an individual trace file in bytes |
##  Caution
If both the maximum number of files and the maximum file size are reached, SQL tracing stops. If this happens, you can increase the values of max_files and filesize_limit . See SAP Note 2629103.
## SAP HANA SQL Trace Analyzer
SAP HANA SQL trace analyzer is a Python tool you can use to analyze the HANA SQL trace output. The tool gives you an overview of the top SQL statements, the tables accessed, statistical information on different statement types and on transactions executed.
For more information about the installation and usage of SAP HANA SQL trace analyzer, see SAP Knowledge Base Article 2412519 FAQ: SAP HANA SQL Trace Analyzer .
## Related Information
Diagnostic Files and Logs
SAP Note 2412519
SAP Note 2629103