Complex Risk Assessment: Part II Trace Flag 4199 fixes w/o individual trace flags & SQL Server 2016 Update

*****Update 2017-03-02*****

Hey!!  It's 2017!  Let's talk about SQL Server 2016, k?

The incorporation of pre-2016 T4199 roll-up optimizer fixes by default with 2016 compatibility level(130) is quite well documented.
Using SQL Server 2016 with compatibility level below 130, these fixes are NOT enabled by default. 
Use trace flag 4199 or database scoped configuration QUERY_OPTIMIZER_HOTFIXES to enable these fixes if in a database at compatibility level below 130.

 So... between pre-2016 optimizer fixes enabled by default in SQL Server 2016 with compatibility level 130, and database scoped configuration QUERY_OPTIMIZER_HOTFIXES is it time to say goodbye to trace flag 4199?

Not so fast...

On January 18, 2017 both SQL Server 2016 RTM CU4 and SQL Server 2016 SP1 CU1 were released.
2016 RTM CU4 contains kb3208177 - 2016 SP1 CU1 does not have an equivalent documented fix.
Maybe there will be an equivalent fix in SQL Server 2016 SP1 CU2?

(SQL Server 2016 SP1 is documented as containing the fixes through SQL Server 2016 RTM CU3.
 https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/)

Optimizer fix enabled by trace flag 4199 in SQL Server 2016 RTM CU4
Improvement: Improves the query performance for SQL Server 2016 by changing the use of histograms on UNIQUE columns
https://support.microsoft.com/en-us/help/3202425/improvement-improves-the-query-performance-for-sql-server-2016-by-changing-the-use-of-histograms-on-unique-columns

*****End Update 2017-03-02  ***** 

**Update 2016-07-21**

Wow!  Its been 2.5 years since I originally put this blog post up!  So much has changed since then!

Especially because... with SQL Server 2016 RTM and the SQL Server 2016 database compatibility level, pre-2016 optimizer fixes rolled up into T4199 are enabled by default!  It gets a little tricky, because in SQL Server 2016 RTM with 2014 compatibility level and below... the optimizer fixes do NOT seem to be enabled by default.  That's my conclusion based on using a query that checks trace flag 4101 behavior, anyway.  But... database scope configuration for optimizer hotfixes makes the entire picture a bit complicated... there will be a future blog post on that, I promise.  Unless Microsoft beats me to it :-)

The main reason for this update: I just found another documented fix that's rolled into trace flag 4199 and I want to keep this post as complete as possible.  

This one is tricky, because the KBa doesn't mention trace flag 4199. But there's a CSS article about KB 222998 also, and *it* does mention trace flag 4199.  (So do the comments, btw.)

That CSS article is here.

"... inaccurate cardinality estimate when using anti-semi join..."

And here's the KB article information.

MS KBa	MSSQL Version Desc	Trace flag	Description
2222998	SQL Server 2005	NULL	Bad legacy CE estimate for antijoin query with multiple columns in WHERE

KBa 2222998 seems to be no more - or at least my link to it dangles.
But here's the related blog post from Jack Li that describes the fix.
Slow query performance because inaccurate cardinality estimate when using anti-semi join following upgrade from SQL Server 2000
https://blogs.msdn.microsoft.com/psssql/2010/09/01/slow-query-performance-because-inaccurate-cardinality-estimate-when-using-anti-semi-join-following-upgrade-from-sql-server-2000/

**End 2016-07-21 Update**

*****

Original blog post from 2014-01-06

This post is a followup to a previous blog post from 2014:

Trace Flag 4199: Complex Risk Assignment Part I

Steinar Andersen's T4199 blog post below expresses an understanding of the scope of trace flag 4199 and optimizer fixes very similar to the one I held until the last week.  Namely, that fixes to error and wrong results conditions would be enabled by default, while enabling other optimizer fixes may require both a code change and a trace flag (rolled up into T4199, or individually enabled with a separate trace flag).

Steinar Andersen

One Trace Flag to rule them all!

However, my recent webvestigation has turned up at least one error condition (Kb982441) and one wrong results fix (Kb2892741) enabled by T4199.  Moreover, these fixes cannot be enabled individually with a separate trace flag, as some T4199 rollup fixes can.

So... want to resolve the index rebuild errors of Kb982441, or resolve the wrong results of kb2892741?  Your choices:

1. Modify system and/or code to avoid defect triggers

2. Take everything that comes with T4199

In my opinion, that really changes the risk assessment involved with T4199.

I agree with the strategy of code + flag to enable various classes of fixes, optimizer behavior among them. 

But it doesn't seem true to the initial intent that an error or wrong results condition would require a trace flag for correction.

   

In my opinion, it also seems like T4199 optimizer fixes should have a timeline to become default behavior.  If database created in SQL Server 2014 includes a new cardinality estimator by default, shouldn't it benefit by default from optimizer fixes made in SQL Server 2005?

My opinions may not benefit SQL Server users much.  But at least the lists of T4199 fixes that I compile can help them :)

So, here you go... through 6 January 2014 these fixes, along with those previously listed in Part I, are the T4199 fixes of which I know.  Note: kb2757097 seems more incidental rather than intentional with respect to T4199.  But, since the KBa references T4199 I included it in the list.

Enjoy!  Hyperlinks behind each KBa number.

MS KBa	MSSQL Version Desc	Trace flag	Description
973255	SQL Server 2008	NULL	query w/MIN or MAX & partitioned view
982441	SQL Server 2008	NULL	error 666 w/Index rebuild
2264562	SQL Server 2008	NULL	full-text query w/CONTAINS or FREETEXT & OR
2667211	SQL Server 2008 R2	NULL	TOP, OPTION FAST(N), IN, EXISTS operators
2698639	SQL Server 2008 R2	NULL	SELECT w/LIKE and ESCAPE
2892741	SQL Server 2008	NULL	incorrect query results w/partitioned table
2757097	SQL Server 2012	NULL	spatial index ignored, T4199 may help

 Note: KBa 2667211 also mentions trace flag 4138 as correcting similar symptoms for the same operators even if the plan does not fit the pattern described in the KBa.  Trace flag 4138 seems distinct from the correction enabled by trace flag 4199 after this development is in place.  However I do not see a separate KBa for trace flag 4138.  In fact, KBa 2667211 indicates it was included in SQL Server 2008 R2 SP1 CU7, however the fix list for that CU does not include KBa 2667211.

http://support.microsoft.com/kb/2703282