sql statistics io scan count explanation

Question

Hi,

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?

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

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.