An I/O latch wait occurs when a page is accessed for reading or writing but the page is not available in the buffer pool. It causes waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request. These wait types can indicate an I/O bottleneck. You can query the sys.dm_os_wait_stats DMV to find latch wait statistics. You can identify I/O problems if you save query outputs of waiting_task_counts and wait_time_ms values from a normal working state of your SQL Server and compare these values when performance is degrade

select *
from sys.dm_os_wait_stats  
where wait_type like 'PAGEIOLATCH%'
order by wait_type asc

Disk Configuration / Best Practices

As a general rule, you should have log files on a physical disk that is separate from the data files for better performance.  The I/O profile for a heavily used database data files (including tempDB) are random. The I/O profile for all database log files are sequential except when a transaction needs to be rolled back.

The internal disks should only be used for database log files, because they excel at sequential I/O, but perform poorly on random I/Os.

The database data and log files should each be placed on their own dedicated disk packs. To ensure optimal performance, I recommend that the database log file be placed on two internal disks configured as RAID 1.  The database data file should reside on a SAN system which is only accessed by SQL Server and has controlled querying and reporting.  Ad hoc access should be disallowed.

Write caching should be enabled where possible and you should make sure the cache is protected from power failures and other possible failures.

To limit possible I/O bottlenecks for your OLTP system you should not mix OLAP and OLTP environments.   Also, make sure your code is tuned and create indexes where necessary to avoid unnecessary I/Os.


Last modified: June 3, 2019



Write a Reply or Comment