4

Could someone explain what is the main difference between these two wait types?

1 Answer 1

4

I think you should first understand the difference between Sync IO and Async IO. The information about the basic nature of both the I/O's can be found in Bob Dorr's I/O presentation blog see the section Async vs Sync IO.

In very simple meaning Async IO is one in which after putting I/O request the program or calling code will not wait for the I/O operation to complete but will get busy with other task, later it can come and check and see if the I/O request has been completed or not. Similarly for Sync IO the calling code will wait for acknowledgement that I/O request is done. Quoting from the Bob Dorr's blog

In Windows the I/O APIs allow sync and async requests. Sync requests are calls to the API such as WriteFile that will not return control to the calling code until the operation is complete. Async hands the request off to the operating system and associated drivers and returns control to the calling code. The calling code is free to execute other logic and later come back to see if/when the I/O completes.

SQL Server uses mostly Async I/O patterns. This allows SQL Server to write or read a page and then continue to use the CPU and other resources effectively. Take the example of a large sort operation. SQL Server can use its read ahead logic to post (async request) many pages and then start processing the first page returned by the request. This allows SQL Server to use the CPU resources to sort the rows on the page while the I/O subsystem is fetching (reading) in other pages at the same time. Maximizing the I/O bandwidth and using other resources such as CPU more effectively.

Now the wait time here represents time spent in waiting Async/Sync IO request to complete.

1
  • I don't understand the example of a sort operation as a Async I/O call, since sort is a blocking operation and needs all the data first to be able to sort it. As it is described here, it sounds like the SQL Server places read requests and starts to sort every page returned while is waiting for the remaining ones. Commented Oct 20, 2023 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.