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 – Clone Database – Schema & Statistics

Background

Working with my best friend to troubleshoot a non-performant query and so here we go.

Scenario

The database is too big and contains data that should be kept private and so what to do.

DBCC Clone

Outline

  1. Source SQL Instance
    • dbcc  clone
    • Backup resulting database
  2. Destination SQL Instance
    • Restore database
  3. Compare Queries

Source SQL Instance

DBCC Clone

SQL

DBCC CLONEDATABASE 

	(
		  [WideWorldImportersDW]
		, [WideWorldImportersDW_DBCCClone]
	);   

GO
GO

Database Backup

SQL

exec master..xp_create_subdir 'E:\temp'
go

backup database [WideWorldImportersDW_DBCCClone]
to disk = 'E:\temp\WideWorldImportersDW_DBCCClone.bak'
with init, format, stats=1 

GO

Destination SQL Instance

Database Restore

SQL

USE [master]
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone'
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone'
go

RESTORE DATABASE [WideWorldImportersDW_DBCCClone] 

FROM  DISK = N'C:\Temp\WideWorldImportersDW_DBCCClone.bak' WITH  FILE = 1

	,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_167133806.mdf'
	,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_UserData_1341343279.ndf'
	,  MOVE N'fg_Partition_Year_Base__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Base_file_001_4162930605.ndf'
	,  MOVE N'fg_Partition_Year_2010__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2010_file_001_3028494415.ndf'
	,  MOVE N'fg_Partition_Year_2011__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2011_file_001_474007300.ndf'
	,  MOVE N'fg_Partition_Year_2012__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2012_file_001_3961387451.ndf'
	,  MOVE N'fg_Partition_Year_2013__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2013_file_001_2554884830.ndf'
	,  MOVE N'fg_Partition_Year_2014__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2014_file_001_3195214954.ndf'
	,  MOVE N'fg_Partition_Year_2015__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2015_file_001_2796606126.ndf'
	,  MOVE N'fg_Partition_Year_2016__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2016_file_001_2922730419.ndf'
	,  MOVE N'fg_Partition_Year_2017__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2017_file_001_89564842.ndf'
	,  MOVE N'fg_Partition_Year_2018__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2018_file_001_2594545631.ndf'
	,  MOVE N'fg_Partition_Year_2019__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2019_file_001_102789485.ndf'
	,  MOVE N'fg_Partition_Year_2020__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2020_file_001_2559250543.ndf'
	,  MOVE N'fg_Partition_Year_Null__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Null_file_001_3677708393.ndf'
	,  MOVE N'fg_Partition_Year_Next__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Next_file_001_2414564952.ndf'
	,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_3476128794.ldf'
	,  NOUNLOAD
	,  STATS = 5

GO

Compare Queries

Outline

  1. Compare Queries
    • Query 1

Compare Queries

Query 1

SQL
set nocount on
go

set statistics io on
go

declare @dateTS datetime
declare @date datetime

set @dateTS = getdate()
set @date = convert(varchar(10), @dateTS, 23)

select
          @dateTS as [@dateTS]
        , @date as [@date]

select top 10 *

from   [Dimension].[Date] tblD

select top 10 *

from   [Dimension].[Date] tblD

where  tblD.[Date] = @date
Output
Output – Source

query1_Source_20181115_0911AM

Output – Destination

query1_Destination_20181115_0912AM

Explanation
  1. We have data from original source
  2. And, not data from the resultant cloned database

 

Query Plan
Query Plan – Destination

queryPlan_Destination_20181115_0853AM

Query Plan – Source

queryPlan_Source_20181115_0905AM.PNG

Statistics IO
Image – Destination

statisticsIO.Destiation.2018115.0855AM

Image – Source

statisticsIO.Source.2018115.0856AM

Explanation
  1. Statistics IO
    • Scan Counts will match
    • Logical & Physical Reads will not
      • As Source has actual data, but destination does not

 

References

  1. Microsoft
    • Docs / SQL / T-SQL / Database Console Commands
      • DBCC CLONEDATABASE (Transact-SQL)

 

Transact SQL – Warning – “No Join Predicate”

 

Background

A couple of weeks ago, I noticed a warning in a query plan.

The warning read “No Join Predicate“.

Query Plan

Warning – No Join Predicate

Sample Queries

Conventional Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP_Legacy') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP_Legacy as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP_Legacy]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip]
				 = tblT.[name]
            , [tripDate]
				 = [tblT].tripDate

            , [participant]
				=
				  tblP.firstname
				+ ' '
				+ tblP.lastname

    from
          [school].[tripParticipant] tblTP
        , [school].[trip] tblT
        , cteParticipant tblP

    where tblTP.tripID = tblT.id

    -- and   tblTP.participantID = tblP.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1145A

Explanation
  1. In this example we are using the old classic join
  2. We have three tables, but only joined the first two tables

 

ANSI Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip] = tblT.[name]
            , [tripDate] = [tblT].tripDate
            , tblP.firstname
            , tblP.lastname

    from   [school].[tripParticipant] tblTP

    inner join [school].[trip] tblT

        on tblTP.tripID
            = tblT.id

    inner join cteParticipant tblP
        /*
            on tblTP.participantID
                = tblP.id
        */
        on tblTP.tripID
             = tblT.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1150AM

Explanation
  1. In this example we are using ANSI join
  2. We have three tables and two join clauses
  3. Unfortunately the second join does not actually join the two tables that it is meant to join
    • We intentionally cut and pasted the early join.
    • But, did not modify the pasted clause and reference the actual tables we are trying to join

 

Summary

The samples listed above are simple and easy to correct.

The one I actually ran into is a bit more difficult to address.

 

 

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.

SSMS – Query Plan shows XML ( not graphics )

Background

I have a nice query that is looking at our cached plan for a certain pattern.

Scenario

SQL Server Management Studio ( v2014 )

Here is where we start out…

Grid

And, so I click on the query_plan column and saw what I pasted below…

Query Plan as XML

 

And, yes I am good with XML, but I was hoping for nice intuitive query plan.

 

Remediation

SQL Server Management Studio ( v2017 )

Download and Install SSMS v2017

Please download v2017 from here.

Use it as it is a far better tool.

SQL Server Management Studio ( v2014 )

To continue to use SSMS v2014 please do the following

Outline

  1. Launch Windows Explorer
  2. Access the following folder C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan
    • Make a backup of the showplanxml.xsd file
    • Access the v2017 version from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan
    • Overwrite the showplanxml.xsd file in C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan with C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan

 

Reference

 

Marketing Version Internal Version Folder File Info
 2017  140  C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan  File Date :- Saturday, ‎August ‎22nd, ‎2017, ‏‎2:51:26 AM

File Size :- 95.1 KB (97,406 bytes)

 2016  130  C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :-  ‎Tuesday, ‎February ‎23, ‎2016, ‏‎9:31:06 AM

File Size :- 85.5 KB (87,650 bytes)

 2014  120  C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Friday, ‎June ‎17, ‎2016, ‏‎7:31:38 PM

File Size :- 85.2 KB (87,333 bytes)

 2012  110  C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Thursday, ‎February ‎20, ‎2014, ‏‎6:32:11 PM

File Size :- 80.3 KB (82,266 bytes)

 2008  100  C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Wednesday, ‎July ‎22, ‎2009, ‏‎1:17:32 AM

File Size :- 70.5 KB (72,272 bytes)

 2005  90
 2000  80

 

Credit

Crediting Aaron Bertrand.

Here is his post

Title :- Getting graphical ShowPlans back in SQL Server 2008 R2
Date Published :- October 28, 2011
Link

 

Backdrop

The reason for the breakage is that each SQL Server Version expands beyond previous ones.

New functionalities are added.

We have new operators, performance lag causation are exposed.

Query plans reflect these changes.

Unfortunately SSMS bundled with previous engines are not aware of this changing landscape; and thus break.

It is not clear whether the change occurs during metadata discovery or doing the actual analysis.

The clear path is to install newer SSMS as they are released or at least one has SQL Server Instances running the equivalent versions.

 

 

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