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

This screen shows the general database information with greater detail than is displayed in the main screen.

Shows all the threads needed by SQL Server, including all connections made by R/3 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 active threads (threads with status runnable). Mark a row, 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.

Displays the collected statistics for name cache and stored procedure execution.

R/3 with SQL Server executes all ABAP SQL statements as stored procedures.

Each R/3 server has a stored procedure name cache in R/3. This stored procedure name cache can contain statistics about stored procedures.

If an R/3 System consists of more than one server, you can display information for your local server or for all servers in the R/3 System. In a central R/3 System, the name cache statistics are displayed first. In a non-central R/3 System, choose Name Cache Stats to display them. To switch name cache statistics collection on or off, choose Set statistics ® On or Off.

Choose SP statistics to display the selection screen for stored procedure statistics. You can restrict your selection or choose Execute to display all the statistics. Each stored procedure executed is displayed in one row, showing the duration and frequency of execution. Refer to the F1 Help to find out what each column means.

To display the source text of a stored procedure, select the line with the stored procedure, then choose Single detail. Choose Explain to display the execution plan. Choose Table detail to display details of the table used by the stored procedure.

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 R/3 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 R/3 work process. This waiting for a lock is known as a lockwait. As the number of lockwaits increases, fewer and fewer R/3 user requests can be processed by the available R/3 work processes. In the worst case, that is when the number of lockwaits equals the number of R/3 work processes, a small number of users can cause the entire R/3 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 R/3 work process. The lock holders are displayed furthest left, that is, they are not indented.

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

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 task called SAP CCMS Blocking Lockstats runs once a minute. If blocking lockstats is switched off, this parameter is switched off.

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

The system displays the name of the table where the blocking lock is held, the duration in minutes, and the start date of the blocking situation.

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

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 R/3 for the last 7 days. Count statistics displays a summary of the statistics for all deadlocks since R/3 was installed.

Deadlocks occur far less frequently with SQL Server 7.0 than with SQL Server 6.5. This is because SQL Server 7.0 uses row level locking.

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 \mssql7\log. For example, if SQL Server fails to start, you need to check the error log in that directory.

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

Displays the content of important system tables.

Displays all the SQL Server parameters and change history.

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

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

Calls the CCMS Backup Monitor (transaction DB12).

Here, you can also display the SQL Agent error log.