SQL sql统计io扫描计数说明

sql statistics io scan count explanation

提问by Paulj

Simple question, but I haven't found a good explanation on google. When using Set Statistics IO ON, the logical reads and scan count is provided in the message window of management studio. If I have:

简单的问题，但我还没有在谷歌上找到好的解释。使用 Set Statistics IO ON 时，管理工作室的消息窗口中提供了逻辑读取和扫描计数。如果我有：

tblExample, scan count 5, logical reads 20

tblExample，扫描计数 5，逻辑读取 20

What does scan count signify?

扫描计数是什么意思？

采纳答案by SQLMenace

From Books On Line

从在线书籍

Scan count:Number of index or table scans performed.

扫描计数：执行的索引或表扫描数。

logical reads:Number of pages read from the data cache.

逻辑读取：从数据缓存读取的页数。

physical reads:Number of pages read from disk.

物理读取：从磁盘读取的页数。

read-ahead reads:Number of pages placed into the cache for the query.

预读：为查询放入缓存的页数。

See also here: http://technet.microsoft.com/en-us/library/ms184361.aspx

另见此处：http: //technet.microsoft.com/en-us/library/ms184361.aspx

回答by Jeff Atwood

As far as what a "table scan" means, the best I could find is this:

至于“表扫描”的含义，我能找到的最好的是：

Scan count simply means how many times the table or index was accessed during the query.It may be a full scan, partial scan, or simply a seek.

扫描计数仅表示在查询期间访问表或索引的次数。它可以是全扫描、部分扫描或简单的查找。

In other words, scan count alone by itself is not enough informationto proceed. You need to know what those scans were, exactly -- so you'll have to look at the actual execution plan for more detail. Bottom line it's not a very useful metric by itself!

换句话说，仅靠扫描计数本身不足以提供继续进行的信息。您需要确切地知道这些扫描是什么——因此您必须查看实际的执行计划以获取更多详细信息。最重要的是，它本身并不是一个非常有用的指标！

Additionally:

此外：

http://www.eggheadcafe.com/software/aspnet/32171165/set-statistics-io-scan-count-explanation.aspx

http://www.eggheadcafe.com/software/aspnet/32171165/set-statistics-io-scan-count-explanation.aspx

Unfortunately, Scan Count these days is not very informative. Hm, well, if you see a number like 19223, the table has probably be accessed through a nested loop join many times.

There was a time when "scan count" simply meant "times table accessed", but that was long ago, maybe in SQL 6.5. The only time you could get a scan count with that definition of 0 is with a query like ...

select *
from TestA1
where CompanyID = 1
and CompanyID = 2

... where SQL Server could be able to conclude that the query will not return any rows, without accessing the table.

不幸的是，现在的Scan Count 信息量并不大。嗯，好吧，如果您看到像 19223 这样的数字，则该表可能已通过嵌套循环连接访问过多次。

曾几何时，“扫描计数”仅表示“访问表的次数”，但那是很久以前的事了，也许在 SQL 6.5 中。您可以使用该定义 0 获得扫描计数的唯一时间是使用类似...

select *
from TestA1
where CompanyID = 1
and CompanyID = 2

... SQL Server 可以得出这样的结论：如果不访问表，查询将不会返回任何行。

回答by Gennady Vanin Геннадий Ванин

If to continue to collect msdn citations. Then [1] which is repeated in [2]:

如果继续收集msdn引用。然后在 [2] 中重复的 [1]：

• "Logical Reads
  This value indicates the total number of page accesses needed to process the query. Every page is read from the data cache, whether or not it was necessary to bring that page from disk into the cache for any given read. This value is always at least as large and usually larger than the value for Physical Reads. The same page can be read many times (such as when a query is driven from an index), so the count of Logical Reads for a table can be greater than the number of pages in a table.

• Physical Reads
  This value indicates the number of pages that were read from disk; it is always less than or equal to the value of Logical Reads. The value of the Buffer Cache Hit Ratio, as displayed by Performance Monitor, is computed from the Logical Reads and Physical Reads values as follows:

• Read Ahead Reads
  The Read Ahead Reads value indicates the number of pages that were read into cache using the read ahead mechanism while the query was processed. These pages are not necessarily used by the query. If a page is ultimately needed, a logical read is counted but a physical read is not. A high value means that the value for Physical Reads is probably lower and the cache-hit ratio is probably higher than... [truncated by vgv8]

• Scan Count
  The Scan Count value indicates the number of times that the corresponding table was accessed. Outer tables of a nested loop join have a Scan Count of 1. For inner tables, the Scan Count might be the number of times "through the loop" that the table was accessed. The number of Logical Reads is determined by the sum of the Scan Count times the number of pages accessed on each scan. However, even for nested loop joins, the Scan Count for the inner table might show up as 1. SQL Server might copy the needed rows from the inner table into a worktable in cache and use this worktable to access the actual data rows. When this step is used in the plan, there is often no indication of it in the STATISTICS IO output. You must use the output from STATISTIC TIME, as well as information about the actual processing plan used, to determine the actual work involved in executing a query. Hash joins and merge joins usually show the Scan Count as 1 for both tables involved in the join, but these types of joins can involve substantially more memory. You can inspect the memusage value in sysprocesses while the query is being executed, but unlike the physical_io value, this is not a cumulative counter and is valid only for the currently running query. Once a query finishes, there is no way to see how much memory it used."

• " Logical Reads
  此值指示处理查询所需的页面访问总数。从数据缓存中读取每个页面，无论是否有必要将该页面从磁盘带入缓存以进行任何给定读取。此值是总是至少和物理读取的值一样大，而且通常比物理读取的值大。同一个页面可以被读取多次（例如当查询是从索引驱动时），因此表的逻辑读取计数可以大于表中的页数。

• Physical Reads
  此值表示从磁盘读取的页数；它始终小于或等于 Logical Reads 的值。性能监视器显示的缓冲区缓存命中率的值是根据逻辑读取和物理读取值计算得出的，如下所示：

• Read Ahead Reads
  Read Ahead Reads 值指示在处理查询时使用预读机制读入缓存的页数。查询不一定使用这些页面。如果最终需要一页，则计算逻辑读取，但不计算物理读取。高值意味着 Physical Reads 的值可能较低并且缓存命中率可能高于... [被 vgv8 截断]

• 扫描计数
  Scan Count 值表示相应表被访问的次数。嵌套循环联接的外部表的扫描计数为 1。对于内部表，扫描计数可能是“通过循环”访问该表的次数。逻辑读取数由扫描计数乘以每次扫描访问的页数的总和确定。但是，即使对于嵌套循环连接，内表的扫描计数也可能显示为 1。SQL Server 可能会将所需的行从内表复制到缓存中的工作表中，并使用该工作表访问实际数据行。在计划中使用此步骤时，在 STATISTICS IO 输出中通常没有指示。您必须使用 STATISTIC TIME 的输出，以及有关使用的实际处理计划的信息，以确定执行查询所涉及的实际工作。散列连接和合并连接通常将连接中涉及的两个表的扫描计数显示为 1，但这些类型的连接可能涉及更多的内存。您可以在执行查询时检查 sysprocesses 中的 memusage 值，但与 physical_io 值不同，这不是累积计数器，仅对当前运行的查询有效。一旦查询完成，就无法查看它使用了多少内存。” 这不是累积计数器，仅对当前运行的查询有效。一旦查询完成，就无法查看它使用了多少内存。” 这不是累积计数器，仅对当前运行的查询有效。一旦查询完成，就无法查看它使用了多少内存。”

[1]
Chapter 4. Troubleshooting Query Performance. Monitoring Query Performance
Inside Microsoft? SQL Server? 2005: Query Tuning and Optimization
by Kalen Delaney

[1]
第 4 章查询性能故障排除。
在 Microsoft 内部监控查询性能？SQL服务器？2005 年：
Kalen Delaney 的 查询调优和优化

---

---

Publisher: Microsoft Press
Pub Date: September 26, 2007
Print ISBN-10: 0-7356-2196-9
Print ISBN-13: 978-0-7356-2196-1
Pages: 448

出版商：Microsoft Press
出版日期：2007 年 9 月 26 日
印刷 ISBN-10：0-7356-2196-9
印刷 ISBN-13：978-0-7356-2196-1
页数：448

[2]
Monitoring Query Performance
Optimizing Query Performance
By Ron Soukup, Kalen Delaney
Chapter 14 from Inside Microsoft SQL Server 7.0, published by Microsoft Press
http://technet.microsoft.com/en-us/library/cc917719.aspx#ECAA

[2]
监视查询性能
优化查询性能
Ron Soukup，Kalen Delaney
第 14 章来自 Inside Microsoft SQL Server 7.0，Microsoft Press 出版
http://technet.microsoft.com/en-us/library/cc917719.aspx#ECAA

回答by Martin Smith

Quite a comprehensive summary of this here.

这里有一个相当全面的总结。

• "SCAN COUNT meaning in SET STATISTICS IO output" - Part 1
• "SCAN COUNT meaning in SET STATISTICS IO output" - Part 2

• “SET STATISTICS IO 输出中的 SCAN COUNT 含义” - 第 1 部分
• “SET STATISTICS IO 输出中的 SCAN COUNT 含义” - 第 2 部分

回答by Irawan Soetomo

Ignore Scan Count, it is not important. Focus on how to lower Logical Reads. Based on http://www.practicalsqldba.com/2013/07/sql-server-performance-tuning.html.

忽略扫描计数，这并不重要。专注于如何降低逻辑读取。基于http://www.practicalsqldba.com/2013/07/sql-server-performance-tuning.html。