Keeping my cardinality

Question

Post reply

Keeping my cardinality

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
April 28, 2014 at 10:37 pm

#307090

Comments posted to this topic are about the item Keeping my cardinality
April 29, 2014 at 2:31 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 37 total)

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

paul s-306273 SSChampion Points: 10730 More actions · Answer 1

Looks like I'm going to learn something everyday this week.

Thanks for the 2014 questions Steve.

dawryn SSCertifiable Points: 5481 More actions · Answer 2

:blink: :blink: Is this question about previously existing database or a newly created one? :unsure: According to linked document answers are only for the latter :Whistling:

domenico.delbrocco SSChasing Mays Points: 617 More actions · Answer 3

Thanks for the question

I guess I need to learn more about SQL Server 2014

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 4

I don't understand. I chose the last option, 'Alter all queries to use the trace flag 9481', which this page says will force use of the old version 70 optimiser.

The following plan affecting trace flags are available in Microsoft SQL Server 2014:
Trace FlagDescription
9481Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.
2312Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 version) of the cardinality estimator when creating the query plan.

If you run in compatibility level 110, are you still able to use the in-memory functionality?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I see I'm not the only one who learned something new this morning. Good question that required research. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Neil Burton SSC-Insane Points: 22999 More actions · Answer 6

paul.goldstraw (4/29/2014)
I don't understand. I chose the last option, 'Alter all queries to use the trace flag 9481', which this page says will force use of the old version 70 optimiser.
The following plan affecting trace flags are available in Microsoft SQL Server 2014:
Trace FlagDescription
9481Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.
2312Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 version) of the cardinality estimator when creating the query plan.
If you run in compatibility level 110, are you still able to use the in-memory functionality?

That caught me out too. I also inferred from this:

If I do not want the cardinality estimatior to be used in a specific database, what can I do?

that the Cardinality Estimator could be switched off entirely rather than reset to an earlier version.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 7

Yep, it left a bit to the imagination. Sadly I imagined wrong. If In-Memory wasn't relevant it really shouldn't have been mentioned because the question came across like it was asking 'I want to use In-Memory functionality but not the new optimiser, how do i do that?'

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

paul s-306273 (4/29/2014)
Looks like I'm going to learn something everyday this week.
Thanks for the 2014 questions Steve.

+1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

In the question: "If I do not want the cardinality estimator to be used," that crystal clear means to disable this feature.

but the answer states that you will run the database with an old version of Cardinality Estimator!

This is contradicting, right?

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 10

Hany Helmy (4/29/2014)
In the question: "If I do not want the cardinality estimator to be used," that crystal clear means to disable this feature.
but the answer states that you will run the database with an old version of Cardinality Estimator!
This is contradicting, right?

From my local help files on SQL 2014, it says

Upgrade all existing databases to use the new cardinality estimator. To do this, use ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 120.

I "assume" that after upgrading the instance, by default this feature is disabled and only by altering the compatibility level it may start to use the new cardinality estimator.

-edit:// (added below quote)

QUOTE

The new cardinality estimator is enabled for all new databases created in SQL Server 2014. However, upgrading to SQL Server 2014 does not enable the new cardinality estimator on existing databases.

UNQUPTE

and... (the below is the additional reference on the trace-flag and what happens if that is enabled)

There are two new query_optimizer_estimate_cardinality XEvents to support the new query plans.

query_optimizer_estimate_cardinality occurs when the query optimizer estimates the cardinality on a relational expression.

query_optimizer_force_both_cardinality_estimation_behaviors occurs when both traceflags 2312 and 9481 are enabled, attempting to force both old and new cardinality estimation behavior at the same time.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 11

dawryn (4/29/2014)
:blink: :blink: Is this question about previously existing database or a newly created one? :unsure: According to linked document answers are only for the latter :Whistling:

It is an option in the database properties page.

http://blogs.technet.com/b/dataplatforminsider/archive/2014/03/17/the-new-and-improved-cardinality-estimator-in-sql-server-2014.aspx

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 12

Nice question for SQL 2014 Steve. I love how the tricky wording of all the "almost right" answers is causing additional learning for us Trolls... 😛

shanjan.sapra Mr or Mrs. 500 Points: 555 More actions · Answer 13

Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.

Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.