TF 8726 and 8727

[jadarnel27]

I came across these in an answer from Paul White on TopAnswer.xyz and just realized they are not in your list (which I reference frequently, so thanks for compiling it!).

I've included the info, and repros for both flags. Both repros use the StackOverflow2010 database, and require these indexes to be present:

CREATE INDEX IX_UserID ON dbo.Comments (UserID, PostID);
CREATE INDEX IX_PostID ON dbo.Comments (PostID, UserID);

Trace Flag: 8726
Undocumented trace flag
Function: Disables the heuristic that discourages the optimizer from producing an index union plan
Link: Why are (seemingly) suitable indexes not used on a LEFT JOIN with OR

Repro

Execution Plan: https://www.brentozar.com/pastetheplan/?id=B1mSzXGv8

-- No index union (seek on Users, multiple scans on Comments)
SELECT *
FROM Users u
	LEFT JOIN Comments c
		ON u.Id = c.UserId 
		OR u.Id = c.PostId
WHERE u.DisplayName = 'alex';

-- Gets the index union plan
SELECT *
FROM Users u
	LEFT JOIN Comments c
		ON u.Id = c.UserId 
		OR u.Id = c.PostId
WHERE u.DisplayName = 'alex'
OPTION (QUERYTRACEON 8726);

Trace Flag: 8727
Undocumented trace flag
Function: Can be used to prevent unwanted index union plans
Link: Why are (seemingly) suitable indexes not used on a LEFT JOIN with OR

Repro

Execution Plan: https://www.brentozar.com/pastetheplan/?id=HkwhlQGwL

-- Gets an index union plan normally
SELECT *
FROM dbo.Users u
OUTER APPLY 
(
	SELECT *
	FROM dbo.Comments c
	WHERE 
		u.Id = c.UserId 
		OR u.Id = c.PostId
) c
WHERE u.DisplayName = 'alex';

-- No index union (seek on Users, multiple scans on Comments)
SELECT *
FROM dbo.Users u
OUTER APPLY 
(
	SELECT *
	FROM dbo.Comments c
	WHERE 
		u.Id = c.UserId 
		OR u.Id = c.PostId
) c
WHERE u.DisplayName = 'alex'
OPTION (QUERYTRACEON 8727);

[ktaranov]

Awesome, great thanks! I will add this info tomorrow.

[ktaranov]

@jadarnel27 Do you have twitter account? I added you in thanks to section with awesome blog link (https://joshthecoder.com/) and can added twitter link too.
Also on Sunday I will add this trace flags to main document.

[jadarnel27]

Oh cool, thanks! My twitter is @josh_the_coder (https://twitter.com/josh_the_coder).

[ktaranov]

@jadarnel27 Added via and ca3a42c and 5bd38c7. Great thanks for your contribution.