May 15, 2014 at 8:47 am
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.
May 15, 2014 at 8:49 am
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.
😎
May 15, 2014 at 8:53 am
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
May 15, 2014 at 9:04 am
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.
May 15, 2014 at 9:05 am
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
May 15, 2014 at 9:41 am
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.
Best response I've seen in a long time. Took me a while to quit laughing before I could finish reading it. Thanks Tom!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 15, 2014 at 9:47 am
I don't have an instance of 2014 to try this on. Still confused on what the most "correct" answer really is here. The references I looked up pointed at 9481 forcing the old estimator...
May 15, 2014 at 10:11 am
Testing on Sql Server 2014 Dev,
Trace flag 9481 forces CardinalityEstimationModelVerion 70
and 2312 forces 120.
The correct answer for the question should be traceflag 2313.
😎
May 15, 2014 at 10:32 am
James Lean (5/15/2014)
Agree with previous posters, the page referenced in the explanation has the trace flags the wrong way round. A Connect bug has already been logged about this:
Please vote for this.
May 15, 2014 at 10:36 am
I'll award back points. I tested this at one point, but in writing the question, I was looking at the documentation.
To the further discussion of upgrading the database: sure, ideally you'd want to move the database. However, that may not be practical, nor allowed. Perhaps your application doesn't work on 2014 mode yet. However you wish to test queries with the new CE to determine if work is worthwhile to upgrade code.
The point is that you want to be careful about choosing ideals, or imposing your situation on that others might be in. There are valid reasons why allowing someone to regression test with the old estimator on the SAME database, or testing uplevel behavior on an old database are good, or even valid, choices.
May 15, 2014 at 12:03 pm
Thank you for the post, SJ, interesting one.
Learnt something new, and then learnt what I learnt is not correct and then I learnt the correct way. Also voted on the connect.
And why it says "voting is no longer enabled on this item" in the tool-tip when you hover the mouse-pointer on the incremental arrow? and yet it is allowing us to vote.
If I summarize my understanding - TF-2312 (physically) changes the CL to 120 in the respective tuning session (or generating the QP) and then it changes back to 110 (for backward compatibility 2012 databases)? Or, - it creates a temporary copy or that database and changes the CL to 120 without effecting the original DB and then do the necessary action?
-//edit: Now I am wondering it has nothing to do with CL at all it just uses the new CE and old CE with respective of the TF and irrespective of the CL.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 15, 2014 at 12:15 pm
Finding this slightly amusing, attached a 2012 database to a 2014 instance, checked the Cardinality Estimator version and it reported 70. Changed the compatibility level to 2014, and 😀 surprise, 120. The correct answer was already there...:-P
😎
May 16, 2014 at 1:17 am
Steve Jones - SSC Editor (5/15/2014)
James Lean (5/15/2014)
Agree with previous posters, the page referenced in the explanation has the trace flags the wrong way round. A Connect bug has already been logged about this:Please vote for this.
Microsoft updated it yesterday
Thank you for pointing this out. I have corrected (switched) the numbers. And I added a community comment to that affect. Both of these will take a while to propagate. Probably 2 weeks or more for the main topic
May 16, 2014 at 5:22 am
I know I'm a day late, but I still learned something with this one. Trace flags are not my strong suit. I don't know them off the top of my head as I'd like to. Thanks for a good question.
May 17, 2014 at 4:56 pm
thanks for the great question steve.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply