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


Upgrading cardinality


Upgrading cardinality

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147067 Visits: 19434
Comments posted to this topic are about the item Upgrading cardinality

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40841 Visits: 19473
Good question, thanks!

I got it wrongErmm or did Iw00t

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 Flag Description
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.

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....
Cool
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62790 Visits: 13298
Nice question, but I choose the wrong trace flag Sad


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7511 Visits: 2250
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."
pmadhavapeddi22
pmadhavapeddi22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 1870
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

http://blogs.msdn.com/b/saponsqlserver/archive/2014/01/16/new-functionality-in-sql-server-2014-part-2-new-cardinality-
estimation.aspx
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17173 Visits: 7416
Interesting question, thanks Steve

Eirikur Eiriksson (5/14/2014)
Good question, thanks!

I got it wrongErmm or did Iw00t

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 Flag Description
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.

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....
Cool


par for the course....Hehe

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26047 Visits: 12499
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://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Optimizing%20Your%20Query%20Plans%20with%20the%20SQL%20Server%202014%20Cardinality%20Estimator.docx
http://blogs.msdn.com/b/saponsqlserver/archive/2014/01/16/new-functionality-in-sql-server-2014-part-2-new-cardinality-estimation.aspx
http://blogs.msdn.com/b/psssql/archive/2014/04/01/sql-server-2014-s-new-cardinality-estimator-part-1.aspx
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

James Lean
James Lean
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4484 Visits: 2343
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:

https://connect.microsoft.com/SQLServer/feedback/details/869852/documentation-bug-concerning-new-ce-and-query-trace-flags

-----
JL
m mcdonald
m mcdonald
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2009 Visits: 1692
Eirikur Eiriksson (5/14/2014)
Good question, thanks!

I got it wrongErmm or did Iw00t

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 Flag Description
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.

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....
Cool


+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
Toreador
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3842 Visits: 8123
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.
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