Deferred Update
-
What are the different situations where we can see "deferred update". I am aware of "any column that is part of a unique constraint/Clustured Index is updated"
Kindest Regards,Amit Lohia
-
-
I learn every hour
I change the server setting to send as a pure update instead of INSERT/DELETE pair but I want to learn (know) what are the other scenario for "deferred update"
Kindest Regards,Amit Lohia
-
We have a third party application that seems to do deferred updates as part of their processing. I noticed this when analyzing the results of triggers from their replicated tables. When balancing Inserts, Updates & Deletes, I realized that many transactions were coming through as Deletes and Inserts instead of just an Update. So If I get a delete followed by an Insert for the same key, then I count it as an update. It was over a year ago, but I remember being a little confused for a while.
So, SQL may not generated deferred updates, but your software might !
-
This is happening in Transaction Replication.
Kindest Regards,Amit Lohia
-
Deferred updates are nowadays even more hot than they were 12 years back: today not only transactional replication uses the log reader's output, but also CDC uses the same output. Transaction replication's code was hidden for us end users, so we hardly needed to be aware of deferred updates. CDC however exposes our code to the deferred updates . The fn_cdc_get_net_changes_ functions are defined to return __$operation = 1 for deletes, __$operation = 2 for inserts, __$operation = 3 for an update's old value and __$operation = 4 for an update's new value. Some issues in the fn_cdc_get_net_changes_ functions in relation to deferred updates have been fixed, but not all of them (still surplus rows with __$operation = 1's can be returned). But the work arounds have come at a serious performance cost: fn_cdc_get_net_chages functions will choke-up on large(r) sets of changes in the _CT tables because at least 3 scans of the _CT tables are needed to do the workarounds correctly. Instead of putting work arounds in the fn_cdc_get_net_changes function, MS should be putting her resources onto having the log reader fixed to have it properly write __$operation 3 & 4 for all updates, instead of 1 & 2 for the deferred updates.
This thread shows how little knowledge is generally available on deferred updates. So I'd like to leave a link here to the only article I've found -albeit for SQL 7.0- that explains in detail what a deferred update actually is: http://sqlmag.com/sql-server/sql-server-70-update-strategies
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply