sql统计io扫描计数解释

发布于 2024-07-11 11:43:06 字数 151 浏览 5 评论 0原文

很简单的问题，但我在谷歌上没有找到很好的解释。当使用“设置统计 IO ON”时，逻辑读取和扫描计数将在 Management Studio 的消息窗口中提供。如果我有：

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

扫描计数意味着什么？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我爱人 2024-07-18 11:43:06

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

扫描计数简单地表示在查询期间访问表或索引的次数。它可能是完整扫描、部分扫描或只是搜索。

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

另外：

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

不幸的是，现在的扫描计数信息并不丰富。嗯，好吧，
如果您看到像 19223 这样的数字，则该表可能已被访问
通过嵌套循环连接多次。
曾经有一段时间，“扫描计数”仅仅意味着“访问的时间表”，
但那是很久以前的事了，也许是在 SQL 6.5 中。你唯一能得到的时间
定义为 0 的扫描计数是使用像 ... 的查询
<前><代码>选择*
来自测试A1
其中公司 ID = 1
公司 ID = 2
... SQL Server 可以得出查询不会返回的结论
任何行，无需访问表。

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 information to 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

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.

回复收藏 0 原文

征﹌骨岁月お 2024-07-18 11:43:06

来自在线图书

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

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

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

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

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

回复收藏 0 原文

心房的律动 2024-07-18 11:43:06

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

“逻辑读取
该值表示处理查询所需的页面访问总数。每个页面都是从数据缓存中读取的，无论是否需要将该页面从磁盘放入缓存以进行任何给定的读取。该值始终至少与物理读取的值一样大，并且通常大于该值。同一页可以被读取多次（例如，当从索引驱动查询时），因此表的逻辑读取计数可能大于表中的页数。
物理阅读
该值表示从磁盘读取的页数；它始终小于或等于逻辑读取的值。性能监视器显示的缓冲区高速缓存命中率值是根据逻辑读取和物理读取值计算得出的，如下所示：
预读读取
Read Ahead Reads 值指示在处理查询时使用预读机制读入缓存的页数。查询不一定使用这些页面。如果最终需要一个页面，则计算逻辑读取，但不计算物理读取。高值意味着物理读取的值可能较低，并且缓存命中率可能高于... [由 vgv8 截断]
扫描计数
Scan Count 值表示相应表被访问的次数。嵌套循环连接的外部表的扫描计数为 1。对于内部表，扫描计数可能是“通过循环”访问表的次数。逻辑读取的数量由扫描计数乘以每次扫描访问的页数之和确定。但是，即使对于嵌套循环联接，内表的扫描计数也可能显示为 1。SQL Server 可能会将所需的行从内表复制到缓存中的工作表中，并使用该工作表访问实际数据行。当计划中使用此步骤时，STATISTICS IO 输出中通常没有任何指示。您必须使用 STATISTIC TIME 的输出以及有关所使用的实际处理计划的信息来确定执行查询所涉及的实际工作。哈希联接和合并联接通常将联接中涉及的两个表的扫描计数显示为 1，但这些类型的联接可能需要更多的内存。您可以在执行查询时检查 sysprocesses 中的 memusage 值，但与physical_io 值不同，这不是累积计数器，仅对当前运行的查询有效。一旦查询完成，就无法查看它使用了多少内存。”

[1]
第 4 章查询性能故障排除。监控查询性能
Microsoft® SQL Server™ 2005 内部：查询调优和优化
作者：Kalen Delaney

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

[2]
监控查询性能
优化查询性能
作者：罗恩·索库普、卡伦·德莱尼
《Inside Microsoft SQL Server 7.0》第 14 章，由 Microsoft Press 出版
http://technet.microsoft.com/en-us/library/cc917719.aspx#ECAA< /a>

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

"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."

[1]
Chapter 4. Troubleshooting Query Performance. Monitoring Query Performance
Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization
by 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

[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

回复收藏 0 原文