Functionality
All functionality is accessible through report /SDF/RSMSSVSE.
Collecting wait Event History
By scheduling a background job, the user can collect SQL Server wait events. The default is continuous monitoring by virtue of hourly samples (job name EarlyWatch_dbmon). Apart from this wait events can also be collected for a specific time frame with a specified sampling interval (the naming convention suggested is EarlyWatch_dbmon_<mm><dd>_<hh><mm>, where start date and start time are all a part of the job name).
The Data is stored in table MONI. Data which is older than a quarter of a year is deleted using the standard reorganization of table moni (for details see doc 12103 report RSSTAT60 and in SE11 domain SAPWLREORT; in table SAPWLREORG the relevant entry is WAIT++++<Date__>++++++ 'SQL Server WAIT STATS').
Data can be collated for the local system or a remote SAP system accessible with a RFC connection. If needed, this can be used ST-PI version is not yet available on a SAP system. You can then use e.g. your Solution Manager system for collecting wait events remotely. This can also be utilized for monitoring many systems from a central monitoring system.
Report /SDF/RSMSSVSH only collects one snapshot. This report in utilized in the regular background job.
Displaying Wait Event History
- For every time interval, as per the wait time, the wait events are ordered. Subsequently the 10 events which have the highest wait time are visible for the interval.
- Each interval is displayed in one 'row' which comprises of 4 lines showing the wait type, the average wait time per request the wait time in milli seconds, the wait time as a percentage of the total wait time. Two screens utilize this kind of display:
- An overview of daily averages for all days where data is available can be seen in 'Summarized by day'.
- All samples for one day can be seen in 'Summarized Wait Statistics for Selected Day”. For the default job these are hourly averages.
- Wait events can be seen in the SQL Server through dbbc sqlperf(waitstats). The new preferred data provider which is the dynamic management view sys.dm_os_wait_stats is visible in SQL Server 2005.
The table listed below summarizes nomenclature.
| Term | Corresponding value in sys.dm_os_wait_stats | Different term in dbbc sqlperf(waitstats) |
| Event | wait_type | |
| Total waits | waiting_tasks_count | requests |
| Signal wait time(ms) | signal_wait_time | |
| Total Wait Time(ms) | wait_time_ms - signal_wait_time | |
| Avg. wait. (micro s) | wait_time_ms * 1000 / waiting_tasks_count |
Please doc that the average wait time is displayed in micro seconds instead of milli seconds which allow to show an integer value.
'Details of Two Subsequent Samples' displays the raw data for one sample interval: which is the difference between both samples, the sample of the start time along with the sample of the end time.
Exclude Idle Events
The wait events which occur when SQL Server is not processing requests are referred to as the Idle events. A classic example of this are the wait states of system processes like the LAZYWRITER_SLEEP.
It is useful not to include the Idle events from the display as these are of no interest in the wait event analysis.
Grouping Wait Events Into Wait Event Classes
The number of wait is events is generally quite large. The results can be displayed by the wait event classes, for making the analysis easier. A wait event class generally comprises of all wait events which are related to wait types of a similar nature. For instance, all lock waits (LCK_M_*) are put together into into class LOCK, all latch waits (LATCH*) are combined in class LATCH. All the idle events are put together in class IDLE. In a SAP system, there is a large class 'Irrelevant' of wait types and either these don’t occur at all or are not relevant for the wait event analysis.
In class 'no_wait_event' data is stored which does not match the wait types and is not visible in the analysis: 'Total' is the sum of the wait event statistic as shown in dbcc sqlperf(waitstats). '_CPU_busy_idle_io' is utilized for storing the CPU usage data.
Collecting SQL Server CPU Usage
- The CPU usage of the SQL Server process is combined together with the wait events. (SQL Server counters @@cpu_busy, @@idle, @@io_busy.)
- This functionality is unavailable on all SAP releases, when collecting data remotely.
- The SQL Server CPU Usage data is only visible through the download functionality.
Download (File Export) of Wait Event History and SQL Server CPU Usage
All wait event and CPU usage data collected in a certain time interval are all shown in a table and can also be downloaded to the frontend PC. In the spreadsheet tools, raw data is provided which can be to be evaluated.
Customizing Wait Event Classes
- The user can utilize the same for displaying the preconfigured classifications of wait types.
- Besides this the user can also switch between the existing classifications.
- An advanced feature which offers additional flexibility for experienced used is the creation of a new classification. This features makes it possible for the users to define their own event type classes and also use for displaying data.