How can I identify non deterministic updates occurring in my SQL Server?

Question

SQL Server books online states

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

I need to try and identify cases where non deterministic updates are occurring in our database. It appears that SQL Server does not throw an error or warning in such a case.

Does anyone know if there is an easy way of identifying where these issues are occurring, querying plan cache etc?. Doing a full code review to identify these issues will be time consuming given the size and complexity of the application.

Tested on SQL Server 2008\2017 developer editions.

Here is an example:

declare @t1 table (id int, col int);
insert into @t1 values(1,1);


declare @t2 table (id int, col int);
insert into @t2 values(1,10), (1,20);


update t1
set t1.col = t2.col
from @t1 t1 join @t2 t2
         on t1.id = t2.id;

select *
from @t1;

Answer 1

SQL Server does not warn you of this when using its proprietary UPDATE syntax. The MERGE statement would throw an error "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row."

The execution plan for your example is

update t1  set t1.col = t2.col  from @t1 t1 join @t2 t2           on t1.id = t2.id
  |--Table Update(OBJECT:(@t1 AS [t1]), SET:(@t1.[col] as [t1].[col] = @t2.[col] as [t2].[col]))
       |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([t2].[col]=ANY(@t2.[col] as [t2].[col])))
            |--Nested Loops(Inner Join, WHERE:(@t2.[id] as [t2].[id]=@t1.[id] as [t1].[id]))
                 |--Table Scan(OBJECT:(@t1 AS [t1]))
                 |--Table Scan(OBJECT:(@t2 AS [t2]))

The Bmk column is output from the scan on @t1 to act as a unique row identifier. The, potentially multiple, joining rows from @t2 are then added and collapsed down to an arbitrary one per Bmk using the ANY aggregate. The plan may in some circumstances replace the ANY aggregate with a DISTINCT SORT

So one way would be to search the plan cache for all execution plans containing update operators that have a descendant operator using either ANY or DISTINCT SORT as this may be a sign that SQL Server was arbitrarily removing the duplicates before doing the update.

This is far from fool proof as they may appear in the plans for other reasons but should give some viable candidates to review.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT st.text
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE  EXISTS(SELECT 1
              FROM   qp.query_plan.nodes('//Update') upd(n)
              WHERE  1 IN ( n.exist('//Aggregate[@AggType eq "ANY"]'), 
                            n.exist('//RelOp[@LogicalOp eq "Distinct Sort"]') ))