Friday, April 25, 2014

Whaddayaknow 'bout #SQLServer Trace Flag 4134?

I don't know much about trace flag 4134 - I ran into it while researching a primary key error that I believe was falsely triggered.  Searching for primary key errors in parallel insert queries I happened on the Connect item that is last in the list below.  Hmmmm...

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
http://support.microsoft.com/kb/970198

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
http://support.microsoft.com/kb/2546901

Parallel insert plan causes Primary Key Violation 
http://connect.microsoft.com/SQLServer/feedback/details/634433/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. :-)

1 comment:

  1. Thanks to Aaron Morelli (tw: @sqlcrossjoin) for pointing out a third kb article mentioning trace flag 4134 as a corrective for an incorrect results condition.
    http://support.microsoft.com/kb/2589980
    Check out his topical trace flag list - it's good stuff!
    http://sqlcrossjoin.files.wordpress.com/2014/04/sqlcrossjoin_traceflagrepository_v2.pdf

    ReplyDelete