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        

                                

Trace Flag 4199: Complex Risk Assessment Part I

**Update**
I may have missed some rolled up trace flags, or some flagless fixes activated by T4199.  Finally, I may find some information about some 'undocumented' fixes included in T4199.   I've added references for those that I've found so far to a followup second post on this topic.

Trace Flag 4199: Complex Risk Assessment: Part II

**End update**

Recently a question came up on Twitter #sqlhelp about kb2904010, a recent fix for sample size in sample stats updates (kb article is linked below).  The original question was about determining the applicability of that fix to a given system.  For a given query, evaluating the appropriateness isn't too bad... but for a change like that you really should evaluate across the system.  And it seemed to me that the following procedure would be appropriate:
1. update all stats on the system with their typical sampling rate
2. save the stats info
3. apply and activate the fix
4. update all stats again with their typical sampling rate
5. compare stats with and without the fix

Here's the reason: if you only evaluate a single query, the effects of the fix on other queries are unknown.  In fact, that's true for any defined set of queries.  Even if you evaluate all current queries, what about the query tomorrow that is executed for the first time against existing data/indexes/stats keys?  So, in this case, comparing the effects across all current stats keys with their current sampling rates, while not necessary  gives information for risk assessment that wouldn't be available if the effect on only a subset of queries is evaluated.

I didn't say all of that on Twitter :) I'm long-winded in real life, but I try to behave when limited to 140.

There was a much shorter answer which took that particular exchange where it needed to go: the risk assessment of the fix in 2904010 is vastly increased by the fact that it is activated by trace flag 4199 (and does not have a separate trace flag which enables the fix individually).  You see, if that organization wasn't already using trace flag 4199, a two state test would not really be sufficient for risk assessment.  They'd have to have a 3 state test: baseline, baseline + T4199, baseline + T4199 + kb2904010. 

Why?  Cuz T4199 pulls a lotta stuff along with it, that's why.  In the days of my youth this is the type of roll-up fix that I would set out testing combinatorically - guessing which fixes would matter most to the system and trying to implement them singly.  Results with single fixes could be compared to baseline and with the full rollup.  Single fixes that showed strong positive returns in testing could be combined, with results compared to baseline and full rollup.

Wanna try?  I did.  But because there are 27 separate trace flags all rolled into trace flag 4199, I got tired out after running 1 test query through all possibilities.  Links below to the 28 kb hot fixes for these trace flags (one trace flag has a separate kba for SQL Server 2005 and SQL Server 2008).

Then... well, then come the 6 fixes which are activated by trace flag 4199 which CANNOT be activated separately.  You want 'em?  Gotta take the whole kaboodle.

That's a pretty complex risk assessment.  Now I will say this: there are considerable gains possible with trace flag 4199.  Just based on the activity of trace flag 4101 I saw the memory grant of some queries shrink from over 10GB to less than 2 mb!  And none of my test queries turned out with plans that were significantly worse on the test data set.  I haven't seen examples of chaos caused by trace flag 4199, and I know that Adam Machanic uses it widely (though he does of course encourage caution in adopting it).  So there are considerable benefits possible... I just wanted to try to put some documentation out there that would be useful for risk assessment.


In the tables below, general kba that mention T4199 appear first.  Then, sorted by trace flag number, the trace flag kbs for trace flags rolled up into T4199.  The MSSQL Version noted there is the earliest major version with the kb hotfix (not necessarily the version with the earliest calendar date availability of the hotfix).

Finally, sorted by kb article number, the 6 hotfixes I found that are activated by T4199 and CANNOT be activated individually.

Enjoy!  Hyperlinks behind each KBa number.

MS KBa	Description
974006	Trace flags rolled into T4199
2801413	Documents T4199 w/querytraceon
MS KBa	MSSQL Version Desc	Trace flag	Description
318530	SQL Server 2000	4101	outer joins w/filter criteria
940128	SQL Server 2005	4102	semi joins; mentions T4118??
946020	SQL Server 2005	4102	optimizer timeout; mentions T4122 in err?
920346	SQL Server 2005	4104	multi-column join cardinality
920347	SQL Server 2005	4105	index union cost
922438	SQL Server 2005	4106	multi-join query compile time
923849	SQL Server 2005	4107	range filter w/multiple table partitions
926024	SQL Server 2005	4108	fast forward-only cursor
926773	SQL Server 2005	4109	error w/fast forward-only cursor
933724	SQL Server 2005	4110	query w/user-defined scalar function
934065	SQL Server 2005	4111	replication merge agent
946793	SQL Server 2005	4115	forward-only cursor
950880	SQL Server 2005	4116	join of local and remote table
948445	SQL Server 2005	4117	blocking issue for row updates
942659	SQL Server 2005	4119	LIKE and comparison operator
953948	SQL Server 2005	4120	cursor deadlock
942444	SQL Server 2005	4121	outer joins; requires T4101
		4122	mentioned in 946020 in err?
948248	SQL Server 2005	4124	BIGINT statistics
949854	SQL Server 2005	4125	inner join of derived table w/DISTINCT
959013	SQL Server 2005	4126	transactional replication
953569	SQL Server 2005	4127	long compile time
955694	SQL Server 2005	4128	cursor for updates
957872	SQL Server 2008	4128	cursor for updates
958547	SQL Server 2005	4129	GETDATE() in table copies
956686	SQL Server 2005	4131	ON clause of query JOIN w/LIKE
958006	SQL Server 2005	4133	query notifications and error log
960770	SQL Server 2008	4135	error 605/804 on temp table insert
MS KBa	MSSQL Version Desc	Trace flag	Description
976935	SQL Server 2008	NULL	parameterized CTE query
2276330	SQL Server 2008	NULL	CHANGETABLE function
2451540	SQL Server 2005	NULL	multiple self-joins on same column
2649913	SQL Server 2008	NULL	sp_cursoropen
2876733	SQL Server 2012	NULL	linked server w/different collation
2904010	SQL Server 2008 R2	NULL	stats update sample size

Exploring Trace Flag 4199 with SQL Server 2012 Querytraceon Part 1

Trace flag 4199 is a rollup trace flag, which will enable a large number of query optimizer fixes that are otherwise individually enabled by separate trace flags.
More information on the query optimizer behavior related to trace flag 4199 here, including individual kb numbers and links for the rolled-up stuff.
http://support.microsoft.com/kb/974006

Querytraceon has now been publicly documented, and trace flag 4199 is one of the flags listed as supported with querytraceon.
http://support.microsoft.com/kb/2801413

Game on!

The SQL Server version: Microsoft SQL Server 2012 - 11.0.2332.0 (X64)
In query executions with and without trace flag 4199 below, I used 'SET STATISTICS IO ON' in order to get physical/logical reads, etc. 
I repeated the tests until I got a run with and without OPTION (QUERYTRACEON 4199) that had all data in cache and performed no physical reads.
That way the comparison was a little more straightforward - neither execution had any IO waits if neither performed any physical reads.

The test query executions returned 68 rows - this known resultset size allowed easy retrieval of information from sys.dm_exec_query_stats.

So... in SQL Server 2012 two executions of a complex query, differing only by inclusion of trace flag 4199 to for query optimizer behavior.
Without trace flag 4199, there were almost 10 times as many logical reads.  Without trace flag 4199, there was over 10 times as much accumulated worker time.
For the queries I spend most of my time with, trace flag 4199 merits consideration - if not as a global trace flag, certainly at the query level for those queries whose performance needs improvement.

I'm sure that just the io statistics and the query stats won't satisfy many as a full exploration of trace flag 4199 behavior with respect to this query.  I'll also compare the "actual plan" xml to see if I can locate where the significant difference in logical IOs occurs.  But not today.  Otherwise... how would I get you to come back to my blog, dear read?
:) 

(68 row(s) affected)
Table 'DIM_1'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_2'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_3'. Scan count 0, logical reads 136, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_4'. Scan count 1, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_DETAIL'. Scan count 2761, logical reads 8405, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_1'. Scan count 409, logical reads 1635, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_5'. Scan count 4, logical reads 138, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_6'. Scan count 0, logical reads 1008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_2'. Scan count 0, logical reads 1671, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


(68 row(s) affected)
Table 'DIM_1'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_2'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_3'. Scan count 0, logical reads 136, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_4'. Scan count 1, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_DETAIL'. Scan count 28038, logical reads 84834, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_1'. Scan count 2150, logical reads 7309, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_5'. Scan count 0, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_6'. Scan count 0, logical reads 2378, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 28246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_2'. Scan count 1, logical reads 5852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SELECT
   last_execution_time AS exec_time,
   last_logical_reads AS logical_rds,
   last_worker_time AS worker_time,
   last_elapsed_time AS elapsed_time
FROM
   sys.dm_exec_query_stats
WHERE
   last_rows = 68


              exec_time  logical_rds  worker_time  elapsed_time
2013-09-24 22:33:26.000        13211       138990        603339
2013-09-24 22:35:29.030       130019      1913968       4098876