SQL Server – SSMS – Query Plan – Compare

Background

One of my favorite go to tool with SQL Server Query troubleshooting is to simple use SQL Server Management Studio (SSMS) to compare the query plans generated via running the same SQL across different machines or SQL Server Versions.\

Use

To use, please follow these simple steps :-

  1. Invoke SSMS
    • Query 1
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Query 2
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Access Query Plan pane
      • Access either Query’s Plan tab
      • Right click in an empty area of the pane
      • The drop-down menu appears
      • From the drop down menu, please choose “Compare Showplan”
      • From the File Open window,  choose the saved query plan from the opposing query

 

Sample

Sample – 01

Showplan Comparison

Pictorial

queryPlan.compare.showPlanComparison.20190109.0535PM.PNG

Explanation

  1. Certificates
    • Top Query :- Index Seek
    • Bottom Query :- Index Scan

 

Showplan Analysis

queryPlan.compare.showPlanAnalysis.20190109.0534PM.PNG

Explanation

  1. Scenarios
    • Difference :- 15316%
    • Estimated top/left :- 3274
    • Estimated bottom/right :- 504816

 

Requirement

As for client, requires at least SQL Server Management Studio v2017.

Works across various SQL Server Database Engine Versions.

Tested here against Server versions :-

  1. v2005
  2. v2017

 

SQL Server – Operator – Top – Top Expression (0)

Background

Earlier today I found myself pressing to make sure I had done right by a query.

 

Query Plan

Query Plan – 01

Here is the original query Plan

Image

queryPlan_computeScaler_HashMatch_20180517_1139AM [clipped]

Explanation

  1. I know that I don’t really want a Hash Match
    • Took care of the Hash Match by reducing the query from two tables to a single table
    • There are a few novel ways to do so, and will cover that later

 

Query Plan – 02

Here is the query Plan once we got rid of the secondary table

Image

queryPlan_computeScaler_HashMatch_20180517_1143AM [clipped]

 

Top ?

I was stuck at the Top Operator for a very long time

Explanation

  1. Rowcount
    • Do I have a set rowcount somewhere
    • Is my environment introducing a constraint for maximum number of records to “touch
    • Is my edition of SQL Server throttling performance
  2. Top
    • Do I have a top N clause somewhere

 

Operator – Top – Default

Overview

Here is what our Top Operators looks like when we do not have “set rowcount” set and we do not have an actual TOP Clause.

Image

Explanation

  1. Actual Number of Rows :- 65
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

 

Operator – Top – “Set rowcount”

Overview

What if we add an actual set rowcount

Image

 

Explanation

  1. Actual Number of Rows :- 2
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

Operator – Top – “Select TOP N”

Overview

Here is what we see when we add a “Top 1” Clause.

Image

Explanation

  1. Actual Number of Rows :- 1
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (1)

 

Other Things

Overview

I was stuck and so tried other things; such as

  1. Newer version of SQL Server ; v2017 to be exact
  2. Took out the insert into and performed a simple select

Could not reproduce…

 

Craig Freedman ( MSFT )

Finally goggled on the right terms and read what Craig Freedman has to say.

The particular post that I will be quoting is:

ROWCOUNT Top
Link

  1. TOP Operator
    • If you’ve looked at any insert, update, or delete plans, including those used in some of my posts, you’ve probably noticed that nearly all such plans include a top operator.
  2. SET ROWCOUNT
    •  It is a ROWCOUNT top. It is used to implement SET ROWCOUNT functionality.
  3. Why doesn’t SQL Server add a ROWCOUNT top to select statements?
    • SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan.  Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans.  For instance, if we add a unique index to our table, the update plan becomes substantially more complex
  4. Placement
    • By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.

 

Martin Smith

The good thing about blogging and allowing comments is that people can come back and provide helpful feedback.

Here is one from Martin Smith:

Martin Smith
December 29, 2012 at 8:15 am

In 2012 it looks like this operator is only added to plans run under “SET ROWCOUNT” of other than zero. As far as I can discern it is added in to the set_options used as a plan cache key.

SQL Server Versions

Here are the versions of SQL Server where you will be able to reproduce the Top (0) Operator preceding data effecting operators :-

  1. 2005
  2. 2008-R2

Dedication

Thankfully I have a far better grasp courtesy of two able men, Craig Freedman & Martin Smith.

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.

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