sql statistics io scan count explanation

Question

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:

tblExample, scan count 5, logical reads 20

What does scan count signify?

Tebet

The docs are a little bit confusing but have a good description: learn.microsoft.com/en-us/sql/t-sql/statements/… – Reliable

Answer 1

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

Erick

Answer 2

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.

Phillada

You can get a scan count of 0 and return a row if you are doing a look up against a unique index. e.g. SET STATISTICS IO ON; SELECT * FROM [master].[dbo].[spt_values] WHERE type='A' AND number=1 AND name='rpc' This is documented here – Reckon

Answer 3

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

Ligure

Answer 4

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

Reckon

Answer 5

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.

Nepotism