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 Tuesday, April 29, 2014 6:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:27 AM
Points: 1,321, Visits: 632
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?
Post #1565880
Posted Tuesday, April 29, 2014 6:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 1,008, Visits: 2,297
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.
Post #1565893
Posted Tuesday, April 29, 2014 7:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
dawryn (4/29/2014)
Is this question about previously existing database or a newly created one? According to linked document answers are only for the latter


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
Post #1565898
Posted Tuesday, April 29, 2014 7:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
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...
Post #1565900
Posted Tuesday, April 29, 2014 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:11 PM
Points: 48, Visits: 83
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.

Post #1565902
Posted Tuesday, April 29, 2014 7:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
shanjan.sapra (4/29/2014)
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.



Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.
Post #1565903
Posted Tuesday, April 29, 2014 7:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
shanjan.sapra (4/29/2014)
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.



"You can enable these trace flags at server, session or query level. To enable the trace flag at query level, you use QUERYTRACEON hint documented in KB2801413. "

From this article:
http://blogs.msdn.com/b/psssql/archive/2014/04/01/sql-server-2014-s-new-cardinality-estimator-part-1.aspx

You can not enable these trace flags at only the database level.
Post #1565907
Posted Tuesday, April 29, 2014 7:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,101, Visits: 1,453
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
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.



Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.


This webpage clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out
Post #1565908
Posted Tuesday, April 29, 2014 7:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
paul.goldstraw (4/29/2014)
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
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.



Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.


This webpage clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out


This is a great example of a correct statement that is still wrong.
What you are saying is true, but that was not one of the possible answers now was it?
It's not even what the post you are quoting is talking about.
Post #1565912
Posted Tuesday, April 29, 2014 7:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,101, Visits: 1,453
PHYData DBA (4/29/2014)
paul.goldstraw (4/29/2014)
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
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.



Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.




This webpage clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out


This is a great example of a correct statement that is still wrong.
What you are saying is true, but that was not one of the possible answers now was it?
It's not even what the post you are quoting is talking about.


The final option was "Alter all queries to use the trace flag 9481". Altering the queries in the way the link mentions, but using trace flag 9481 instead of 2312 as in the example given would force those queries to use the old optimiser, which is what the question is asking. The answer Steve is saying is correct wouldn't allow the use of memory optimised tables, which the question said was the point of upgrading in the first place.
Post #1565916
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse