Keeping my cardinality

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721102

    Comments posted to this topic are about the item Keeping my cardinality

  • paul s-306273

    SSChampion

    Points: 10615

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

    Thanks for the 2014 questions Steve.

  • dawryn

    SSCarpal Tunnel

    Points: 4769

    :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:

  • This was removed by the editor as SPAM

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 587

    Thanks for the question

    I guess I need to learn more about SQL Server 2014

  • paul.goldstraw

    SSCrazy

    Points: 2626

    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 Flag Description

    9481 Use 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.

    2312 Use 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: 286987

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

  • Neil Burton

    SSC-Insane

    Points: 22255

    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 Flag Description

    9481 Use 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.

    2312 Use 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

    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: 13488

    paul s-306273 (4/29/2014)


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

    Thanks for the 2014 questions Steve.

    +1

  • Hany Helmy

    SSChampion

    Points: 13488

    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?

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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

    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

    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

    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.

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

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