Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Upgrading cardinality Expand / Collapse
Author
Message
Posted Thursday, May 15, 2014 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:39 PM
Points: 1,382, Visits: 1,752
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.
Post #1571289
Posted Thursday, May 15, 2014 7:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 225, Visits: 909
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.

Post #1571304
Posted Thursday, May 15, 2014 8:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 1,421, Visits: 2,442
Does anyone know if the trace flag should actually be 2312?
I don't have a 2014 instance to test with. :-(

Post #1571340
Posted Thursday, May 15, 2014 8:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 7,923, Visits: 9,649
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
Post #1571350
Posted Thursday, May 15, 2014 8:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 1,830, Visits: 6,646
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
Post #1571352
Posted Thursday, May 15, 2014 8:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 225, Visits: 909
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.
Post #1571353
Posted Thursday, May 15, 2014 8:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 2,532, Visits: 7,077
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.
Post #1571356
Posted Thursday, May 15, 2014 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 7,923, Visits: 9,649
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
Post #1571359
Posted Thursday, May 15, 2014 9:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 225, Visits: 909
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.
Post #1571367
Posted Thursday, May 15, 2014 9:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 7,923, Visits: 9,649
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.

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
Post #1571368
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse