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?

Answer 1

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.