SQL Server – Statistics IO – Scan Count – When unique Index exist

Background

Reviewing the performance of a query and wanted to dig in deeper into the Statistics IO; specifically the Scan Count.

Lab

dbo.Numbers

Created and Populate Table

Let us create our test table.

It is a simple Numbers table and we will name it dbo.Numbers.

We will fill it with a million records; starting at 1, incrementing by 1, and close out at 1 million.

SQL


use master
go

SET NOCOUNT ON;
go

/*
	drop table [dbo].[Numbers]
*/
if object_id('[dbo].[Numbers]') is null
begin

	create table [dbo].[Numbers]
	(
		[Number] int not null
	)

end 
go

declare @edition sysname
declare @bCompressionSupported bit

set @edition = cast(serverproperty('edition') as sysname)

if (
		@edition in 
		( 
			'Standard Edition (64-bit)'
		)
	)
begin

	set @bCompressionSupported = 0

end
else if (@edition like 'Enterprise%')
begin

	set @bCompressionSupported = 1

end

print 'edition: ' + @edition
print 'compression : '  + cast(@bCompressionSupported as varchar(10))

if not exists
(

	select *
	from   sys.objects tblSO
	where  tblSO.parent_object_id = object_id('[dbo].[Numbers]')
	and    tblSO.[type] = 'PK' 

)
begin

	print 'Creating Primary Key ...'

	if (@bCompressionSupported = 1)
	begin

		alter table [dbo].[Numbers]
			add constraint [PK_Number] primary key
		   (
				[Number]
		   )
			WITH 
			(
				  DATA_COMPRESSION = PAGE
				, FILLFACTOR=100
				, ignore_dup_key=ON
			)				

	end
	else
	begin


		alter table [dbo].[Numbers]
			add constraint [PK_Number] primary key
		   (
				[Number]
		   )
			WITH 
			(
				  DATA_COMPRESSION = NONE
				, FILLFACTOR=100
				, ignore_dup_key=ON
			)				


	end

	print 'Created Primary Key'

end


if not exists
(

	select *
	from   sys.objects tblSO
	inner join sys.indexes tblSI
		on tblSO.object_id = tblSI.object_id
	where  tblSO.object_id = object_id('[dbo].[Numbers]')
	and    tblSI.[name] = 'INDX_Unique_Number' 

)
begin

	print 'Create Non-Clustered Index - INDX_Unique_Number ..'

	if (@bCompressionSupported = 1)
	begin

		create unique nonclustered index [INDX_Unique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = PAGE
			, FILLFACTOR=100
			, ignore_dup_key=ON
		)				

	end
	else
	begin


		create unique nonclustered index [INDX_Unique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = NONE
			, FILLFACTOR=100
			, ignore_dup_key=ON
		)				


	end

	print 'Created Non-Clustered Index - INDX_Unique_Number'

end


if not exists
(

	select *
	from   sys.objects tblSO
	inner join sys.indexes tblSI
		on tblSO.object_id = tblSI.object_id
	where  tblSO.object_id = object_id('[dbo].[Numbers]')
	and    tblSI.[name] = 'INDX_NonUnique_Number' 

)
begin

	print 'Create Non-Clustered Index - INDX_NonUnique_Number ..'

	if (@bCompressionSupported = 1)
	begin

		create nonclustered index [INDX_NonUnique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = PAGE
			, FILLFACTOR=100
			, ignore_dup_key=OFF
		)				

	end
	else
	begin


		create nonclustered index [INDX_NonUnique_Number]
		on [dbo].[Numbers]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = NONE
			, FILLFACTOR=100
			, ignore_dup_key=OFF
		)				


	end

	print 'Created Non-Clustered Index - INDX_NonUnique_Number'

end

go

DECLARE @UpperLimit INT;

set @UpperLimit = 1000000;

if not exists
	(
		select 1
		from   [dbo].[Numbers]
	)

begin

	print 'Adding data into [dbo].[Numbers] ....'

	;WITH cteNumber 
	AS
	(
			SELECT
				x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
			FROM  sys.all_objects AS s1
			CROSS JOIN sys.all_objects AS s2
			CROSS JOIN sys.all_objects AS s3
	)
	INSERT INTO [dbo].[Numbers]
	SELECT [Number] = cte.[x]
    FROM   cteNumber cte
    WHERE  cte.[x] BETWEEN 1 AND @UpperLimit;
 
	print 'Added data into [dbo].[Numbers]'

end

GO


 

 

Query Table

It is time to query the table

We will progressively add more numbers to our “In Clause”.

SQL



/*

	set statistics io on;
	set nocount on;

*/
set noexec off
go

set noexec off
go

use [master]
go

print replicate('=', 120)

print 'Searching for 1 Number'

SELECT
        [Extent1].[Number] AS [Number]  -- 1 Value
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (  1 ) 

print replicate('=', 120)

print 'Searching for 2 Numbers'

SELECT
        [Extent1].[Number] AS [Number]  -- 1-2 Value
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2
		 ) 

print replicate('=', 120)

print 'Searching for 10 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 10 Values
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
		 ) 


print replicate('=', 120)

print 'Searching for 60 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 10 Values
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
		 ) 


print replicate('=', 120)

print 'Searching for 64 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 Thur 64 Values
		FROM [dbo].[Numbers] AS [Extent1]
 		WHERE [Extent1].[Number] IN
		 (
			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64
		 ) 

		 
print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 65 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
		FROM [dbo].[Numbers] AS [Extent1]
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65
		 ) 

print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 65 Numbers ( Using Unique Index)'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
		FROM [dbo].[Numbers] AS [Extent1] with ( INDEX=INDX_Unique_Number)
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65
		 ) 

print replicate('=', 120)

------------------------------------------------------------------------------------------------


print 'Searching for 65 Numbers ( Using Non-Unique Index)'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
		FROM [dbo].[Numbers] AS [Extent1] with ( INDEX=INDX_NonUnique_Number)
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65
		 ) 

print replicate('=', 120)


print 'Searching for 66 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 66 Values
		FROM [dbo].[Numbers] AS [Extent1]
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65, 66
		 ) 

print replicate('=', 120)

------------------------------------------------------------------------------------------------

print 'Searching for 70 Numbers'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 90 Values
		FROM [dbo].[Numbers] AS [Extent1]
		WHERE [Extent1].[Number] IN
		 (

			  1, 2, 3, 4, 5, 6, 7, 8, 9, 10
			, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
			, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
			, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
			, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
			, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60
			, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70
		 ) 

print replicate('=', 120)

---------------------------------------------------------------------------------------------------
go



 

Output

Query Plan

Explanation
  1. Message :- Scan a particular range of rows from a nonclustered index.
  2. Actual Number of Rows :- 65
  3. Estimated Number of Executions :- 65
  4. Number of Executions :- 65
  5. Number of Rows Read :- 65
Statistics IO

 

Tabulate

Scenario Statistics I/O Scan Count
 Searching for 1 Number Table ‘Numbers’. Scan count 0, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 0
 Searching for 2 Numbers  Table ‘Numbers’. Scan count 2, logical reads 6, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  2
 Searching for 10 Numbers  Table ‘Numbers’. Scan count 10, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  10
 Searching for 60 Numbers  Table ‘Numbers’. Scan count 60, logical reads 180, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  60
 Searching for 64 Numbers  Table ‘Numbers’. Scan count 64, logical reads 192, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  64
 Searching for 65 Numbers  Table ‘Numbers’. Scan count 0, logical reads 302, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
 Searching for 65 Numbers ( Using Unique Index)  Table ‘Numbers’. Scan count 0, logical reads 208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
 Searching for 65 Numbers ( Using Non-Unique Index)  Table ‘Numbers’. Scan count 0, logical reads 208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
 Searching for 66 Numbers  Table ‘Numbers’. Scan count 0, logical reads 211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0
Searching for 70 Numbers  Table ‘Numbers’. Scan count 0, logical reads 223, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  0

 

 

Version Tested On

 

Version
Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) – 12.0.5203.0 (Intel X86)
Sep 23 2016 18:45:55
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 6.1 (Build 7601: ) (WOW64)

 

 

Source Code Control

GitHub

  1. SQL Server – Statistics I/O – Scan Count – In List Query
    Link

 

MS Connect

  1. Statistics IO / Scan Count not always indicative
    Number :- 3133893
    Status :- Active
    Type :- Bug
    Opened :- 5/8/2017 9:46:53 AM
    Link

 

Summary

Not so sure why Scan Count is being reported as zero when more than 65 values are being queried on.

 

References

  1. Amit Banerjee ( SQL Server Escalation Engineer [ SEE ] , Microsoft SQL support )
    • SCAN COUNT meaning in SET STATISTICS IO output
      Link
    • Scan Count meaning in SET STATISTICS IO output: Part 2
      Link

One thought on “SQL Server – Statistics IO – Scan Count – When unique Index exist

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s