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


Cardinality Regression


Cardinality Regression

Author
Message
Prassad Dabbada V R
Prassad Dabbada V R
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 174
some Confusion..

In the question no where it is mentioned that SQL Server 2014 is running database compatibility level 110
if the database compatibility level of SQL Server 2014 was set to 110 (SQL Server 2012 Compatibility level) then we can use Trace Flag as 2312 and run the query and force to use Cordinal Optimization of compatibility level 120.


Please guide me if I am wrong
Mr. Kapsicum
Mr. Kapsicum
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3750 Visits: 1061
is msdn confused, or its me....!!!

Nice Question....
batgirl
batgirl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3227 Visits: 1820
I respectfully disagree with the answer noted as "correct" by this QOTD.

From the article cited:

5.If your workload is running with database compatibility level 110 and you want to test or run a specific query with the new cardinality estimator, you can run the query with trace flag 2312 to use version 120 (the new version) of the cardinality estimator. To run a query with a trace flag, see the KB article Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level.


What's up?
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)

Group: General Forum Members
Points: 156938 Visits: 11658
Yeah, this was another one where I had to read BOL a couple times to see what they were trying to say. Thanks for the question.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144313 Visits: 18651
batgirl (6/2/2014)
I respectfully disagree with the answer noted as "correct" by this QOTD.

From the article cited:

5.If your workload is running with database compatibility level 110 and you want to test or run a specific query with the new cardinality estimator, you can run the query with trace flag 2312 to use version 120 (the new version) of the cardinality estimator. To run a query with a trace flag, see the KB article Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level.


What's up?


The correct answer was based off old documentation that has been since updated. The documentation is correct now and the answer to this question is wrong.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144313 Visits: 18651
free_mascot (6/1/2014)
Thank you, Steve. Easy One.


I am curious why you think this was an easy question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Thomas Abraham
Thomas Abraham
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6033 Visits: 2256
Got it right only because I've been paying attention. And because I answered it without consulting BOL!! ;-)

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
richardd
richardd
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3384 Visits: 649
SQLRNNR (6/2/2014)I am curious why you think this was an easy question.

The fact that it's almost identical to the question posted on 15th May helps! :-)
http://www.sqlservercentral.com/questions/SQL+Server+2014/108889/

The date on that question says 2nd June, so I assume there's a technical glitch somewhere.



SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144313 Visits: 18651
richardd (6/2/2014)
SQLRNNR (6/2/2014)I am curious why you think this was an easy question.

The fact that it's almost identical to the question posted on 15th May helps! :-)
http://www.sqlservercentral.com/questions/SQL+Server+2014/108889/

The date on that question says 2nd June, so I assume there's a technical glitch somewhere.


There is that, but since that question the documentation on BOL has been fixed.;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

batgirl
batgirl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3227 Visits: 1820
SQLRNNR (6/2/2014)
richardd (6/2/2014)
SQLRNNR (6/2/2014)I am curious why you think this was an easy question.

The fact that it's almost identical to the question posted on 15th May helps! :-)
http://www.sqlservercentral.com/questions/SQL+Server+2014/108889/

The date on that question says 2nd June, so I assume there's a technical glitch somewhere.


There is that, but since that question the documentation on BOL has been fixed.;-)


The two questions are actually opposite.

5/15 said I'm running the "old" version (by virtue of compatibility mode), how can I test the "new" version.
6/2 said I'm running the "new" version and it doesn't work, how can I run the "old" version.
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