Analyzing Long Running Transaction in SAP : For Freshers

Identify the Long Running Transactions: You can use transaction code ST03 or ST03N to evaluate the overall system performance. These transactions provide an analysis of response times, giving you an overview of the total workload and the performance of the system. STAD allows you to analyze individual transactions, where you can identify the ones that are taking an extended amount of time to execute.

Analyze the Transaction: After identifying the long running transactions, the next step is to analyze them. Use ST12 transaction to get a detailed trace of individual transactions or programs. It provides a detailed analysis of where exactly in the transaction the bottleneck is happening.

Check System Performance: Transaction codes SM50 and SM66 provide an overview of the system's work processes. You can see if any work process is stuck or running for a long time, which may be due to a long running transaction.

Database Performance: The database performance can be checked using transaction code ST04. This gives you an overview of the database's performance, including buffer hit ratio, lock waits, SQL statement analysis, etc. Poor database performance can lead to longer transaction times.

Lock Entries: Long running transactions can sometimes cause table locks, which can affect other transactions. You can check for lock entries using transaction code SM12. If there are old or stuck lock entries, it might be necessary to clear them.

Update Process: The update queues can be monitored using transaction code SM13. If there are many unprocessed update requests, it could indicate a problem. You might need to analyze why updates are not getting processed and may need to restart the update work processes if required.

Work with ABAP Developers or Functional Team: If a custom program or transaction is causing the issue, you may need to work with the ABAP development team or the relevant functional team to optimize the code or transaction. They can use tools such as SQL Trace, Runtime Analysis or the ABAP Debugger to identify inefficient code or database accesses.

Consider System Resources: Check whether the system has sufficient resources such as CPU and memory to handle the load. You can use transaction code ST06 for a basic overview of the system's resource usage. If the system resources are constantly at high utilization, you might need to consider a system upgrade or load balancing.

Archiving Data: If the system has a lot of old data, it might be slowing down transactions. Data archiving can be setup to archive old data, thus improving system performance. You can use transaction code SARA to setup archiving.

Implement Proper Indexing: Check if the database tables used by the transaction are properly indexed. Indexes improve the performance of database accesses. However, creating too many indexes can slow down database write operations. Therefore, this needs to be done in cooperation with your DBA and after carefully analyzing the database accesses of your transaction. 

As a DBA , your scope increase and you need to check the following as well .

SQL Optimization: Investigate the SQL queries running for a long time using SQL trace and other database-specific tools. You may need to optimize these queries for better performance.

Database Statistics: Ensure that database statistics are up-to-date. Accurate statistics are essential for the database optimizer to decide the best execution plan for SQL queries.

Database Parameters: Review and tune database parameters. Each database system has its own set of parameters that can be adjusted for optimal performance.

Database Indexes: Check the usage of indexes in the database. Inefficient or unused indexes can be dropped. New indexes can be created where required, after careful analysis.

Database Blocks and Deadlocks: Monitor the database for any blocks or deadlocks. These can significantly slow down transactions and should be resolved promptly.

Table Partitioning: If certain database tables have grown significantly, consider table partitioning. Partitioning can help improve performance by reducing index size and making it easier for the database to manage data.

Storage Systems: Check the performance of the underlying storage systems. Slow disk I/O can significantly impact database performance.

Network Latency: In a distributed environment, network latency can impact performance. You may need to work with your network team to investigate any network issues.

Database Logs: Regularly review database logs for any signs of problems. This can include things like repeated errors, warnings about deprecated features, or messages about resource shortages.

Backup and Recovery: Ensure that backup and recovery procedures are in place and working as expected. While this may not directly improve performance, it's essential for dealing with any potential data loss situations.

Database Version and Patches: Ensure that the database software is up-to-date with the latest patches. Newer versions often come with performance improvements and bug fixes.

Here are few commonly used databases and steps specifics to that which can be checked further :

SAP HANA:

• SQL Optimization: Use the PLAN_VISUALIZATION or EXPLAIN PLAN for SQL queries to understand their execution and optimize accordingly.
• Database Statistics: HANA auto-updates its statistics. To manually update, use UPDATE STATISTICS command. --> Before making any update commands , be 100% sure what you are doing and consult senior consultants. 
• Database Parameters: Review parameters in the global.ini and indexserver.ini files.

        

• log_mode:

Tradeoff: Durability vs. Performance.

Synchronous logging ensures transaction durability but may impact the performance of long-running transactions due to the additional overhead of writing logs to disk synchronously. Asynchronous logging improves performance but may introduce the risk of data loss in case of a system failure.

• log_auto_truncate:

Tradeoff: Data Integrity vs. Performance.

Enabling automatic log truncation improves performance by reducing the time taken for log cleanup tasks. However, it may impact data integrity as it removes logs that are required for point-in-time recovery.

• logsegment_min_size and logsegment_size:

Tradeoff: Log Switch Frequency vs. Performance.

Setting a smaller logsegment_min_size and logsegment_size may increase the frequency of log switches, impacting long-running transactions. On the other hand, larger log segments reduce the number of switches but may lead to higher memory consumption.

• timeout:

Tradeoff: Transaction Completeness vs. Resource Utilization.

A shorter timeout helps prevent long-running transactions from tying up system resources for an extended period. However, it may terminate transactions prematurely, leading to incomplete results or data inconsistencies.

• statement_timeout:

Tradeoff: Query Execution Completeness vs. Resource Utilization.

Setting a shorter statement_timeout ensures that individual SQL statements complete within a defined time frame. However, complex queries or long-running transactions may be terminated before completion, potentially impacting data consistency or result accuracy.

• memory_allocation_limit:

Tradeoff: Resource Utilization vs. Transaction Execution.

Setting a low memory_allocation_limit limits the amount of memory a transaction can use, preventing excessive resource consumption. However, it may impact the execution of memory-intensive or complex long-running transactions, leading to performance degradation or errors.

• max_parallel_degree:

Tradeoff: Resource Contention vs. Query Performance.

Higher max_parallel_degree values allow more parallelism in query execution, potentially improving performance. However, it may introduce resource contention, especially if the system lacks sufficient resources or if multiple parallel queries compete for the same resources.

• max_runtime:

Tradeoff: Query Execution Completeness vs. Resource Utilization.

Setting a shorter max_runtime ensures that individual SQL statements complete within a defined time limit. However, it may terminate long-running queries prematurely, potentially impacting data consistency or result completeness.

• Database Indexes: In HANA, primary keys automatically create an index. Analyze whether other fields need indexing based on the PLAN_VISUALIZATION of your SQL queries.
• Table Partitioning: Use the ALTER TABLE command to partition large tables. --> Before making any update commands , be 100% sure what you are doing and consult senior consultants. Overall System Health: M_SYSTEM_OVERVIEW - Gives a general overview of the system status including CPU utilization, memory usage, disk usage, system uptime, etc.
• Monitoring: Monitor system performance with SAP HANA Studio or Cockpit. Use M_* views for specific monitoring tasks.

         Overall System Health: M_SYSTEM_OVERVIEW - Gives a general                                 overview of the system status including CPU utilization, memory                             usage, disk usage, system uptime, etc.
         Active Sessions: M_CONNECTIONS - Provides information about all                                 currently active connections to the SAP HANA database.
         CPU Usage: M_HOST_RESOURCE_UTILIZATION - Displays CPU usage                         statistics.
         Memory Usage: M_MEMORY - Provides detailed information about memory                     usage in the system. For overall memory usage, you can use                                     M_TOTAL_MEMORY_USED.
        Disk Usage: M_VOLUME_IO_TOTAL_STATISTICS - Gives you an overview                     of disk usage.
         SQL Performance: M_SQL_PLAN_CACHE - Provides details about SQL                        statements that are stored in the plan cache. You can use this view to                     analyze performance of SQL queries.
         Locks: M_LOCKS - Provides information about locks in the system.
         Alerts: M_ALERTS - Displays information about alerts generated by the                             system.
         Services: M_SERVICES - Provides information about all services running on                     all hosts of the system.

• Long running transactions :

               Execute the following SQL statement to identify long-running transactions:

                SELECT SESSION_ID, SQL_PLAN_ID, ELAPSED_TIME_MS

                FROM M_TRANSACTIONS

               WHERE STATE = 'ACTIVE' AND ELAPSED_TIME_MS >                                                <desired_threshold>;    

               Analyze SQL plan for each such transaction

                EXPLAIN PLAN FOR STATEMENT_ID <sql_plan_id>;

                SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID =                                        <sql_plan_id>;

DB2:

• SQL Optimization: Use the db2expln tool or EXPLAIN PLAN for SQL query optimization.

            DB2EXPLN is a command-line tool provided by IBM DB2 that allows you to                        analyze and understand the access plans generated by the query optimizer for SQL                statements

                    EXPLAIN PLAN FOR <your_sql_statement>;

• Database Statistics: Use RUNSTATS command to update database statistics.

            RUNSTATS is a DB2 utility that gathers statistical information about database                    objects, such as tables and indexes

                   RUNSTATS ON TABLE schema_name.table_name

                   RUNSTATS ON INDEX schema_name.index_name

• Database Parameters: Review and tune database parameters using UPDATE DB CFG or UPDATE DBM CFG commands.

• UPDATE DB CFG FOR <database_name> USING <parameter_name>                                <new_value>

•  MAXAPPLS: Specifies the maximum number of applications (connections) allowed to connect to the database simultaneously. A low value may result in contention and blocking during high load, potentially affecting long-running transactions.
• LOCKTIMEOUT: Determines the maximum time (in seconds) that a transaction will wait for a lock before timing out. A shorter timeout may cause transactions to be terminated prematurely.
• LOGFILSIZE: Defines the size of the active log file in bytes. If the log files are too small, it can lead to frequent log file switches, which can impact transaction performance.

• UPDATE DBM CFG USING <parameter_name> <new_value> these are certain parameters that needs to be checked on priority as we have some knowledge this can indirectly/directly have effects on Long running transaction.

• MAXAGENTS: Determines the maximum number of agents (database processes) that can be active simultaneously in the DB2 instance. A low value may result in resource contention and potentially impact long-running transactions.
• HEALTH_MON: Enables or disables the health monitor, which monitors the health of the instance and can detect and terminate long-running or runaway transactions.
• SHEAPTHRES: Specifies the threshold for sort heap allocation. A low value may cause frequent disk I/O for sorts, impacting transaction performance.

• Database Indexes: Use the CREATE INDEX command to create indexes on frequently accessed columns. --> this will generally require a wider audience for discussion and all the stakeholders need to be informed

        Read more about it in different blog.

• Table Partitioning: Use CREATE TABLE command with PARTITION BY clause to partition large tables.

        Read more about it in different blog.

• Monitoring: Use db2pd, LIST APPLICATIONS, or GET SNAPSHOT commands for monitoring database.

            The db2top command provides a real-time interactive interface to monitor                various aspects of DB2, including active transactions.

            

            The db2pd command allows you to gather detailed diagnostic information                about a running DB2 instance.

                           db2pd -transactions

            MON_GET_ACTIVITY : 

            SELECT APPLICATION_HANDLE, AGENT_ID, AGENT_TYPE,                                SECONDS_ACTIVE

            FROM TABLE(MON_GET_ACTIVITY(NULL, -1)) AS T

            WHERE AGENT_TYPE = 'UOW'

            ORDER BY SECONDS_ACTIVE DESC;

            SELECT AGENT_ID, AGENT_TYPE, SECONDS_ACTIVE

            FROM SYSIBMADM.SNAPAPPL_INFO    

            WHERE AGENT_TYPE = 'UOW'

            ORDER BY SECONDS_ACTIVE DESC;

                

MSSQL:

SQL Optimization using SQL Server Profiler or Execution Plans:

• SQL Server Profiler: This is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.

• Execution Plans: In SQL Server Management Studio, after writing your SQL query in the query editor, you can click on the "Display Estimated Execution Plan" icon or press Ctrl + L to see the estimated execution plan. For the actual execution plan, you can click on the "Include Actual Execution Plan" icon or press Ctrl + M.

• Database Statistics using the UPDATE STATISTICS command:

UPDATE STATISTICS table_name;

This command updates the statistics of the specified table or indexed view.

Review and tune database parameters using SQL Server Management Studio (SSMS):

You can use SSMS to access, configure, manage, administer, and develop all components of SQL Server. It includes both script editors and graphical tools. You can review parameters such as memory allocation, max degree of parallelism, etc.

Database Indexes using the CREATE INDEX command:

CREATE INDEX index_name ON table_name (column1, column2, ...);

Replace index_name with the name you want to give the index, table_name with the name of the table on which you want to create the index, and column1, column2, etc. with the columns you want to include in the index.

• Table Partitioning using CREATE PARTITION SCHEME and CREATE PARTITION FUNCTION commands:

CREATE PARTITION FUNCTION partition_function_name (input_parameter_type) AS RANGE LEFT FOR VALUES (value1, value2, ...);

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

TO (filegroup1, filegroup2, ...);

Replace partition_function_name with the name for the partition function, input_parameter_type with the data type of the column on which you want to partition the table, value1, value2, etc. with the boundary values for the partitions, partition_scheme_name with the name for the partition scheme, and filegroup1, filegroup2, etc. with the filegroups to hold the partitions.

• Monitoring using SQL Server Management Studio, Dynamic Management Views (DMVs), or Performance Monitor:

SQL Server Management Studio: You can use Activity Monitor SSMS to monitor current activity on the database.

Dynamic Management Views: DMVs return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. For example, sys.dm_exec_requests can be used to see what requests are currently executing.

Performance Monitor: This is a Microsoft Management Console (MMC) snap-in that provides tools for analyzing system performance. You can monitor SQL Server counters for a comprehensive view of SQL Server performance.

Please replace the placeholders (table_name, column1, index_name, etc.) with the actual values based on your database schema.