Do “random” Query Hints affect statistics in the Execution Plan?

So, there is this rumor that using a query hint affects the statistics that SQL Server uses to determine the execution plan, so we are told to ignore it.

Well, ain’t that just great.

Lets test it!

USE tempdb
GO
CREATE TABLE Test
(
ID INT IDENTITY(1,1),
TestData varchar(255),
CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)
)
GO
INSERT INTO dbo.Test
(
 --ID - this column value is auto-generated
 TestData
)
VALUES
(
 -- ID - INT
 'Test' -- TestData - varchar
)

DBCC TRACEON(3604)

 

So we did our setup. Lets test it on a simple select.  2373 gives us the memory allocation, and by using Querytraceon 8607 we will have a look at the physical operators and the output tree that is indeed closer to the final stage of the execution plan.

SELECT TOP 100 * FROM dbo.Test WITH (INDEX = PK_ID)  OPTION (querytraceon 8607, querytraceon 2373)

And this is the output:

Memory before deriving properties of LogOp_Get: 5

Memory after deriving properties of LogOp_Get: 6

Memory before deriving properties of AncOp_PrjList : 6

Memory after deriving properties of AncOp_PrjList : 6

Memory before deriving properties of LogOp_Project: 6

Memory before deriving properties of ScaOp_Const : 6

Memory after deriving properties of ScaOp_Const : 6

Memory after deriving properties of LogOp_Project: 6

Memory before deriving properties of ScaOp_Const : 6

Memory after deriving properties of ScaOp_Const : 6

Memory before deriving properties of ScaOp_Const : 6

Memory after deriving properties of ScaOp_Const : 6

Memory before deriving properties of LogOp_Top: 6

Memory after deriving properties of LogOp_Top: 6

Memory before deriving properties of LogOp_Top: 6

Memory after deriving properties of LogOp_Top: 6

Memory before deriving properties of LogOp_Get: 6

Memory after deriving properties of LogOp_Get: 6

Memory before deriving properties of LogOp_Top: 6

Memory after deriving properties of LogOp_Top: 6

Memory before deriving properties of LogOp_GetIdx: 6

Memory after deriving properties of LogOp_GetIdx: 7

*** Output Tree: (trivial plan) ***

PhyOp_Top NoTies

PhyOp_NOP

PhyOp_Range TBL: dbo.Test(1) ASC Bmk ( QCOL: [tempdb].[dbo].[Test].ID) IsRow: COL: IsBaseRow1000 Hints( FORCEDINDEX )

ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=100)

ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)

********************

** Query marked as Cachable

********************

(1 row(s) affected)

Lets try without the the index hint.

SELECT TOP 100 * FROM dbo.Test OPTION (querytraceon 8607, querytraceon 2373)

And here is the output:

Memory before deriving properties of LogOp_Get: 5

Memory after deriving properties of LogOp_Get: 6

Memory before deriving properties of AncOp_PrjList : 6

Memory after deriving properties of AncOp_PrjList : 6

Memory before deriving properties of LogOp_Project: 6

Memory before deriving properties of ScaOp_Const : 6

Memory after deriving properties of ScaOp_Const : 6

Memory after deriving properties of LogOp_Project: 6

Memory before deriving properties of ScaOp_Const : 6

Memory after deriving properties of ScaOp_Const : 6

Memory before deriving properties of ScaOp_Const : 6

Memory after deriving properties of ScaOp_Const : 6

Memory before deriving properties of LogOp_Top: 6

Memory after deriving properties of LogOp_Top: 6

Memory before deriving properties of LogOp_Top: 6

Memory after deriving properties of LogOp_Top: 6

Memory before deriving properties of LogOp_Get: 6

Memory after deriving properties of LogOp_Get: 6

Memory before deriving properties of LogOp_Top: 6

Memory after deriving properties of LogOp_Top: 6

Memory before deriving properties of LogOp_GetIdx: 6

Memory after deriving properties of LogOp_GetIdx: 7

*** Output Tree: (trivial plan) ***

PhyOp_Top NoTies

PhyOp_NOP

PhyOp_Range TBL: dbo.Test(1) ASC Bmk ( QCOL: [tempdb].[dbo].[Test].ID) IsRow: COL: IsBaseRow1000

ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=100)

ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)

********************

** Query marked as Cachable

********************

(1 row(s) affected)

So we can conclude that it does not indeed affect the logical operations.


		
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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