Выясняем причину длительных ожиданий IO_COMPLETION и WRITE_COMPLETION

Автор: Paul Randal, Causes of IO_COMPLETION and WRITE_COMPLETION SQL Server wait types

Во многих наборах статистики ожиданий, которые я анализировал, появляются ожидания IO_COMPLETION и WRITE_COMPLETION (но никогда как самый распространённый тип ожидания).

Официальные определения этих типов ожиданий:

• IO_COMPLETION: Возникает в ожидании завершения операций ввода-вывода. Этот тип ожидания, как правило, представляет операции ввода-вывода, не связанные со страницами данных. Ожидания завершения ввода-вывода страниц данных отображаются как ожидания PAGEIOLATCH_*.
• WRITE_COMPLETION: Возникает, когда выполняется операция записи.

Я обещал многим людям, которые недавно присылали мне статистику ожиданий, написать статью в блоге с более подробной информацией о том, когда возникают эти типы ожиданий, так что вот он.

Я использовал код Extended Events из моей статьи How to determine what causes a particular wait type, чтобы отслеживать возникновение этих типов ожиданий, затем выполнил ряд различных операций и проанализировал стеки вызовов. Случаев слишком много, чтобы задокументировать их все здесь, поэтому я обобщу свои выводы ниже.

Обратите внимание, что эти списки не являются исчерпывающими, но вы поймёте, о каких видах операций идёт речь.

IO_COMPLETION

• Чтение блоков журнала из журнала транзакций (во время любой операции, которая вызывает чтение журнала с диска, например восстановление).
• Чтение битовых карт выделения с диска (например, страниц GAM, SGAM, PFS) во время многих операций (например, восстановление, запуск БД, восстановление из резервной копии).
• Запись промежуточных буферов сортировки на диск (они называются «Bobs»).
• Чтение и запись результатов слияния с/на диск во время соединения слиянием.
• Чтение и запись eager spools на диск.
• Чтение заголовков VLF из журнала транзакций.

WRITE_COMPLETION

• Запись любой страницы в снимок базы данных (например, во время выполнения DBCC CHECK*, что часто является самой распространённой причиной этого типа ожидания).
• Запись заголовков VLF при создании или увеличении файла журнала транзакций.
• Запись страницы заголовка файла на диск.
• Запись частей журнала транзакций во время запуска базы данных.
• Запись страниц выделения на диск при создании или увеличении файла данных.

Это не те ожидания, о которых я бы обычно беспокоился, и я ожидаю, что отдельные времена ожидания ресурсов будут соответствовать задержкам чтения и записи экземпляра.