If we look at the actual plan, the current query is reading too much data from the table to be updated. This is from the index seek on BoxTrackInfo:
This is an index seek on btid for each row that comes out of the scan of BlueTrackEvents. Update locks are acquired as btStatusID is checked to see if the row qualifies for the update. Only 1,401 rows ...
I am able to get identical results between python and T-SQL code with the MD5 algorithm. For example, the NO COLLUSION string hashes to 0x5CA1A58C070F24EF1D4D2900E5727F37 on both platforms.
Example T-SQL code:
SELECT HASHBYTES('MD5', 'NO COLLUSION');
Example Python code:
result = hashlib.md5(b'NO COLLUSION')
I'm not ...
Joe has correctly pointed out that Python's hashlib.md5 and SQL Server's HASHBYTES('MD5', ...) functions have the same output.
As an additional clarification, the built-in hash() function in Python is not intended to be used in the same way. It's implementation is platform specific, varies depending on the type of object being used, and is (as you mentioned)...
I would not rely on Object Explorer (or the AG dashboard) in SSMS to determine the status. Use the DMVs directly (or create your own dashboards against them). Something like this:
AGName = COALESCE(ag.name, 'N/A'),
FROM sys.databases AS d
LEFT OUTER JOIN sys....
The only way to efficiently maintain a large cache of relationships like this is to not recompute the whole lot. Unfortunately partial updates, and reliably knowing what to update, can be complex depending on the requirements dictated by your business rules.
compute a hash for all the records in the tables involved
This is your first (and possibly biggest) ...