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 Wednesday, May 14, 2014 9:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:42 PM
Points: 31,036, Visits: 15,466
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
Post #1571096
Posted Wednesday, May 14, 2014 11:42 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:10 PM
Points: 1,914, Visits: 5,271
Good question, thanks!

I got it wrong or did I

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....
Post #1571128
Posted Thursday, May 15, 2014 12:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 12,956, Visits: 10,727
Nice question, but I choose the wrong trace flag



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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1571140
Posted Thursday, May 15, 2014 12:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1571141
Posted Thursday, May 15, 2014 12:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:09 AM
Points: 805, Visits: 1,402
I am also confused

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

Post #1571154
Posted Thursday, May 15, 2014 1:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 3,966, Visits: 5,207
Interesting question, thanks Steve

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

I got it wrong or did I

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


par for the course....


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1571161
Posted Thursday, May 15, 2014 3:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 7,696, Visits: 9,424
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
Post #1571202
Posted Thursday, May 15, 2014 5:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 3,408, Visits: 1,747
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


-----
James Lean
@jamesleansql
Post #1571244
Posted Thursday, May 15, 2014 5:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:06 AM
Points: 1,343, Visits: 1,336
Eirikur Eiriksson (5/14/2014)
Good question, thanks!

I got it wrong or did I

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


+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
Post #1571255
Posted Thursday, May 15, 2014 6:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
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.
Post #1571279
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse