查询优化和索引是减少物理和逻辑磁盘 I/O 的有效途径。
摘自"SQL 服务器 DMV 起动包、"由红门书 （2010 年） 出版。
Glenn Berry, Louis Davidson 和 Tim Ford
有持续性的需要，尽量减少逻辑和物理 I/O。 O 相关的数据库管理对象 （目的地） 的集合可以帮助调查，具体来说，您的系统上进行的数据写入并从磁盘中读取时的物理 I/O。
在此类别中的旅游景点管理组织提供从磁盘子系统的磁盘 I/O 显式图片。 他们向我们展示，例如，如何将 I/O 分布在各种文件在磁盘上放置其中 I/O 是成为性能瓶颈，从而导致 I/O 档位，依此类推。 您可以使用此信息来优化磁盘子系统的体系结构。 此外可以收集数据并使用它来支持更多存储容量对业务部门领导的请求。
当然，一些物理 I/O 是不可避免的。 SQL Server 必须将应用程序数据写入磁盘。 它也必须写入到事务日志中为每个插入、 更新和删除、 甚至为大容量操作。 然而，你只是需要更多磁盘电源的结论之前，记住有很多您可以在查询优化和索引，尽量减少不必要的逻辑和物理 I/O。
您应该考虑从 DMOs 派生的 I/O 信息涵盖在这里 （所有哪些开始与"sys.dm_io_"），以及从其他动态管理视图 (Dmv) 引用 I/O 性能以某种方式的数据包括：
- sys.dm_exec_query_stats — — 一个给定的查询已过去它已经执行的时间成本的 I/O
- sys.dm_exec_connections — — 在该连接发生的 I/O
- sys.dm_exec_sessions — — 已采取的 I/O 放置在该届会议期间
- sys.dm_os_workers — — 一个给定的工作线程挂起 I/O
这一节中的查询的所有与 SQL Server 2005 中，2008 年和 2008 R2，和所有需要查看服务器状态的权限。
调查 I/O 档通过磁盘瓶颈
我们将在这里使用的 DMV 是 sys.dm_io_virtual_file_stats，SQL Server 联机丛书描述为："返回的数据和日志文件 I/O 统计信息。 此动态管理视图取代 fn_virtualfilestats 函数"。
此 DMV 接受两个参数：database_id 和 file_id。 您可以为任一指定 NULL。 在这种情况下，它将返回信息的所有数据库上的所有文件。
请注意此 DMV 累积。 换言之，数据列中的值递增不断从点时，服务器上次重新启动。 这意味着您需要采取基准测量，跟实际测量。 然后减去两个，以便您可以看到那里积累 I/O。
此脚本，您可以看到的读取的数量，并写上每个数据库的 SQL Server 实例上运行的每个数据和日志文件。 它被按平均 I/O 档时间以毫秒为单位）：
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
此查询将显示您等待磁盘 I/O 的时间最长的文件。 它可以帮助您决定在哪里可以找到基于您可用磁盘资源的单个文件。 此外可以使用它来帮助说服别人像 SAN 工程师，SQL Server 看到磁盘瓶颈的某些文件。
调查通过挂起 I/O 磁盘瓶颈
这为调查磁盘 I/O 瓶颈采用稍有不同的方法。 使用 sys.dm_io_pending_io_requests DMV，SQL Server 联机丛书描述为："在 SQL Server 中返回的每个挂起的 I/O 请求的行"。
DMV 中的数据执行脚本的时候您的系统上提供 I/O 请求挂起的"时间点"的快照：
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs. num_of_reads , fs. num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
因为此数据表示活动的时间点快照，您需要多次运行此查询以查看是否相同的文件 （和相同的驱动器号） 显示一贯在列表的顶部。 如果发生这种情况，则该特定的文件或驱动器号的 I/O 瓶颈的证据。 您可以使用此帮助说服您的 SAN 系统工程师正在遇到 I/O 问题的特定 LUN。
最后两个列在查询中的返回的读取和写入文件的累计数目自启动 SQL Server （或因为在创建该文件 — — 时间较短者为准）。 试图决定哪些 RAID 级别使用的特定驱动器号时，此信息很有用。 例如，带有更多写活动的文件将通常更好地对执行 RAID 10 LUN 比他们会在 RAID 5 LUN 上。
了解每个文件的相对读/写比率可以帮助您将您适当的 LUN 上的数据库文件。 这一点，反过来，将帮助您优化您的查询，以提高效率。
.png)
.png)
.png)
Glenn Berry 在丹佛，科罗拉多州 NewsGator 技术工作作为数据库架构师 他是 SQL 服务器 MVP，并且具有微软认证，包括 MCITP、 MCDBA、 MCSE、 MCSD、 MCAD 和 MCTS，这证明了他喜欢参加测试的整个集合。
Louis Davidson 已在 IT 业 16 年作为公司的数据库开发人员和架构师。 他六年来一直 SQL 服务器 Microsoft MVP，写了四本书的数据库设计。 目前他是数据架构师和有时 DBA 支持办事处在弗吉尼亚州弗吉尼亚海滩和位于田纳西州的纳什维尔的基督教广播网络
Timothy Ford 我s SQL 服务器 MVP 和与 SQL Server 工作 10 年以上。他是主 DBA 和 SQL Server 平台的频谱保健的主题事项专家。他曾是自 2007 年以来的各种 Web 站点有关技术写作并维护自己在博客上的 thesqlagentman.com，以及远程办公和专业发展主题作为覆盖 SQL。**
了解更多有关"SQL 服务器 DMV 起动包"在 red-gate.com/our-company/about/book-store。