Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Steve Jones Keeping my cardinality RE: Keeping my cardinality

  • April 29, 2014 at 5:21 am

    #1709390

    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