Write Log Errors

[Former Member]

Hi All,

We have a SAP 4.7/windows 2000 server/SQL Server 2000.

We are getting error in Early Watch report

<b>WRITELOG 13654257 1047443 13,04 6,00 </b>

Recommendation from SAP:

The table above shows very high wait times for wait type(s) PAGEIOLATCH_SH (above 20ms for each I/O) and/or WRITELOG (above 10ms for each I/O). These figures have been calculated from data recorded since your last database reboot. We therefore recommend that you observe these values over a longer period of time. If you do not notice an improvement, this indicates a severe performance problem (see SAP Note 521750).

Recommendation:

Use a transaction log of sufficient size so that no growth occurs. Also check the I/O subsystem for possible improvements.

We have increased the log size but the problem persists.

Can you suggest what more can be done and also how to check PAGEIOLATCH_SH & WRITELOG

Regards

Abhishek

[former_member239282]

Hi Abhishek,

the current database activity can be observed in ST04 ->'Detail analysis

menu' -> 'SQL processes'.

To find processes waiting on I/O order by column 'Waittime' descending. Those processes with 'Waittime' <> 0 are currently waiting (ignore system processes).

Column 'SQL wait resource' displays what the processes are waiting on.

PAGEIOLATCH_SH or PAGEIOLATCH_EX indicates waiting on I/O on data files. For an indepth analysis the wait resource can be used to identify the file and the database object where the wait occurs. WRITELOG indicates waiting on I/O on transaction log files.

From the output of sqlperf(WAITSTATS) you have to divide WAITTIME by REQUESTS. For these quotients the following reference values hold for the average value since SQL Server start:

PAGEIOLATCH_SH: < 20 ms/request

Typical values are in the range of 4 to 10 ms/request.

WRITELOG: < 10 ms/request

Typical values are in the range of 1 to 5 ms/request.

I/O analysis can be complicated due to the many layers involved. This includes the hardware (e.g. disks, host adapters, caches, lines, and controllers), software (firmware, drivers, Windows operating system, and the database SQL Server), and their configuration. For most components the hardware partner is the best contact.

General database tuning may reduce the I/O load.

SQL Server 2000 doesn't have configuration parameters relevant for I/O. Therefore the administrative task reduces to a sensible configuration of the database files. When SAP brought its product on SQL Server hardware and operating systems were already advanced enough, to support a very simple implementation: all tables are put into one large pool, which is distributed over all database files. This is fully managed by SQL Serveralone.

Therefore SQL Server installations do not suffer from hot spots caused by misconfiguration of table storage. This eliminates a layer typically critical for the I/O performance of RDBMS.

The database administrator has to follow only few simple rules:

- No data file must be full.

- The free space within the data files is spread evenly over all files (more precisely it is sufficient to spread it evenly over all available I/O channels - where the meaning of 'I/O channel' depends on the hardware).

• if it helpful reward points are appreciated

Message was edited by:

Pierluigi Demaria