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

Background

Quick follow-up to our last post.

That post is titled “SQL Server – Statistics IO – Scan Count – When unique Index exists“.

And, it is here.

Lab

Outline

In this exercise, we will create a table and create a single non-unique index on our lone column.

The name of the table is dbo.Numbers_NoUniqueINDX, the name of the lone column is Number.

And, we will create a single non-unique Index.

 

Create & Populate Table

SQL


use master
go

set noexec off
go
SET NOCOUNT ON;
go

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

	create table [dbo].[Numbers_NoUniqueINDX]
	(
		  [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
	inner join sys.indexes tblSI
		on tblSO.object_id = tblSI.object_id
	where  tblSO.object_id = object_id('[dbo].[Numbers_NoUniqueINDX]')
	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_NoUniqueINDX]
		(
			[Number]
		)
		WITH 
		(
			  DATA_COMPRESSION = PAGE
			, FILLFACTOR=100
			, ignore_dup_key=OFF
		)				

	end
	else
	begin


		create nonclustered index [INDX_NonUnique_Number]
		on [dbo].[Numbers_NoUniqueINDX]
		(
			[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_NoUniqueINDX]
	)

begin

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

	;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_NoUniqueINDX]
	(
		[Number] 
	)
	SELECT [Number] = cte.[x]
    FROM   cteNumber cte
    WHERE  cte.[x] BETWEEN 1 AND @UpperLimit;
 
	print 'Added data into [dbo].[Numbers_No_UniqueINDX]'

end

GO


 

Query


set noexec off
set nocount on
set statistics io on;
go

use [master]
go

print replicate('=', 120)

print 'Searching for 1 Number'

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

print replicate('=', 120)

print 'Searching for 2 Numbers_NoUniqueINDX'

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

print replicate('=', 120)

print 'Searching for 10 Numbers_NoUniqueINDX'

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


print replicate('=', 120)

print 'Searching for 60 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 10 Values
		FROM [dbo].[Numbers_NoUniqueINDX] 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_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 Thur 64 Values
FROM [dbo].[Numbers_NoUniqueINDX] 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_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 65 Values
FROM [dbo].[Numbers_NoUniqueINDX] 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 66 Numbers_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 66 Values
FROM [dbo].[Numbers_NoUniqueINDX] 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_NoUniqueINDX'

SELECT 
        [Extent1].[Number] AS [Number]  -- 1 thru 90 Values
FROM [dbo].[Numbers_NoUniqueINDX] 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

 

Statistics IO

 

 

Tabulate

Scenario Statistics I/O Scan Count
 Searching for 1 Number Table ‘Numbers_NoUniqueINDX’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 0
 Searching for 2 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  2
 Searching for 10 Numbers Table ‘Numbers_NoUniqueINDX’. 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_NoUniqueINDX’. 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_NoUniqueINDX’. 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_NoUniqueINDX’. Scan count 65, logical reads 234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  65
 Searching for 66 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 66, logical reads 237, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 66
 Searching for 70 Numbers Table ‘Numbers_NoUniqueINDX’. Scan count 70, logical reads 249, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 70

 

 

Query Plan – Operator – Index Seek

Explanation

Scan Count matches accurately with the Number of Rows Read.

 

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

 

GuidePost

Amit Banerjee

Scan Count meaning in SET STATISTICS IO output: Part 2
Link

Even though we are evaluating the condition for all the 20 rows returned by the “backupset” table, we find that there is an additional filter in the SEEK condition i.e. the file_number column, for the data fetched from the “backupfile” table. This changes the scan count to Zero. The reason for this is that the SQL instance now knows that it has to fetch only one row for the value given in the filter as both columns present in the composite primary key are being used during the SEEK. Now SQL knows prior to the fetch that there cannot be duplicate rows, so no extra scan/seek is required.

This is a gotcha that we might overlook when a clustered index seek is being used! Since, we are dealing with a small set of rows, the plan changes may not be significant but for larger tables, such a difference in evaluation may have a significant impact on the query plan and ultimately the performance of the query.

 

Summary

There is a slight difference in how SQL Server measures Scan Counts when unique key scans are being measured, compared to how they are measured when Non-Unique Keys are used.

I don’t get it, but as always Amit Banerjee obviously does.

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