SQL Server - SET STATISTICS IO "SCAN COUNT" explanation

Asked By serge on 10-May-08 01:32 PM
/*
You can paste this whole message in a new query window
and run it if you wish. I am using SQL 2005 SP2.

I am trying to understand the exact definition of Scan Count.
SQL 2005 Books Online for SET STATISTICS IO says:
Scan count = Number of scans performed.

The biggest part of this that I don't understand is when
I see Scan Count = 0. Please use the following script example
and find my questions at the bottom.
*/

use tempdb
go

if object_id(N'dbo.TestA1', N'U') is not null
drop table dbo.TestA1
go

create table dbo.TestA1
(
CompanyID int not null primary key,
Company nvarchar(50)
)
go

insert dbo.TestA1
select 1, 'Company1' union all
select 2, 'Company2' union all
select 3, 'Company3' union all
select 4, 'Company4' union all
select 5, 'Company5'
go

select *
from dbo.TestA1
go

sp_helpindex TestA1
go

set statistics io on
set nocount on

-- Query #1
select *
from TestA1
where CompanyID = 1

-- Query #2
select *
from TestA1
where CompanyID = 1
and CompanyID = 2

-- Query #3
select CompanyID
from TestA1
where CompanyID = 1
union
select CompanyID
from TestA1
where CompanyID = 2

-- Query #4
select *
from TestA1
where CompanyID = 1
or CompanyID = 2

-- Query #5
select CompanyID
from TestA1
where CompanyID = 1
or CompanyID = 2

-- Query #6
select *
from TestA1
where CompanyID > 1

/*
Some points:
1- All 6 queries are ending up using Clustered Index Seek
2- Queries #1, #2 and #3 have Scan count 0
3- Queries #4 and #5 have Scan count 2
4- Query #6 have Scan count 1

My questions:
1- What is the exact definition of Scan count? If Books Online says

2- If scan means it "went and accessed the table" then I don't get it,
why queries #1, #2 and #3 indicate Scan count = 0 whereas the other
3 queries don't have Scan count = 0 and yet all 6 are using the same
clustered index seek operation.

3- Ultimately I need to understand what Scan count = 0 means.

4- On a little bit off topic question, if I write a query like Query #5,
in other words when I use an OR condition, doesn't that equal to the
same query as Query #3? Would SQL convert an OR argument to
a separate SELECT statement? If I had 3 OR expressions, would this
become 3 SELECT statements during execution? If that's not that the
case then what was it again where OR expressions are not necessarily
best optimized way to run them?

Thank you
*/




Erland Sommarskog replied on 11-May-08 06:32 PM
serge (sergea@nospam.ehmail.com) writes:

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.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
serge replied on 12-May-08 08:04 AM
Thanks Erland