SQL Server IO_COMPLETION 和 WRITE_COMPLETION 等待的原因
SQL Server IO_COMPLETION 和 WRITE_COMPLETION 等待的原因
http://www.sqlskills.com/blogs/paul/causes-io_completion-write_completion-sql-server-wait-types/
In many of the sets of wait statistics I’ve been analyzing, the IO_COMPLETION and WRITE_COMPLETION waits show up (but never as the most prevalent wait type).
The official definition of these wait types are:
- IO_COMPLETION: Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.
- WRITE_COMPLETION: Occurs when a write operation is in progress.
I promised many of the people who sent me wait statistics recently that I would write a blog post giving more detailed information on when these wait types occur, so here it is.
I used the Extended Events code in my post How to determine what causes a particular wait type to watch for these wait types occurring and then ran a variety of operations and analyzed the call stacks. There are way too many occurrences to document them all here, so I’ll summarize my findings below.
Note that these are not lists are not exhaustive, but you get the idea of the kinds of operation where these wait types occur.
IO_COMPLETION
- Reading log blocks from the transaction log (during any operation that causes the log to be read from disk – e.g. recovery)
- Reading allocation bitmaps from disk (e.g. GAM, SGAM, PFS pages) during many operations (e.g. recovery, DB startup, restore)
- Writing intermediate sort buffers to disk (these are called ‘Bobs’)
- Reading and writing merge results from/to disk during a merge join
- Reading VLF headers from the transaction log
WRITE_COMPLETION
- Writing any page to a database snapshot (e.g. while running DBCC CHECK*, which is often the most common cause of this wait type)
- Writing VLF headers while creating or growing a transaction log file
- Writing a file’s header page to disk
- Writing portions of the transaction log during database startup
- Writing allocation pages to disk when creating or growing a data file
These aren’t waits that I’d generally be concerned about, and I’d expect the individual resource wait times to be in line with those of the read and write latencies of the instance.
Enjoy!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战