May 14, 2014 at 9:02 pm
Comments posted to this topic are about the item Upgrading cardinality
May 14, 2014 at 11:42 pm
Good question, thanks!
I got it wrong:ermm: or did I:w00t:
Note, on Microsoft Support there is an article; Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level
The following plan affecting trace flags are available in Microsoft SQL Server 2014:
Trace FlagDescription
9481Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.
2312Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 version) of the cardinality estimator when creating the query plan.
On MSDN, there is Cardinality Estimation (SQL Server)
There it says:
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 9481 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.
Now I just have to find a coin to toss....
😎
May 15, 2014 at 12:18 am
Nice question, but I choose the wrong trace flag 🙁
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 15, 2014 at 12:19 am
I am also bit confused; after the upgrade it says db is in 110 compatibility i.e.SQL 2012 while 9481 trace flag will force the query optimizer to use version 70 (SQL 2012 version).
Anyway good question, thanks.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 15, 2014 at 12:48 am
I am also confused :w00t: :unsure:
After reading all these articles, I felt the anwer is
"change the compatibility to 120 and test to work new CE"
Please find the below links
http://msdn.microsoft.com/en-us/library/dn600374%28v=sql.120%29.aspx (which is provided by steve jones)
1st point is
"
Upgrade all existing databases to use the new cardinality estimator. To do this, use ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 120.
"
5th point is
"
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 9481 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.
"
but is different in the below links
http://support.microsoft.com/kb/2801413
estimation.aspx
May 15, 2014 at 1:31 am
This was removed by the editor as SPAM
May 15, 2014 at 3:30 am
Interesting question.
Not sure whether the correct answer is actually correct. The following MS documents all state either that Trace Flag 9841 forces use of the old CE or that trace flag 2312 forces use of the new CE (and most say both):
http://support.microsoft.com/kb/2801413
http://blogs.msdn.com/b/sql_shep/rss.aspx
The word doc in there had Paul White as one of its technical reviewers, which inclines me to trust it more than other MS documents.
The only MS document I know that puts these flags the other way round is the one referenced in the explanation for this question. So the weight of easily found MS documentation seems to be firmly on the other side, suggesting that this question has trusted the wrong MSDN page. I don't curently have SQL Server 2014 (still too busy earning SQL Server 2012) so maybe someone else can check? I believe that the XML Query Plan will tell you that it was built using the new CE if in fact it was, and not if it wasn't.
Tom
May 15, 2014 at 5:03 am
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:
-----
JL
May 15, 2014 at 5:30 am
Eirikur Eiriksson (5/14/2014)
Good question, thanks!I got it wrong:ermm: or did I:w00t:
Note, on Microsoft Support there is an article; Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level
The following plan affecting trace flags are available in Microsoft SQL Server 2014:
Trace FlagDescription
9481Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.
2312Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 version) of the cardinality estimator when creating the query plan.
On MSDN, there is Cardinality Estimation (SQL Server)
There it says:
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 9481 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.
Now I just have to find a coin to toss....
😎
+1
I had previously seen the first KB article you referenced and this one:
http://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eats-bad-tsql-breakfast/
Both speak of the same behavior...
Using OPTION(QUERYTRACEON 9481) to test Compatibility Level 110
You can go back to the old cardinality estimator with SQL Server 2014 in two ways: You could change the whole database’s compatibility level back to 110 like this:
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL=110;
GO
But that changes it for everything in the whole database. We might just want to see how THIS query would behave using the old cardinality estimator, but still on SQL Server 2014. You can do that by adding OPTION(QUERYTRACEON 9481) to the very end of our gruesome query:
Cheers
May 15, 2014 at 6:32 am
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.
May 15, 2014 at 7:05 am
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.
May 15, 2014 at 7:19 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.
^
!
!
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.
May 15, 2014 at 8:25 am
Does anyone know if the trace flag should actually be 2312?
I don't have a 2014 instance to test with. 🙁
May 15, 2014 at 8:42 am
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
May 15, 2014 at 8:46 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.
+1
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply