Detail Analysis Menu

The ST04 Detail Analysis Menu provides you more tools to monitor your SQL Server database. Note that for certain connection settings some of the functionality is not available.  For example, if you monitor a database of one of the SAP Java applications there are not all database tables available which are used to monitor a Web Application Server.

For more information, see also Monitoring Remote SQL Server Databases.

In the Detail Analysis Menu of the SAP/SQL Server Database Monitor, you can display information about the following areas:

SQL Processes

Shows all the threads needed by SQL Server, including all connections made by SAP work processes.

To sort the display by work process, choose Process monitor ® Grouped output. The work process ID is displayed in the column Host PID.

You can display the currently executed statement for threads at the bottom window. Double-click a row or mark a row and then choose SQL Statement.

If a blocking situation occurs, the column Blcked shows the SQL Server process connection that is blocked and the process that is blocking it. All processes that are not displayed as zero in this column are being blocked by another process.

As in the main screen, you can Refresh and Reset and display changes since reset.

SAP SQL Statistics

Displays statistics collected by the SAP system for the execution of SQL statements.

Each SAP server has a statement statistics cache in memory. This cache can contain statistics about stored procedures and dynamic statements. Normally the SAP system does not use stored procedures in SAP NetWeaver. You can enable the use of stored procedures with a profile parameter. Therefore,  the statistics for stored procedures can be also displayed here.

The cache statistics are displayed on the main screen. All application servers are listed along with the sizes of the stored procedure and statement caches. If you have a central system only one server is displayed. To switch name cache statistics collection on or off, choose SQL stat on/off.

Choose SQL statistics to display the SAP SQL statement. Each statement executed is displayed in one row, showing the duration and frequency of execution. The first line shows the totals of some of the columns. Refer to the F1 help to find out what each column means. Choose ABAP code to see the ABAP coding.

To display the statement text of a stored procedure or a dynamic statement, select a line and choose SQL statement. On the next screen, choose Explain Tree  to display the new execution plan, which takes into account the parameter values listed below on the screen. Choose Table detail to display details of the table used by the stored procedure or dynamic statement.

If there is still an execution plan in the SQL Server procedure cache you can also choose SP explain from the menu.

IO per File

Displays the I/O statistics for each file. You can use I/O statistics to compare the activity for each data file, and to determine whether activity is evenly distributed over the data files or whether activity is concentrated on one or more particular data files. Also, the IOSTALLS per read operation, which is an important indicator for the performance of the IO system is displayed.

SQL requests

Displays information on the SQL Server procedure cache. This information consists of an analysis of the statements, which put the highest load on the database. The analysis shows a list of recently executed statements with statistical information on the number of executions, the average runtime, and the logical reads and writes of the last execution. By default, the 300 statements with the highest load are shown. You can change this default value.

There are similar functions as in SAP SQL Statistics that provide you with a detailed analysis of a statement. Choose SQL text or double-click on a line to display the full statement text. Choose Explain to display the execution plan. Choose ABAP code to display the ABAP coding. Choose Table detail to get details of the table used by the stored procedures and dynamic statements.

SQL profiler

This is an SQL-specific trace tool that you can use to monitor the database activity. You can start the SQL profiler using pre-configured settings for the event classes to be traced. If you create new traces, you can define the event class, the trace parameters and filters to limit the amount of data to be traced. You can also stop the profiler. If you use the Display icon, you can also view the individual events recorded by the profiler. You can then view the complete text of the event, or you can get the stored procedure definition if the event is a stored procedure execution. You can also get the execution plan for an event by choosing Explain, or you can edit the statement first and then explain it by choosing Edit and Explain.

Exclusive Lockwaits

Normally no data is displayed here. You see the following message: Currently no exclusive lockwaits found.

Exclusive lockwaits are wait situations that are currently being caused by database locks.

A user holding a lock occupies an SAP work process. If other users attempt to apply the same lock, these users will have to wait. During this time, these users occupy their own SAP work process. This waiting for a lock is known as a lockwait. As the number of lockwaits increases, the available SAP work processes can process fewer and fewer SAP user requests. In the worst case, that is when the number of lockwaits equals the number of SAP work processes, a small number of users can cause the entire SAP system to freeze.

If exclusive lockwaits occur, both blocking and blocked processes are displayed. For each blocking situation, a tree of waiting processes is shown. Column Hpid shows the process IDs of the host processes. Usually, this is the process ID of the SAP work process.

To display the SQL statement on the database, mark a row and then choose SQL statement.

Blocking Lockstats

Displays a history of blocking situations. This function shows blocking situations in the past as opposed to Exclusive Lockwaits, which shows the current situation. To collect this history, a SQL Server job called SAP CCMS Blocking Lockstats runs once a minute. If blocking lockstats is switched off, this job is disabled.

We recommend that you switch on blocking lockstats, as the effect on system performance is negligible.

Each line on the function’s display shows a time stamp when a blocking situation occurred, the requesting spid, the blocked spid, the work process pid, the number of open transactions on the blocked spid, and some other self explanatory columns.

The Wt Tm(sec) column is particularly important. It shows how long, in seconds, the spid was blocked. The red lines represent the blocked spids. The white lines causing the blocking situation can be deduced by comparing time stamps.

The WaitRsc, Table, and Index columns contain data about the resource involved in the locking conflict. Multiple occurrances of the blocking spid can be shown if the WaitRsc column changes.

The InputBuff column displays the command that was executed by the spid, if the required permissions to obtain the data exist.

We recommend to analyze only long running blocking situations in detail. You should place your attention on scheduling the custom batch jobs and the blocking these jobs can cause.

A regular weekly job, SAP CCMS Cleanup Saplocks deletes all blocking lockstats data that is older than 7 days.

Deadlock

Displays a selection screen to search for deadlocks.

The section Execution selection allows you to select Single statistics or Count statistics. Single statistics displays the detailed history of database deadlocks collected by the SAP system for the last 7 days. Count statistics displays a summary of the statistics for all deadlocks since the SAP system was installed.

Error Logs

The SQL Server error log provides additional information about bottleneck situations and their causes.

You can alternatively use the Enterprise Manager to display the SQL Server error log, or display the error log directly from the file system directory \Program Files\Microsoft SQL Server\LOG. For example, if SQL Server fails to start, you need to check the error log in that directory.

You can also display the SQL Agent log here.

State On Disk

Displays information to help you monitor the database growth. This button calls the Database Performance Monitor: Database Allocation (transaction DB02).

System Tables

Displays the content of important system tables, the result of DBCC commands, and system functions.

SQL Parameters

Displays all the SQL Server parameters, database options and change history.

Performance History

You can check the history of the values displayed here. A snapshot is collected every 2 hours.

DB Utilities

From here, you can execute SQL Server stored procedures and DBCC commands.

DB Backup History

Calls the CCMS monitor for Backup and Restore Information (transaction DB12).

Further options in the Detail Analysis Screen

Besides the areas described above, you can also display the following information using the DB Analysis menu:

Server Detail

From the  menu choose Server Detail to get an overview of SQL Server performance data, as it was available in former releases. With SAP NetWeaver you can use the DBCOLLECTOR screen.

DLL Consistency Check

From the detail analysis menu choose goto dll consistency chk.

You see the history of disp+work.exe on each application server. An ALV display shows a line for each DLL which is loaded by the disp+work.exe.

You can display the active DLLs, or the DLLs which are delivered as part of the SAP application by choosing the respective buttons. Use the buttons to switch between the display of the history of kernel patch upgrades and the other operating system dlls which are loaded by disp+work.exe. For example, you can see the MDAC dlls which are used when connecting to the database.

The report is particularly useful for troubleshooting.