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 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 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.

An SAP System with SQL Server executes ABAP SQL statements as stored procedures or as dynamic statements.

Each SAP server has a stored procedure name cache in the SAP System. This stored procedure name cache can contain statistics about stored procedures and dynamic statements.

The name cache stats are now always displayed on the main screen where t. All application servers are listed on the left of the screen. When you double-click one of the application server, its name cache stats appear on the right hand side of the screen. If you select an application server and choose SP name cache, the name cache stats are also displayed. If you have a central system only one server is displayed. To switch name cache statistics collection on or off, choose SP stat on/off.

Choose SP statistics to display the stored procedure statistics. Each stored procedure 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.

To display the source text of a stored procedure, select the line with the stored procedure and choose SQL statement. Choose SQL 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 and dynamic statements.

If there is already an execution plan in the procedure cache you can also choose SP.

Choose ABAP code to see the ABAP coding.

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 I/O wait time per file, which is an important indicator for the performance of the IO system is displayed.

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 Stats on SP that provide you with a detailed analysis of a statement. 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.

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 new or stored execution plan by using SP Explain or SQL Explain.

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, fewer and fewer SAP user requests can be processed by the available SAP work processes. 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. 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 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.

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 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.

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 error log here.

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, database options 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 monitor for Backup and Restore Information (transaction DB12).

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