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


Upgrading cardinality


Upgrading cardinality

Author
Message
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2612 Visits: 2832
Setting aside the trace flag issue (I don't blame the question writer for Microsoft's inability to write proper documentation) the only problem I have with this question is the use of the term "best".

To paraphrase an old quote about lawyers, ask five SQL Server professionals the best way to write a simple SELECT statement, and they'll give you six(teen) different answers.

You could have made this question bulletproof by using the term "recommended" instead of "best". Even the flipped trace flag would be technically correct, because Microsoft actually does recommend using the wrong one on that page.
crmitchell
crmitchell
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1761
Toreador (5/15/2014)
Whatever the truth about the trace flags, I disagree with the answer.
In my opinion, the best way to test the behaviour of a particular database version is on a database that is actually set to that version. Relying on compatibility levels, flags, etc, is relying on there being no bugs in how the emulation has been coded. The only way to be certain of your results (and hence the best way of doing it) is to create a copy of your database that is set to the appropriate version and compatibility level.


^
!
!
This.

The explanation for this states that using the trace flags can be used for this and presumably it is EASIER to do so but I cannot agree that that necessarily makes it the BEST way.

If you define BEST as most likely to ensure you get the correct answer then the only way you can absolutely guarantee that is to carry out your test in that specific environment. With anything else you are relying on the emulation being correct.
sestell1
sestell1
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: 2547 Visits: 3462
Does anyone know if the trace flag should actually be 2312?
I don't have a 2014 instance to test with. :-(
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14332 Visits: 12197
sknox (5/15/2014)
Setting aside the trace flag issue (I don't blame the question writer for Microsoft's inability to write proper documentation) the only problem I have with this question is the use of the term "best".

To paraphrase an old quote about lawyers, ask five SQL Server professionals the best way to write a simple SELECT statement, and they'll give you six(teen) different answers.

No, I wont believe 16 and certainly not 6. 5 SQL Server professionals will never all agree on a compromise solution, so the correct answer is not 32, and zero SQL Server professionals won't offer an answer at all, so it isn't 31 either. So it must be 30, since any two disjoint nonempty sets of SQL professionals will provide two different answers, and no proper superset of some (non-empty) original set of SQL Server professional will provide the same answer as the original set.

Of course this only applies to small sets, as can clearly be seen by considering the subsets of a set or 20 SQL Server professionals, especially if one places a limit on the amount of irrelevant twaddle included in the answer, since there are many SQL Server questions for which it just isn't possible to imagine 1048754 different answers, but it's safe for sets with fewer than 10 members since SQL Server Profesionals can devise 510 answers to any question about SQL Server.

Tom

Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2781 Visits: 8084
TomThomson (5/15/2014)
No, I wont believe 16 and certainly not 6. 5 SQL Server professionals will never all agree on a compromise solution, so the correct answer is not 32, and zero SQL Server professionals won't offer an answer at all, so it isn't 31 either. So it must be 30, since any two disjoint nonempty sets of SQL professionals will provide two different answers, and no proper superset of some (non-empty) original set of SQL Server professional will provide the same answer as the original set.

Of course this only applies to small sets, as can clearly be seen by considering the subsets of a set or 20 SQL Server professionals, especially if one places a limit on the amount of irrelevant twaddle included in the answer, since there are many SQL Server questions for which it just isn't possible to imagine 1048754 different answers, but it's safe for sets with fewer than 10 members since SQL Server Profesionals can devise 510 answers to any question about SQL Server.


+1
crmitchell
crmitchell
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1761
TomThomson (5/15/2014)
sknox (5/15/2014)
Setting aside the trace flag issue (I don't blame the question writer for Microsoft's inability to write proper documentation) the only problem I have with this question is the use of the term "best".

To paraphrase an old quote about lawyers, ask five SQL Server professionals the best way to write a simple SELECT statement, and they'll give you six(teen) different answers.

No, I wont believe 16 and certainly not 6. 5 SQL Server professionals will never all agree on a compromise solution, so the correct answer is not 32, and zero SQL Server professionals won't offer an answer at all, so it isn't 31 either. So it must be 30, since any two disjoint nonempty sets of SQL professionals will provide two different answers, and no proper superset of some (non-empty) original set of SQL Server professional will provide the same answer as the original set.

Of course this only applies to small sets, as can clearly be seen by considering the subsets of a set or 20 SQL Server professionals, especially if one places a limit on the amount of irrelevant twaddle included in the answer, since there are many SQL Server questions for which it just isn't possible to imagine 1048754 different answers, but it's safe for sets with fewer than 10 members since SQL Server Profesionals can devise 510 answers to any question about SQL Server.


lol

unfortunately only too true.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15177 Visits: 18603
sestell1 (5/15/2014)
Does anyone know if the trace flag should actually be 2312?
I don't have a 2014 instance to test with. :-(



I'm getting slightly confused, ran queries with SHOWPLAN_XML on and both flags OPTION (QUERYTRACEON 9481) / OPTION (QUERYTRACEON 2312)

The output indicates 2005 query plan for all instances, regardless of compatibility level.
Cool
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14332 Visits: 12197
Toreador (5/15/2014)
Whatever the truth about the trace flags, I disagree with the answer.
In my opinion, the best way to test the behaviour of a particular database version is on a database that is actually set to that version. Relying on compatibility levels, flags, etc, is relying on there being no bugs in how the emulation has been coded. The only way to be certain of your results (and hence the best way of doing it) is to create a copy of your database that is set to the appropriate version and compatibility level.

In that case you seem to be reading a different question from the one I'm reading. The question I saw was about testing specific queries using the new cardinality estimator, rather than testing all the queries in a database.

You probably wouldn't want to test all of them, because there are some queries that you won't want the new estimator to handle until the bug has been fixed - see for example sql-server-2014-cardinality-estimation-regression - so it's perfectly reasonable to think about testing specific queries rather than the whole kit and caboodle.

Tom

crmitchell
crmitchell
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1761
In that case you seem to be reading a different question from the one I'm reading. The question I saw was about testing specific queries using the new cardinality estimator, rather than testing all the queries in a database.

You probably wouldn't want to test all of them, because there are some queries that you won't want the new estimator to handle until the bug has been fixed - see for example sql-server-2014-cardinality-estimation-regression - so it's perfectly reasonable to think about testing specific queries rather than the whole kit and caboodle.[/quote]

No

Setting up a copy of the database is all about setting up the environment. Within that environment you are free to run any of the tests - whilst it is possible to test all of the queries there is no compunction to do so - you are equally free to limit your testing to any subset of them such as the specific set of tests in the question.
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14332 Visits: 12197
Eirikur Eiriksson (5/15/2014)
sestell1 (5/15/2014)
Does anyone know if the trace flag should actually be 2312?
I don't have a 2014 instance to test with. :-(



I'm getting slightly confused, ran queries with SHOWPLAN_XML on and both flags OPTION (QUERYTRACEON 9481) / OPTION (QUERYTRACEON 2312)

The output indicates 2005 query plan for all instances, regardless of compatibility level.
Cool

Maybe http://blogs.msdn.com/b/psssql/archive/2014/04/01/sql-server-2014-s-new-cardinality-estimator-part-1.aspx is yet anothet BOL page which misleads? Or has MS done something crazy like allowing the trace flags to cause the optimizer to switch to a different cardinality estimator version without recording it in the setting of the CardinalityEstimationModelVersion attribute in the xml plan?

Tom

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