SET STATISTICS IO – What is Scan count?

SET STATISTICS IO is a typical command which lets Database users to display information regarding the amount of disk activity generated by the Transact-SQL statements.

This command can be set to either ON or OFF.

Switching it to “ON”, it displays the stats of activity done by the SQL Server engine, like Scan Counts and number of Page Reads (Logical/Physical).

Let’s see here what does Scan Count exactly means in the information it provides:

As per MS BOL Scan Count is the:

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.
– Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = .
– Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on a non-primary key column. This is done to check for duplicate values for the key value that you are searching for. For example WHERE Clustered_Index_Key_Column = .
– Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.

Here we will see in what scenarios we will see different “Scan Counts”:

–> Execute below scripts as-is to see what they do:

USE [AdventureWorks2012]
GO

-- Insert some records into a new table from AdventureWorks's Person.Person table:
SELECT TOP 5000 BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, ModifiedDate
INTO dbo.Person
FROM [Person].[Person]
GO

-- Let's Clear the Cache:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Executing 3 different T-SQL statements to see what stats-information they provide:
SET STATISTICS IO ON
	SELECT * FROM dbo.Person 
	SELECT * FROM dbo.Person WHERE BusinessEntityID = 1
	SELECT * FROM dbo.Person WHERE BusinessEntityID IN (1,2)
SET STATISTICS IO OFF
GO

Output Message:
(5000 row(s) affected)
Table 'Person'. Scan count 1, 
 logical reads 38, physical reads 0, read-ahead reads 38, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table 'Person'. Scan count 1, 
 logical reads 38, physical reads 0, read-ahead reads 0, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2 row(s) affected)
Table 'Person'. Scan count 1, 
 logical reads 38, physical reads 0, read-ahead reads 0, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In the above Output window we can see that Scan Count = 1 in all the three queries.
This is because the table does not have an index and it does a single full Table-Scan for every query above.

The Execution plan below shows that all 3 queries are doing full Table-Scan.

---

–> Now let’s create a Unique Clustered Index on the table and see what “Scan Count” it gives on the same set of queries:

-- Create a Unique Clustered Index on the table:
CREATE UNIQUE CLUSTERED INDEX C_IX ON dbo.Person(BusinessEntityID)
GO

-- Let's Clear the Cache again:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Execute below statements and see what Output-Message it gives:
SET STATISTICS IO ON
	SELECT * FROM dbo.Person 
	SELECT * FROM dbo.Person WHERE BusinessEntityID = 1
	SELECT * FROM dbo.Person WHERE BusinessEntityID IN (1,2)
SET STATISTICS IO OFF

Output Message:
(5000 row(s) affected)
Table 'Person'. Scan count 1, 
 logical reads 44, physical reads 0, read-ahead reads 35, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table 'Person'. Scan count 0, 
 logical reads 2, physical reads 0, read-ahead reads 0, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2 row(s) affected)
Table 'Person'. Scan count 2, 
 logical reads 4, physical reads 0, read-ahead reads 0, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In the above Output window we can see a difference in Scan Count in all 3 queries.
1. First Query shows Scan Count = 1 as there is a Index but to return all rows it has to Scan the whole Index.
2. Second Query shows Scan Count = 0 as it uses the Index, but does a Index-Seek instead of Scan and Optimizer knows that there is only 1 value for BusinessEntityID = 1.
3. Third Query shows Scan Count = 2 as it also uses the Index, but it does a ORDERED FORWARD seek as it is looking for 2 different values, i.e. BusinessEntityID = 1 and 2.

Note: You will also see a reduction in number of Reads for the 2nd & 3rd queries, as it is utilizing the Index we created on the table, thus optimizing the performance.

Query plan for 3rd query:
|--Clustered Index Seek(OBJECT:([AdventureWorks2012].[dbo].[Person].[C_IX]), 
 SEEK:([AdventureWorks2012].[dbo].[Person].[BusinessEntityID]=(1) OR 
       [AdventureWorks2012].[dbo].[Person].[BusinessEntityID]=(2)) 
       ORDERED FORWARD)

The Execution plan below shows very clearly how the above 3-queries are using Index:

---

–> Let’s see what count it gives for T-SQL statement with 5 values:

SET STATISTICS IO ON
	SELECT * FROM dbo.Person WHERE BusinessEntityID IN (1,2,3,4,5)
SET STATISTICS IO OFF

(5 row(s) affected)
Table 'Person'. Scan count 5, 
 logical reads 10, physical reads 0, read-ahead reads 0, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here, we can see the Scan Count = 5 for the above query and reason is obvious. It is doing 5 ORDERED FORWARD Seeks to retrieve the 5 unique values.

-- Final Cleanup:
DROP TABLE dbo.Person

Check more on MSDN about SET STATISTICS IO : http://msdn.microsoft.com/en-us/library/ms184361.aspx