Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Keeping my cardinality Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 10:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
Comments posted to this topic are about the item Keeping my cardinality






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1565753
Posted Tuesday, April 29, 2014 1:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:32 AM
Points: 1,417, Visits: 809
Looks like I'm going to learn something everyday this week.
Thanks for the 2014 questions Steve.
Post #1565781
Posted Tuesday, April 29, 2014 2:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:23 AM
Points: 2,516, Visits: 771
Is this question about previously existing database or a newly created one? According to linked document answers are only for the latter

Post #1565794
Posted Tuesday, April 29, 2014 2:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:08 AM
Points: 4,160, Visits: 5,557
Interesting question, thanks Steve.
Had to research this one, so learned something...


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1565799
Posted Tuesday, April 29, 2014 2:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 7:38 AM
Points: 256, Visits: 82
Thanks for the question
I guess I need to learn more about SQL Server 2014
Post #1565803
Posted Tuesday, April 29, 2014 4:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 1,160, Visits: 1,547
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?
Post #1565835
Posted Tuesday, April 29, 2014 5:10 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 4,611, Visits: 4,068
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
Post #1565853
Posted Tuesday, April 29, 2014 5:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 5:27 AM
Points: 1,268, Visits: 4,145
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
Post #1565860
Posted Tuesday, April 29, 2014 5:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 1,160, Visits: 1,547
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?'
Post #1565863
Posted Tuesday, April 29, 2014 6:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:53 PM
Points: 1,431, Visits: 687
paul s-306273 (4/29/2014)
Looks like I'm going to learn something everyday this week.
Thanks for the 2014 questions Steve.

+1
Post #1565877
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse