High wait times for WRITELOG and PAGEIOLATCH_SH

[Former Member]

Hi,

According to the Early Watch Service Report the wait statistics of SQL Server is showing long wait times for wait type(s) PAGEIOLATCH_SH (above 20ms for each I/O) and WRITELOG (above 10ms for each I/O).

Please send us the solution to correct the issue.Looking forward for ur reply.

Thanks & Regards.

Soumya Saha

[Former Member]

Hi Soumya,

Could you please tell me what you did to solve this issue. I am also getting the same error in EWA on a new production system after fresh installation. I am using MS SQL server 2005 sp2 with 2 datafiles of disk size 60GB and one of 50GB and one logfile of size 100gb. Its for SAP NW2004s PI7 system.

Regards and thanks.

Edited by: Basis Consultant on Dec 1, 2008 11:41 AM

[Former Member]

Please read item 3 from the note 987961:

Compared to SQL Server 2000 not only additional data is available (io_stall values for read and write separately) but data are also measured differently. The values in SQL Server 2005 are now closer

to the times observed in the Windows System Monitor.

On some SAP systems times as high as 400 ms/read have been observed without indication of database performance issues.

Nevertheless these counters are very valuable for analyzing I/O performance of SQL Server 2005. But they need to be evaluated taking into account the specific I/O load profile in terms of number and

size of I/O operations. It is reasonable to expect values less then 10 ms/read on data files and less then 5 ms/write on transaction log files.

As stated above values much higher then this do not allow to deduce an I/O bottleneck.

Regards,

Nikunj Thaker.

[Former Member]

Solved

[Former Member]

HI Soumya,

Any specific changes you made, can you please provide us steps you have taken to solve, this could be very helpful to others.

Thanks

Mushtaq

[former_member192334]

Hi Soumya

How did you solve this?

Thamks

[Former Member]

Hi Soumya, how did you solve this problem? I am having a similar problem.

We are running a select query that is taking about 5 minutes on our production system (Win 2003 Ent, SQL 2005 SP4, 32 GB RAM, 4 Gb fiber SAN).

Same query on same database on a single CPU old server with 4 GB RAM, on a USB drive runs instantly. Basic file IO operation on the SAN is 50 times faster than the USB so I am sure its noting to do with disk IO. I even tried replicating ona single core VM and it runs instantly.

Please help.

Thanks

[markus_doehr2]

That can be caused by quite some things - and without knowing anything about your environment (SAN? iSCSI? SCSI?) it´s impossible to suggest anything.

Markus

[Former Member]

Do you have any perfmon data for your system and what application are you running?

Try monitoring the "Page Life Exceptency", if it is a low number, less then 300 you might need more RAM. You also might have high contention on your SAN or HD, how is the system set up? How many data files? What SQL version? What is the SQL DB size?