Sql statistics io scan count explanation


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?

Best Solution

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!



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.