Click here to monitor SSC
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
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 172
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
Right there with Babe
Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)

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

Nice Question....
batgirl
batgirl
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1561 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10315 Visits: 9594
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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2341 Visits: 2254
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.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3089 Visits: 642
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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

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