IO-related wait types in SQL Server: Io_completion and pageiolatch_*

Source: Internet
Author: User

Source: IO-related wait types in SQL Server: Io_completion and pageiolatch_*

A large SQL statement operation, the execution plan contains a merge join operation, observed that SQL long time in the io_completion wait state, if it is to read the related table data, the server should do its best to service, but the server's physical IO is far from reaching the bottleneck.
What the hell is this io_completion doing? Is the table's data page IO request still in other operations? If so, what's the difference with pageiolatch_*? If not, what type of operation is it?

Io_completion This wait type represents a variety of synchronous read and write operations in the data file that are not related to the table and are read from the transaction log.
Occurs while waiting for the I/O operation to complete. This wait type typically represents non-data page I/O. Data page I/O completion waits as pageiolatch_* waits to appear.
The biggest difference between io_completion and pageiolatch_* is that io_completion is a non-data page wait, and pageiolatch_* is the IO waiting for the data page.
Io_completion This wait type represents a variety of synchronous read and write operations in the data file that are not related to the table and are read from the transaction log.

The following scenarios are included:
1. Read log blocks from the transaction log (during any operation that causes the log to be read from disk-for example, recovery)
2. In many operations, such as recovery, DB startup, read from disk allocation bitmap (e.g. GAM, SGAM, PFS page) during recovery
3. Write intermediate sort buffers to disk (called "Bobs")
4. During the merge join, the result of writing to disk or reading the merge join on disk
5. Read-write eager spools (a behavior of data caching to disk) to disk
6. Read the VLF header information from the transaction log.

Reference: https://www.sqlskills.com/help/waits/io_completion/

IO-related wait types in SQL Server: Io_completion and pageiolatch_*

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.