SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Keeping my cardinality


Keeping my cardinality

Author
Message
Hany Helmy
Hany Helmy
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3489 Visits: 1117
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
Raghavendra Mudugal
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2504 Visits: 2958
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
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1275 Visits: 537
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
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1275 Visits: 537
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... :-P
shanjan.sapra
shanjan.sapra
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 283
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.
PHYData DBA
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1275 Visits: 537
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.
PHYData DBA
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1275 Visits: 537
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.
paul.goldstraw
paul.goldstraw
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1352 Visits: 1765
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
PHYData DBA
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1275 Visits: 537
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.
paul.goldstraw
paul.goldstraw
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1352 Visits: 1765
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search