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-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 173
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

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

Nice Question....
batgirl
batgirl
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2109 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-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43880 Visits: 10831
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 (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63407 Visits: 18570
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

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63407 Visits: 18570
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

Thomas Abraham
Thomas Abraham
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: 3759 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.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: 3236 Visits: 648
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 (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63407 Visits: 18570
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

batgirl
batgirl
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2109 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