OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' ))‌

Question

OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' ))‌

VoldemarG

Hall of Fame

Points: 3883
More actions
August 23, 2018 at 9:47 am

#362205

I need to review this simple query written by someone else a while ago, i have to review it for certain data analysis purposes:
Select [...fieldlist...] from vw_AgentDetails
OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' ))
Question:
Does OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' )) matter for my purposes, and how do I go about finding out why it is there and what it does?
thank you.
Likes to play Chess

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18380 More actions · Answer 1

VoldemarG - Thursday, August 23, 2018 9:47 AM
I need to review this simple query written by someone else a while ago, i have to review it for certain data analysis purposes:
Select [...fieldlist...] from vw_AgentDetails
OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' ))
Question:
Does OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' )) matter for my purposes, and how do I go about finding out why it is there and what it does?
thank you.

FORCE_LEGACY_CARDINALITY_ESTIMATION'
Forces the query optimizer to use Cardinality Estimation model of SQL Server 2012 (11.x) and earlier versions. This is equivalent to trace flag 9481 or Database Scoped
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

Thom A SSC Guru Points: 99134 More actions · Answer 2

The fact that they are using it suggest that the view (I assume it's a view with a suffix of _vw), performs poorly with the new estimator. I think the thing you should be reviewing is the View.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

Understood. Thanks. So this OPTION only matters for performance and has nothing to do with data itself, correct?

Likes to play Chess

Jonathan AC Roberts SSCoach Points: 18380 More actions · Answer 4

VoldemarG - Thursday, August 23, 2018 10:20 AM
Understood. Thanks. So this OPTION only matters for performance and has nothing to do with data itself, correct?

Yes, it's a hint for the optimiser and won't affect results.