Purpose
It is very important to monitor the transaction log size of SQL Server. If SQL Server runs out of log space the system will come to a standstill. The log space is released when a log backup is performed. But the space is only made available up to the starting point of the longest running open transaction. That means that if transactions are held open for a long time, the log will grow out of control. We have seen cases where transactions were held open for days in some SAP background jobs which resulted eventually in a full log and system standstill.
If transactions are open for a long time, and they also hold many locks on database objects, then un-necessary blocking can occur resulting in unexplained bad performance. This also increases the risk of deadlocks in the system.
Until now the only indicator in DBACOCKPIT that a transaction has been open too long has been the DB02 main screen (DBACOCKPIT -> Space -> Overview). The middle section of the screen looks like this:
DB02
What is new is the "History" button. That is the topic of this blog post. This button takes you to the new DBACOCKPIT action where you can view a history of long running transactions and examine the details of not only the processes which held a transaction for a longer time, but also the locks which were held by the session and blocked other sessions.
The screen is also available under DBACOCKPIT -> Performance -> History -> Open Transaction History
Setup
The Open Transaction History is always available for the local system if it's running on SQL Server, or if the DBACOCKPIT is used to monitor a remote SQL Server.
The action is supported for SQL Server 2005 and higher.
Using the Open Transaction History
The DBACOCKPIT action is available under Performance -> History -> Open Transaction History. The initial screen looks like this:
Open Transaction History
The top part of the screen shows a list of sessions which held a transaction open for more than 1 minute. The system checks this once every 20 minutes. The list shows when the data was collected, the session ID and which application program was using the session. The start time of the transaction and the duration in minutes so far at the collection time is also shown.
There is a column in the list which contains the first part of the SQL statement which was the last one executed on the session when the data was collected (the inputbuffer). Usually this statement is much longer than the area provided on the list. You can view the full statement by selecting the row and clicking on the "Details for selected row" button. Double-clicking on the line has the same effect. This will cause the full statement to appear in an edit control in the middle part of the screen.
The list also shows some attributes of the session such as the CPU time and number of reads. Also the current log size and the allocated space within the log at the time of collection are shown.
The toolbar of the ALV control has a couple of useful buttons: "ABAP Code" and "Table Detail". The system tries to determine the ABAP report name and location and also the primary table name to allow navigation to either view the ABAP code in an editor or view the table definition. The "ABAP Code" button is disabled when monitoring remote servers.
Active Request
When a session is checked there may be a SQL statement currently running on the session or it may be inactive. This is indicated by the column "Active request". If there is no active request it means that at the time the session was just waiting for the next SQL statement to be issued and no activity was occurring in that session on SQL Server. There can be many reasons for this. The ABAP program may be executing a long running calculation, it may be waiting in a remote function call (RFC), waiting for an enqueue object or working on executing SQL statements on another connection. If there is no active statement then the statement shown in the inputbuffer edit control is just the last statement executed prior to the data collection time, it wasn't being executed at the time.
If there was an active request then the columns to the right contain information about the request. CPU and I/O numbers will show how many resources were consumed by the request, what the wait type was and how long the wait had been.
If there was an active request on the session which was saved in the history, then the button "Show Active Request" in the toolbar will navigate to the statement analysis screen to examine the statement and the query plan of the request that was active.
Locks
The lower third of the screen shows a list of sessions which were blocked by the selected top level session which was determined to have been running the longest. If there was a hierarchy of blocked processes then the "SQL statement text" column will show which sessions were blocked in the hierarchy by showing arrows of different lengths indicating the depth of the session in the blocking hierarchy. This display is similar to the Performance -> Locks screen which shows currently blocked sessions.
For each of the sessions which were blocked the statement is shown and the button "SQL Statement" allows you to view the full statement and the full query plan in the statement monitor. This also allows navigation to the ABAP code and all the other statement analysis features such as viewing table and indexing details.
Usage Scenarios
Long running transactions can cause two different types of problems on the database server:
- Allocated log space grows out of control. If the log runs out of space then no changes can be made in the database anymore.
- Blocking locks which are held too long can cause performance bottlenecks and in some cases deadlocks can occur. This can happen even on systems which have more than sufficient CPU and I/O resources.
It is important to monitor the allocated log space over time and make sure there is always plenty of space available on the disk. In DBACOCKPIT go to Space -> History -> Database Size History and check the "Allocated log space (MB)" column. You will see that the log space allocated is reduced when log backups occur and then gradually increases until the next backup. Always make sure that the peak usage is well within the total available log space on the disk. If the allocated space in the log behaves erratically with extreme peaks, then it's an indication that transactions are held too long. In that case use the Open Transaction History screen to find the longest running transactions that have been caught by the history job. Use ALV features to sort the data according to the number of minutes the transaction was held open. Make a note of when these transactions start, which jobs open the transactions and whether there are performance problems or other artificial delays which could cause a transaction to remain open for an extended period.
Sometimes excessive blocking can cause performance problems. If users complain that some of their transaction are intermittently very slow, whereas most other transactions are fast, that's an indication that excessive blocking is occurring. In that case you can use DBACOCKPIT -> Performance -> SQL Statements and sort the data by "Wait ratio: elapsed/CPU time". There you will see statements which run for a long time, but don't use much CPU time, i.e. they are blocked. In addition you can visit the Open Transaction History screen and see if the "Blocking" column is checked. Then double click on those lines where "Blocking" is indicated. On the bottom ALV list you will be able to see all the sessions which were blocked by that statement. You may see a long list of blocking and blocked sessions. You can see which work process was used, whether it was a dialog or batch process. When you click on the "SQL Statement" button in the toolbar you can see which ABAP report was used and which table was used. This may help you determine how and why the job or frontend process was blocked and possibly how to avoid such a situation.
Release Information
This DBACOCKPIT feature was delivered with the following SAP_BASIS support packages:
756 SP01
755 SP03
754 SP05
753 SP07
752 SP10
751 SP13
750 SP23
740 SP27
731 SP30
The new feature is not supported in releases earlier than NetWeaver 7.30 SP1 (SAP_BASIS 731).
Conclusion
I hope this overview of the Open Transaction History monitor proves helpful to you. If you have further questions about the Monitor, or about a long running open transaction you are observing in your system, I encourage you to ask your questions in our SAP Community using this link.
If you want to read more about SAP on SQL server then I invite you to view our topic area here.
- SAP Managed Tags:
