Alerts for SQL Server
Alerts for the database server indicate when threshold values that have been defined for the SQL Server have been exceeded. Existing threshold values reflect SAP recommendations for the database server, however they can be adjusted to meet individual system requirements.
See alsoViewing and Customizing Threshold Values.
Use
The database alerts, together with the SAP monitoring functionality, help to optimize database administration. They reduce the amount of work involved by routinely checking the system for significant events and drawing attention to problems as soon as they arise. The amount of time that has to be invested for routine checks is significantly reduced and system inconsistencies can be eliminated more quickly.
Alerts marked in red indicate that there is a system problem that must be rectified. Alerts marked in yellow are warnings that indicate system events that might develop into problems later, if they are ignored.
Structure
The database alerts are displayed in the SAP Alert Monitor for the SQL Server. Together with other essential system information, the alerts are presented in the form of a tree that is subdivided into the following main categories:
- Space Management
- Performance
- Backup Restore
- DDIC Consistency
- Health
The information provided can be viewed from different perspectives that offer varying levels of detail. The following tables summarize the information that is displayed in the Current Status view, which is active when you initially access the monitor. This view displays only the most recent alerts.
For information on other views, seeThe SQL Server Alert Monitor.
To find out the threshold values, which trigger alerts in the system, seeViewing and Customizing Threshold Values.
- To find out the meaning of the various icons on the screen choose Extras → Legend.
- To display the threshold values that trigger alerts, select an alert, press the right mouse button and choose Properties.
Space Management
Status information and alerts related to the disk space that is available for the server are displayed for both the <SAPSID> Database and the tempdb database. For each physical database file, you can check the following by expanding the tree nodes:
| Alert | Information Displayed |
|---|---|
|
Autogrowth |
Autogrowth setting. When autogrowth is enabled, the preallocated file size is automatically increased by a predefined number of megabytes, which can be expressed as a percentage, as required. If the file, and all files on the device, have enough room to grow, this alert is normal |
|
Stats in MB |
Space currently allocated for the file. |
|
Space already used for the file. |
|
|
Growth setting, which is active. This is the amount of space automatically allocated to the file when it fills up. |
|
|
Free disk space |
The amount of free space left on the device where the file is located. |
|
Free space in file |
The percentage of free space left in the file |
The values are refreshed every 8 hours.
Double-clicking one of the alerts takes you to the SAP Monitor screen Space → Overview in the DBA Cockpit (transaction DB02).
Performance
Factors, which influence database performance and are automatically checked by the alert monitor are disk I/O, cache hit ratios and CPU usage.
| Alert | Information Displayed |
|---|---|
|
I/O |
The millisecs/access for each file is also shown. The values shown represent the performance of the disk over the last 20 minutes, which corresponds to the frequency with which the sap_perf job is run. The kB Read/sec is also shown some there is some context to the access statistics. |
|
CPU |
SQL batches/se are shown, along with the Wrt Log ms/request, Oldest blocked Spid, Oldest transaction, and the Percentage of CPU used by the SQL Server. The alert indicates when the SQL Server is reaching the limits of the CPU capacity available for it. For more information, double-click to open the Database Performance Analysis transaction. Click Current Activity. |
|
Data hit ratio |
The total number of requests satisfied by the data cache divided by the total number of data cache accesses. For more information double-click to open Database Performance Analysis. Click Detail Analysis Menu → Server Details and then look under Cache Detail. |
|
Procedure hit ratio |
The total number of requests satisfied by the procedure cache divided by the number accesses of the procedure cache. To find out more, double-click to open Database Performance Analysis. Click Detail Analysis Menu → Server Details and then look under Cache Detail. |
|
Most expensive statement |
If any of the other performance nodes result in a red alert, then the most expensive statement being executed at the time of that alert is querried to determine what percentage of the performance problem is attributable to this expensive statement. |
Values are refreshed every 4 to 15 minutes.
Backup Restore
Information in this category assists the database administrator in determining whether the backup strategy is being implemented successfully.
| Alert | Information Displayed |
|---|---|
|
DB status |
Days since last backup. |
|
master DB status |
Days since last backup. |
|
msdb DB status |
Days since last backup. |
|
Backups running |
Backups currently in progress. |
Backup dates are refreshed once a day. The transaction log backup is checked every 4 minutes.
Double-clicking on an alert accesses the Backup and Recovery → Overview Monitor in the DBA Cockpit (transaction DB12).
DDIC Consistency
This category of information shows the objects that are defined in the ABAP Dictionary, but do not exist on the database.
| Alert Item | Information Displayed |
|---|---|
|
Tables |
Number of tables defined in the ABAP Dictionary that do not exist on the database. |
|
Views |
Number of views defined in the ABAP Dictionary that do not exist on the database. |
|
Indexes |
Number of indexes defined in the ABAP Dictionary that do not exist on the database. |
Values are refreshed once a day.
Double-clicking on an alert accesses the Space Overview Monitor in the DBA Cockpit (transaction DB02). For details, click DB Analysis → Database <-> ABAP Dictionary → Display.
Health
Monitoring the effectiveness of parameter settings and watching out for a number of common errors can judge the health of the system.
| Alert | Information Displayed |
|---|---|
|
SQL Server Build Data |
The alert shows the build number, whether SQL Server is clustered, and if AWE is enabled. |
|
OS Build Data |
The alert shows the Windows build, the OS version and the chip type. |
|
SQL Server trace flags |
Trace flags that have been set. Trace flags can be used to analyze different aspects of the SQL Server. |
|
SQL Server memory setting |
The type of SQL server memory setting that is active. Memory can have a fixed setting or vary within predefined limits. |
|
SQL Server Setup |
A variety of conditions are checked including SQL Server configuration, update table customization, and usage of dbsl dll file version. |
|
Disk I/O |
Number of disk read or write errors that have been detected. |
|
Network packet |
Errors detected during the transmission of data packets through the network. |
|
Error log messages |
Total number of error messages that have a severity level of 17 or higher. The system administrator must resolve errors of these levels. To view logs, double-click to open Database Performance Analysis. Click Detail Analysis Menu → Error Logs. To view a log, select it, enter the no. of lines to read and press Enter. |
|
Error log last 24 hours |
This alert is fired if anerror of severity level of 20 or higher occurs, or a backup fails, within the last 24 hours. If an autoreaction method is required for events in the error log, this alert should be used. |
Double-clicking on an alert takes youDatabase Performance Overview (transaction ST04).