May 31, 2014 at 1:24 pm
Comments posted to this topic are about the item Cardinality Regression
May 31, 2014 at 9:12 pm
Good question, thanks Steve.
😎
Still some confusion in the documentation at support.microsoft.com:
The following plan affecting trace flags are available in Microsoft SQL Server 2014:
9481
Use 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.
2312
Use 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.
June 1, 2014 at 9:10 am
Confusion!?
2312 - http://support.microsoft.com/kb/2801413
Use 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.
9481 - http://msdn.microsoft.com/en-us/library/dn600374%28v=sql.120%29.aspx
Once your workload is running with the new cardinality estimator (database compatibility level 120), and a specific query has regressed, you can run the query with trace flag 9481 to use version 70 (the SQL Server 2012 version) of the cardinality estimator.
Since you don't have the option for trace flag 9481, then changing the compatibility level to 110 is the right answer.
Igor Micev,My blog: www.igormicev.com
June 1, 2014 at 9:19 am
Igor Micev (6/1/2014)
Confusion!?2312 - http://support.microsoft.com/kb/2801413
Use 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.
9481 - http://msdn.microsoft.com/en-us/library/dn600374%28v=sql.120%29.aspx
Once your workload is running with the new cardinality estimator (database compatibility level 120), and a specific query has regressed, you can run the query with trace flag 9481 to use version 70 (the SQL Server 2012 version) of the cardinality estimator.
Since you don't have the option for trace flag 9481, then changing the compatibility level to 110 is the right answer.
Check this thread😀
😎
June 1, 2014 at 11:59 am
When I saw this, I found myself wondering if this were the documentation bug again or not? It could be a trick question, where the "correct" answer was the only answer provided that would run teh query with the old cardinality estimator, so the poor practice answer (use compatibility level) would be "correct" despite being poor practice for dealing with a single query rather than all queries in a DB. After all, it was just over a fortnight since STeve's last question on this topic and the documentation error was thoroughly aired then, surely he would have correted this one by now?
Anyway, in the end I decided it was probably a repeat of believing teh wrong BOL page and got it right by selecting an option that wouldn't actually work.
Tom
June 1, 2014 at 1:08 pm
TomThomson (6/1/2014)
When I saw this, I found myself wondering if this were the documentation bug again or not? It could be a trick question, where the "correct" answer was the only answer provided that would run teh query with the old cardinality estimator, so the poor practice answer (use compatibility level) would be "correct" despite being poor practice for dealing with a single query rather than all queries in a DB. After all, it was just over a fortnight since STeve's last question on this topic and the documentation error was thoroughly aired then, surely he would have correted this one by now?Anyway, in the end I decided it was probably a repeat of believing teh wrong BOL page and got it right by selecting an option that wouldn't actually work.
+1 (Ditto)
😎
June 1, 2014 at 3:08 pm
Gah, again!
I have found so many resources (not only MSDN) saying trace flag 2312 is used to run a query with the new cardinality estimator, not to disable it.
So the only correct option would have been to set the compatibility level...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 1, 2014 at 3:10 pm
Eirikur Eiriksson (6/1/2014)
Igor Micev (6/1/2014)
Confusion!?2312 - http://support.microsoft.com/kb/2801413
Use 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.
9481 - http://msdn.microsoft.com/en-us/library/dn600374%28v=sql.120%29.aspx
Once your workload is running with the new cardinality estimator (database compatibility level 120), and a specific query has regressed, you can run the query with trace flag 9481 to use version 70 (the SQL Server 2012 version) of the cardinality estimator.
Since you don't have the option for trace flag 9481, then changing the compatibility level to 110 is the right answer.
Check this thread😀
😎
Hm, ah, aham, ok. 🙂
Igor Micev,My blog: www.igormicev.com
June 1, 2014 at 3:21 pm
d. There is nothing you can do except update your resume
Smells like irony...:-P
😎
June 1, 2014 at 11:32 pm
Thank you, Steve. Easy One.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 2, 2014 at 1:32 am
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
June 2, 2014 at 1:40 am
is msdn confused, or its me....!!!
Nice Question....
June 2, 2014 at 6:20 am
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?
June 2, 2014 at 6:23 am
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.
June 2, 2014 at 7:40 am
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[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply