Now I think maybe this trace flag might eliminate some intra-query deadlock conditions I've been tracking as well. Maybe.
Here's what I wish I knew, but haven't been able to glean from any source:
1. Are global, session, and querytraceon scope all valid for trace flag 4134?
2. The possibility of T4134 behavior becoming default behavior at some point is mentioned in the Connect item. Has it become default behavior in SQL Server 2012 or SQL Server 2014?
Well... I'll update this post with anything more that I learn. And if you land here for any reason, and know something about this trace flag beyond what I've got here... please comment. Thanks!!
FIX: You receive an incorrect result when you run a query that uses the row_number function in SQL Server 2008 or in SQL Server 2008 R2
FIX: Results may change every time that you run a parallel query in SQL Server 2005, in SQL Server 2008, or in SQL Server 2008 R2 if the query uses a ranking function and if the computer has eight or more CPUs
Parallel insert plan causes Primary Key Violation
"… see the following KB: http://support.microsoft.com/kb/970198. Even that the symptom described in that KB is different, the root cause is the same. Please, note the mentioned Hotfix requires special trace flag 4134 to be enabled in order to take effect."
"The reason for protecting a change by a trace flag is to make sure we do not regress any other cases if the change may affect query plan choice. After some time, when we see stable behavior after the Hotfix, we remove trace flag protection for some fixes and make them active by default. For this fix, it may happen for future Cumulative Updates and the next Service Pack."
**** Update 2021 February 8 ****
I'll bring this additional kb article up from the comments into the main post :-) Thanks to Aaron Morelli for pointing this kb article out.
KB2589980 - FIX: Incorrect results or constraint violation when you run a SELECT or DML statement that uses the row_number function and a parallel execution plan in SQL Server 2008
As far as I know, the behavior enabled by trace flag 4134 was made default behavior in SQL Server 2012. Thankfully, after SQL Server 2012 I haven't had to pay attention to it again. :-)