Usage
Collecting Wait Event History of the Local Database
The user Runs the report /SDF/RSMSSVSE, by selecting 'Scheduling data collection'. In the upper portion of the screen, the default job can be scheduled by collecting hourly samples.
The job only get created and released incase the user is authorized to do the same.
Monitoring a specific time interval:
The user will be able to schedule a job collecting wait events for the specified time window with a shorter sampling interval, in the bottom portion of the screen.
Collecting Wait Event History of a Remote System
A RFC connection to the remote system needs to exist (maintenance in transaction SM59).
Create a variant of report /SDF/RSMSSVSH:
Run transaction SE38.
Enter /SDF/RSMSSVSH
Choose 'Variants' -> Change -> Create.
Enter a variant name, e.g. the SID of the remote system.
On the screen 'Maintain Variant' mark 'data of RFC destination system' and enter the name of the RFC destination.
Proceed to the next screen choosing 'Attributes'. Enter a description and save.
The Variant will look like this:
Objects for selection screen 1000
data of local system
data of RFC destination system X
RFC dest. CT5
data of multi-connected system
DBCON C62
Schedule a job:
User runs the transaction SM36, by entering a job name, e.g. EarlyWatch_dbmon_<SID>. User creates a step for ABAP program /SDF/RSMSSVSH and also specifies the variant which was created earlier. User needs to enter the Start Condition using Date/Time; mark Periodic job.
Displaying Wait Event History
Run report /SDF/RSMSSVSE and execute (F8).
The top ten wait types for all days is the place where data are available can be seen on the first screen 'Summarized by day' .
This screen can be used for comparing days.
| 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 note that the average wait time is displayed in micro seconds instead of mili seconds which displays an integer value.
On this screen, the user can customize the display in two ways by utilizing the menu buttons: The user can exclude or include the idle wait types from the analysis. The events are excluded, by default as they are appropriate for analysis. Secondly, the user can either only display the wait event classes or show the wait types itself. SQL Sever terminology is the default is the wait types. The display by classes can be used for quickly narrowing down the type of wait events appropriate.
For showing the details of a single day, the user moves the cursor into the row of this day and selects the 'Drill down'. The user arrives on the screen 'Summarized Wait Statistics for Selected Day'. The top ten wait types are displayed for all sample intervals of this day. Please note that the settings for idle events and classes are typically inherited from the 'Summarized by day' screen.
For displaying the raw data for one sample interval the user puts the cursor on the row of this interval and selects 'Drill down'.
Download (File Export) of Wait Event History and SQL Server CPU Usage
- By the download functionality, the user can access the collected CPU usage data. By this the raw data collected in a specific time interval can be displayed. Raw data can also be downloaded to the frontend PC.
- On the entry screen 'Summarized by day' selects the 'Download'. The user enters the time frame and executes.
- (Data are shown alternatively as plain list or in a formatted table (ALV). By selecting the format which is most relevant for further processing.)
Customizing Wait Event Classes
The user selects the 'Customizing of event classes', on the selection screen of report /SDF/RSMSSVSE. Four classification schemes are delivered:
SQL10 and SQL10_32CHARONLY (ST-PI 2008_1_* and later) : suitable for SQL Server 2008. In the section, 'Known Issues', the user can see the reason for introducing a second scheme SQL10_32CHARONLY has been described.
DEFAULT : Appropriate for SQL Server 2000 and 2005. Incase no other customizing has been chosen manually DEFAULT is used. For SQL Server 2008 the corresponding customizing should be selected.
SQL7 : suitable for SQL Server 7.
With SQL Server 2008 the number of various wait events has significantly gone up. Therefore, the grouping into 'event classes' is more valuable due to the fact that it simplifies getting an overview in the areas in which the most wait time takes place.
Select a scheme for data display:
By selecting,” Choose”, the user can activate a classification scheme which can be utilized in the data and this displays the mark line along with the customizing.
Create a new scheme:
- The user can create his own classification scheme, in case the classification of some wait type is not appropriate for the specific analysis.
- The following procedure is suggested: For SQL Server 2008 start with one of the tab which has delimited text files attached to this note. As an Alternate the scheme which fits to your SQL Server release is displayed which fits to your SQL Server release, e.g. the DEFAULT scheme in case the user has SQL SERVER 2005. By using the spreadsheet format, the user can download the scheme to a local file (CTRL+Shift+F9).
- The user can modify the file as per your needs. The user can also reassign wait types to different classes. Besides creating new classes and also assigning the wait types to them. For the new wait types, the user can also create entries.
- For uploading your classification scheme the user selects the 'Import' function (Shift+F5) available in ST-PI 2008_1_*. This 'Import' function expects a tab delimited text file which can be created from the spread sheet program.On ST-PI below ST-PI 2008_1_*:
- The user selects 'Create' and enters a name for uploading your classification scheme. Then copies the list of wait types into the clipboard, by selecting the arrow next to 'List of wait types', by Choosing 'Upload from clipboard'. User repeats these steps for the list of classes and executes.